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]

Reply via email to