Federico Mariani created CAMEL-26997:
----------------------------------------
Summary: 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
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)