hansva opened a new issue, #9105: URL: https://github.com/apache/hop/issues/9105
### Apache Hop version? 2.20.0 ### Java version? 21 ### Operating system macOS ### What happened? **Affected transforms:** Update, Delete, Insert/Update, Execute row SQL script, Dimension lookup/update **Problem** On PostgreSQL (and look-alikes such as Greenplum), a failed statement aborts the whole transaction. Every statement after it fails with `current transaction is aborted, commands ignored until end of transaction block` until a rollback. With an error hop on one of the transforms above, a single bad row (a value too long, a foreign key or primary key violation, ...) is sent to the error hop as expected. But every row after it in the same transaction is sent there too, even though those rows are valid. Rows written before the bad row in the same commit block are lost as well, because the aborted transaction is rolled back at the end. **Example:** Update with an error hop, 5 input rows, row 3 too long for its column: - Expected: rows 1, 2, 4 and 5 updated, row 3 sent to the error hop - Actual: only rows 1 and 2 updated (commit size 2), rows 3, 4 and 5 all sent to the error hop Table output and Synchronize after merge don't have this problem: they already use a savepoint per row. **Fix** When the transform has an error hop, the database uses savepoints (`isUseSafePoints()`), the connection is not in auto-commit, and batch mode is off, each row now runs behind a savepoint. A failing row is rolled back to its savepoint and sent to the error hop, and the following rows carry on in the same transaction. The transform commits itself after the savepoint is released, because a commit ends the savepoint. Without that, a commit in the middle of a row broke the release or rollback that came after it. Batch mode is unchanged. **Tests:** Testcontainers ITs per transform (commit sizes 1 and 100), and `integration-tests/database/main-0048-error-hop-savepoints.hwf`. They fail without the fix and pass with it. ### Issue Priority Priority: 2 ### Issue Component Component: Database -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
