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)

Reply via email to