Croway opened a new pull request, #30083: URL: https://github.com/apache/camel/pull/30083
[CAMEL-26997](https://issues.apache.org/jira/browse/CAMEL-26997) ### What was wrong With `resetAutoCommit=true` (the default), `JdbcProducer.processingSqlBySettingAutoCommit` turns autocommit off, executes the statement and calls `conn.commit()` as soon as `createAndExecuteSqlStatement` returns. With `outputType=StreamList` that is before the route has read the result: the producer leaves the `ResultSet` open for the route and closes it, resets autocommit and closes the connection in on-completions. pgjdbc reads a result with a fetch size through a server-side cursor (portal), `fetchSize` rows at a time, only inside a transaction (`PgStatement.executeInternal` at `REL42.7.14`: `fetchSize > 0`, forward-only, autocommit off, not holdable), and the cursor lives until the end of that transaction (default holdability `CLOSE_CURSORS_AT_COMMIT`). The early commit closes it, so `jdbc:ds?outputType=StreamList&statement.fetchSize=5` fails after the first batch with `ERROR: portal "C_1" does not exist`, with no fault at all. With a redelivery policy the exchange even succeeded with the first rows only (CAMEL-26996). Without a fetch size pgjdbc loads the whole result in `execute()`, so `StreamList` "worked" but did not stream. Found with fault-injection tests against a real PostgreSQL, where it showed up without any fault. ### The fix When the producer leaves the result open (`StreamList`), it no longer commits right after the execute. The on-completion that already resets autocommit and closes the connection (order `LOWEST + 200`, after the one that closes the result set and the statement, `LOWEST + 100`) now first **commits when the exchange completes, or rolls back when it fails**. The other output types are unchanged (commit right after the execute). camel-spring-jdbc uses the same producer. ### Design choices for reviewers - **Commit on complete, roll back on failure** (rather than always commit at the end). For a `SELECT` (the usual `StreamList`) it makes no difference. For a statement with side effects that returns rows (`INSERT`/`DELETE ... RETURNING`, H2's `final table`), a failed exchange now rolls the change back instead of keeping it committed: with a "delete and stream the deleted rows" statement, a route failing halfway no longer loses the rows it had not processed. This matches what the producer does when the execute itself fails (rollback), and the usual Camel unit-of-work semantics. Documented in the upgrade guide; happy to switch to "always commit" if preferred. - A failed commit or rollback in the on-completion is logged at WARN and ignored (the exchange is done), as the producer already does for a failed rollback. - `resetAutoCommit=false` is unchanged (the producer does not commit then). ### Tests - `JdbcProducerStreamListTransactionTest` (H2, runs in CI): a `DelegatingDataSource` whose connections close their result sets on `commit()` (what `CLOSE_CURSORS_AT_COMMIT` allows) must stream all rows; a `select * from final table (insert ...)` with `StreamList` is committed when the exchange completes and rolled back when it fails. - `JdbcProducerStreamListPostgresIT` (PostgreSQL through `camel-test-infra-postgres`, `PGSimpleDataSource` so autocommit on, 30 rows, `statement.fetchSize=5`): the split must route all 30 rows. - **Fail before the fix**: `rowsAreReadBeforeTheCommit` (`RuntimeCamelException: Cannot process result`), `rollsBackWhenTheExchangeFails` (`expected: <3> but was: <4>`), and the IT (`Cannot process result` caused by `PSQLException: ERROR: portal "C_1" does not exist`). - After the fix: the new tests, the IT and the full camel-jdbc suite (68 tests) pass (incl. `JdbcProducerConnectionClosedByDriverTest`, whose streaming case now rolls back on a closed connection, logged and ignored). The IT follows the module's `*IT` convention, so CI does not run it; the H2 test covers the same commit timing. ### Upgrade guide Yes, 4.24: with `StreamList` and `resetAutoCommit=true` the commit happens when the exchange completes (rollback when it fails). ### Relation to #30060 (docs) #30060 adds a NOTE to `jdbc-component.adoc` that describes the current behaviour (with the default `resetAutoCommit=true` the producer commits before a `StreamList` is read, so on PostgreSQL leave `statement.fetchSize` unset). Once this PR is merged that NOTE is no longer true and must be dropped (this PR documents the new behaviour after the `StreamList` example). Whichever of the two PRs merges second adjusts it. ### Related CAMEL-26996 (a `StreamList` that failed to read looks finished, so a redelivered split succeeds with part of the rows) is a separate PR, #30082; the two fixes do not overlap. camel-sql does not commit in its `StreamList` producer, so it is not affected (CAMEL-26991 deals with its connection handling). _Claude Code on behalf of Croway_ -- 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]
