0

I have ETL (Pentaho DI 8.2) to fill a Data Vault Hub table. Basically the ETL depends on the UNIQUE index to refuse duplicates of the business key column. In SQL Server this works great. With PostgreSQL I notice that it stops inserting after the first violation of the unique index. The ETL write is set to do individual inserts and ignore database errors. I add a rule to ignore the duplicates, but this slows down performance. Also I want to prevent this complication with an extra construct.

How do I get PostgreSQL to continue inserting valid rows into my hub tables?

0

My first suggestion would be to try avoiding duplicates in the first place: Pentaho has "Unique Rows" and "Unique Rows (hashset)" steps to help with that. First option would work fast but is only good for pre-sorted input.

The table output step with error handling should be capable of proceeding correctly past the error. Could you check if you have any steps past the output? (E.g., "abort on error" would kill the transform). Posting a log would also help a lot to identify the issue.

UPDATE: In short, due to PostgreSQL internals, the default behaviour on error within transaction is different to some other DBs. It requires a formal rollback or commit before any other commands could be issued. This behaviour made it's way into the JDBC driver used by Pentaho -- as expected.

SOLUTION: Update the JDBC driver to the later version (at least 9.4.1210, but I would recommend version 42+). Then in the connection properties in Pentaho go to Advanced and create property "autosave" with value "always". This will effectively ROLLBACK any failing statements by automatically creating a savepoint after every successful statement. For more details see: https://jdbc.postgresql.org/documentation/head/connect.html

This comes at a minor performance hit, but likely not huge. You still have to keep the Commit size at "1" otherwise the whole batch will happily fail and you'll miss data.

| improve this answer | |
  • The whole idea is that the database simplifies the ETL considerably. Using the unique index changes my ETL from 10 to 2 (or 3) steps. Consider my issue is not to avoid the error, but to have PostgreSQL to keep inserting after an error. – Invisi Mar 29 '19 at 13:04
  • Please provide a section of Pentaho log and transformation (screenshot, maybe, if you worry about the confidential details or the ETL) so I could help you further – Megadest Mar 29 '19 at 14:15
  • please note it's a POSTGRESQL issue, NOT a Pentaho issue! I want to know to get PostgreSQL to keep inserting rows, not help to change my ETL. The ETL only shows there are unique violations. – Invisi Mar 30 '19 at 16:34
  • @Invisi - well, this is a combination of issues, not just PostgreSQL. Let me update the answer and see if you like it more – Megadest Apr 1 '19 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.