[
https://issues.apache.org/jira/browse/CAMEL-26997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Work on CAMEL-26997 started by Federico Mariani.
------------------------------------------------
> camel-jdbc - outputType=StreamList with a fetch size fails on PostgreSQL: the
> producer commits before the route reads the stream, which closes the cursor
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: CAMEL-26997
> URL: https://issues.apache.org/jira/browse/CAMEL-26997
> Project: Camel
> Issue Type: Bug
> Components: camel-jdbc
> Affects Versions: 4.14.9, 4.18.5, 4.23.0
> Reporter: Federico Mariani
> Assignee: Federico Mariani
> Priority: Major
> Fix For: 4.24.0
>
>
> With {{resetAutoCommit=true}} (the default),
> {{JdbcProducer.processingSqlBySettingAutoCommit}}
> ({{components/camel-jdbc/src/main/java/org/apache/camel/component/jdbc/JdbcProducer.java}},
> lines 76-122 on main) turns autocommit off, executes the statement and calls
> {{conn.commit()}} (line 91) 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 (lines
> 398-406), and closes it, resets autocommit and closes the connection in
> on-completions (lines 102-120 and 221-233).
> With a fetch size, pgjdbc reads the result through a server-side cursor
> (portal), {{fetchSize}} rows at a time. It does so 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 (the default holdability is
> {{CLOSE_CURSORS_AT_COMMIT}}). The early commit closes it: once the route has
> read the first batch, the next fetch fails with {{ERROR: portal "C_1" does
> not exist}}. Without {{statement.fetchSize}}, pgjdbc reads the whole result
> into memory in {{execute()}}, so {{StreamList}} works but does not stream.
> More generally, with the {{CLOSE_CURSORS_AT_COMMIT}} holdability a driver may
> close the open result sets on commit, so committing before the stream is read
> is wrong whatever the driver.
> Together with CAMEL-26996 (a {{StreamList}} that has failed looks finished),
> a route with a redelivery policy succeeds with the first batch of rows only,
> with no fault at all. Found with fault-injection tests against a real
> PostgreSQL, where it showed up without any fault.
> h3. Steps to reproduce
> PostgreSQL 17, pgjdbc 42.7.14, a {{PGSimpleDataSource}} (autocommit on, the
> JDBC default), 30 rows:
> {code:java}
> from("direct:stream")
> .setBody(constant("select id from items order by id"))
> .to("jdbc:ds?outputType=StreamList&statement.fetchSize=5")
> .split(body()).streaming()
> .to("mock:row")
> .end();
> {code}
> h3. Actual (4.23.0 and 4.24.0-SNAPSHOT)
> * The exchange fails after 4 of 30 rows: {{RuntimeCamelException: Cannot
> process result}}, caused by {{org.postgresql.util.PSQLException: ERROR:
> portal "C_1" does not exist}}.
> * With {{errorHandler(defaultErrorHandler().maximumRedeliveries(3))}}: the
> exchange succeeds, 4 of 30 rows routed (CAMEL-26996).
> * Control: {{resetAutoCommit=false}} with connections whose autocommit is off
> (HikariCP {{autoCommit=false}}): 30 rows.
> h3. Expected
> All 30 rows, read a batch of 5 at a time.
> h3. Workaround
> {{resetAutoCommit=false}} with a data source whose connections have
> autocommit off (the producer then leaves the transaction to the pool).
> h3. Suggested fix
> In streaming mode, end the transaction of the producer when the exchange is
> done rather than right after the execute: commit right away only when the
> producer closes the result at once; otherwise commit in the on-completion
> that resets autocommit and closes the connection, after the result set is
> closed (roll back when the exchange failed).
> camel-sql does not commit in its {{StreamList}} producer, so it is not
> affected.
> h3. Related
> * CAMEL-7447 ({{StreamList}}), CAMEL-2220 ({{statement.}} options such as
> {{fetchSize}}), CAMEL-4272 ({{resetAutoCommit}}).
> * CAMEL-26996.
> _Claude Code on behalf of Croway_
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)