Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Error while trying to insert rows into POSTGRESQL

  1. #1
    Join Date
    Nov 2014
    Posts
    11

    Default Error while trying to insert rows into POSTGRESQL

    Hello guys,

    I have a DW scenario here and I want to populate my DW through stage using Kettle.

    As the official scenario is quite complex and the error is also happening when doing simple tests, I am only posting the simple scenario here for you.

    What I want to do:

    Import rows from a TABLE INPUT (Firebird) into a TABLE OUTPUT (PostgreSQL).

    I mapped all the fields and ran the transformation, which failed with the error appended to the end of this thread. Then I realized that I had some columns in upper case and this could cause problems. Then I recreated my table with all columns in lower cappital, but I still get the same error.

    * When I copy the insert in the error message and run it directly in postgresql (same user account), it succeeds.

    Please, assist.


    014/11/19 17:06:34 - Table output.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Unexpected batch update error committing the database connection.
    2014/11/19 17:06:34 - Table output.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
    2014/11/19 17:06:34 - Table output.0 - Error updating batch
    2014/11/19 17:06:34 - Table output.0 - Entrada em lote 0 INSERT INTO fin."fatofatdre" ("empresa", "ano", "mes", "valor_nf", "nivel1", "nivel2", "nivel3") VALUES ( 1, '2013', '06', '25934.85', 'GOVERNO', 'DESENVOLVIMENTO', 'PROJETO') foi abortada. Chame getNextException para ver a causa.
    2014/11/19 17:06:34 - Table output.0 -
    2014/11/19 17:06:34 - Table output.0 - at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1377)
    2014/11/19 17:06:34 - Table output.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1366)
    2014/11/19 17:06:34 - Table output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:571)
    2014/11/19 17:06:34 - Table output.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
    2014/11/19 17:06:34 - Table output.0 - at java.lang.Thread.run(Thread.java:745)
    2014/11/19 17:06:34 - Table output.0 - Caused by: java.sql.BatchUpdateException: Entrada em lote 0 INSERT INTO fin."fatofatdre" ("empresa", "ano", "mes", "valor_nf", "nivel1", "nivel2", "nivel3") VALUES ( 1, '2013', '06', '25934.85', 'GOVERNO', 'DESENVOLVIMENTO', 'PROJETO') foi abortada. Chame getNextException para ver a causa.
    2014/11/19 17:06:34 - Table output.0 - at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2743)
    2014/11/19 17:06:34 - Table output.0 - at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:461)
    2014/11/19 17:06:34 - Table output.0 - at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1928)
    2014/11/19 17:06:34 - Table output.0 - at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
    2014/11/19 17:06:34 - Table output.0 - at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
    2014/11/19 17:06:34 - Table output.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1353)
    2014/11/19 17:06:34 - Table output.0 - ... 3 more
    2014/11/19 17:06:34 - Table output.0 - Finished processing (I=0, O=0, R=234, W=0, U=0, E=1)
    2014/11/19 17:06:34 - Transformação 1 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!
    2014/11/19 17:06:34 - Spoon - The transformation has finished!!
    2014/11/19 17:06:34 - Transformação 1 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!
    2014/11/19 17:06:34 - Transformação 1 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!

  2. #2
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Turn off batch updates, and you'll get a better response from PostgreSQL

    Right now, it's telling you to "Call getNextException to see the cause."

    It may be something as simple as a type mis-match... ano might be an integer in the DB, but a string in PDI.

  3. #3
    Join Date
    Nov 2014
    Posts
    11

    Default

    Hello gutlez,

    you were exactly right. I just disabled it and got a more detailed error message informing that a type mismatch had occured. Changed it and resolved.

    thanks a lot for your help.

  4. #4
    Join Date
    Jul 2009
    Posts
    475

    Default

    I'll second what gutlez wrote about checking the datatypes. Your 2013, 06 and 25934.85 values are all quoted, so Postgres sees them as strings (varchars), and they might be numeric types in the database. I use Postgres and really like it, but when I first started using it, PG felt like it was more finicky about datatypes. PG doesn't seem to do many implicit data type conversions, at least compared to other DBMS.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Quote Originally Posted by mozartiano View Post
    Hello gutlez,

    you were exactly right. I just disabled it and got a more detailed error message informing that a type mismatch had occured. Changed it and resolved.

    thanks a lot for your help.
    Now that you got your type mismatch fixed, turn it back ON!
    It requires a little bit less chatty round-trips to the DB with Batch Mode ON, so you'll get slightly better performance with it. You can also look at the PostgreSQL Bulk Loader to move more data into the DB faster too...

  6. #6
    Join Date
    Feb 2011
    Posts
    152

    Default

    Also, if you ever try to use the PG Bulk Loader step, watch out for this bug when dealing with boolean values: http://jira.pentaho.com/browse/PDI-7339

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.