[ 
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)

Reply via email to