[jira] [Comment Edited] (BEAM-12044) JdbcIO should explicitly setAutoCommit to false

["Sylvain Veyrié (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/BEAM-12044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17307970#comment-17307970 ] 

Sylvain Veyrié edited comment on BEAM-12044 at 3/24/21, 4:04 PM:
-----------------------------------------------------------------

[~jkff] it looks like it. I must admit I did not check if the block with setDefaultAutoCommit is actually executed.

 

We are using scio, hence the corresponding [JdbcIO Scala wrapper|https://github.com/spotify/scio/blob/2a297c055cd5d351441748f787345ddd3b34318d/scio-jdbc/src/main/scala/com/spotify/scio/jdbc/JdbcIO.scala]. If something is wrong there, I can bounce back to this project.


was (Author: sveyrie):
[~jkff] it looks like it. I must admit I did not check if we execute the block where setDefaultAutoCommit is called.

 

We are using scio, hence the corresponding [JdbcIO Scala wrapper|https://github.com/spotify/scio/blob/2a297c055cd5d351441748f787345ddd3b34318d/scio-jdbc/src/main/scala/com/spotify/scio/jdbc/JdbcIO.scala]. If something is wrong there, I can bounce back to this project.

> JdbcIO should explicitly setAutoCommit to false
> -----------------------------------------------
>
>                 Key: BEAM-12044
>                 URL: https://issues.apache.org/jira/browse/BEAM-12044
>             Project: Beam
>          Issue Type: Bug
>          Components: sdk-java-core
>    Affects Versions: 2.28.0
>            Reporter: Sylvain Veyrié
>            Priority: P2
>
> Hello,
> Per [PostgreSQL JDBC documentation|https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor], autocommit must be explicitly disabled on the connection to allow cursor streaming.
> [~jkff] mentionned it [on the mailing list|https://www.mail-archive.com/dev@beam.apache.org/msg16808.html], however even if there is:
> {code:java}
> poolableConnectionFactory.setDefaultAutoCommit(false);
> {code}
> in JdbcIO:1555, currently, at least with JDBC driver 42.2.16, any read with JdbcIO will memoize the whole dataset (which leads to OOM), since 
> {code:java}
> connection.getAutoCommit()
> {code}
> returns true in JdbcIO#ReadFn#processElement.
> I can provide a PR — the patch is pretty simple (and solves the problem for us in 2.28.0):
> {code:java}
> if (connection == null) {
>         connection = dataSource.getConnection();
> }
> connection.setAutoCommit(false); // line added
> {code}
> Thanks!



--
This message was sent by Atlassian Jira
(v8.3.4#803005)