关于Kettle更新Greenplum&Postgresql出错问题

今天遇到一个比较奇葩的事，在Kettle更新Greenplum&Postgresql时会出以下错误：

 

2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : Unexpected error
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : org.pentaho.di.core.exception.KettleStepException: 
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : Error in step, asking everyone to stop because of:
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : Error looking up row in database
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : ERROR: Unexpected internal error (cdbdisp.c:466)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:307)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at java.lang.Thread.run(Thread.java:662)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : Error looking up row in database
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : ERROR: Unexpected internal error (cdbdisp.c:466)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.pentaho.di.core.database.Database.getLookup(Database.java:3120)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.pentaho.di.core.database.Database.getLookup(Database.java:3093)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:80)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:290)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	... 2 more
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : Caused by: org.postgresql.util.PSQLException: ERROR: Unexpected internal error (cdbdisp.c:466)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2077)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1810)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:498)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	at org.pentaho.di.core.database.Database.getLookup(Database.java:3101)
2014/08/08 11:08:15 - Insert / Update.0 - ERROR (version 4.2.1, build 1 from 2012-11-22 19.15.47 by Administrator) : 	... 5 more
2014/08/08 11:08:15 - Table input.0 - Stopped while putting a row on the buffer

 网上基本找不到跟“Unexpected internal error (cdbdisp.c:466)”相关的问题，但是在Pentaho论坛找到一个bug http://wiki.pentaho.com/display/EAI/Insert+-+Update

解决方法是在数据库连接的高级选项中，勾选“Supports boolean data type”即可。

 

想了下，问题的原因应该是GP和PG中不会对boolean向int自动转换；问题出在建表时有字段类型是类似smallint(1)这种的情况，jdbc遇到长度为1的整形字段时（定义字段）会自动转为布尔值，所以产生了该问题。最好的解决方法，是在select时对这种类型的字段应该乘以1或者加0，利用隐式转换使字段结果为整型字段（显式转换应该也可以），这样有个好处，在遇到2~9时，不会因为前边提到的布尔类型转换都成为1