You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Leona Yoda (Jira)" <ji...@apache.org> on 2022/02/07 11:26:00 UTC

[jira] [Comment Edited] (SPARK-38116) Ability to turn off auto commit in JDBC source for read only operations

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

Leona Yoda edited comment on SPARK-38116 at 2/7/22, 11:25 AM:
--------------------------------------------------------------

I post sample PR, however, the intended behavior might be realized already if user set fetchSize > 0.

(cf.  [https://github.com/apache/spark/blob/v3.2.1/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala#L136])
 # If user set fetchsize > 0, autocommit will be set to false when reading operation in the original code.
 # fetchSize is set to 0 by default, so if users disable autocommit the jdbc driver will try to get all rows.

By the PR users will be able to choose the case autocommit true and fetchSize > 0 ... but the document says it won't work.

 

Then, I think, in any case for reading operation, auto commit should be disabled. Removing the if condition on {{beforeFetch}} is considerable.


was (Author: yoda-mon):
I post sample PR, however, the intended behavior might be realized already if user set fetchSize > 0.

(cf.  https://github.com/apache/spark/blob/v3.2.1/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala#L136)
 # If user set fetchsize > 0, autocommit will be set to false when reading operation in the original code.
 # fetchSize is set to 0 by default, so if users disable autocommit the jdbc driver will try to get all rows.

By the PR users will be able to choose the case autocommit true and fetchSize > 0 ... but the document says it won't work.

 

Then, I think, in any case for reading operation, auto commit should be disabled. Removing the if condition on {{beforeFetch}} is considerable.

 

 

> Ability to turn off auto commit in JDBC source for read only operations
> -----------------------------------------------------------------------
>
>                 Key: SPARK-38116
>                 URL: https://issues.apache.org/jira/browse/SPARK-38116
>             Project: Spark
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 3.2.1
>            Reporter: Artem Kupchinskiy
>            Priority: Minor
>
> Currently, all the jdbc connections on executors side work always with auto commit option set to true.
> However, there are cases where this mode makes hard to use JdbcRelationProvider at all, i.e. reading huge datasets from Postgres (a whole result set is collected regardless of a fetch size when autocommit is set to true https://jdbc.postgresql.org/documentation/91/query.html#query-with-cursor )
> So the proposal is following:
>  # Add a boolean option "autocommit" to JDBC Source allowing a user to turn off autocommit mode for read only operations.
>  # Add guards which prevent using this option in DML operations.  
>  



--
This message was sent by Atlassian Jira
(v8.20.1#820001)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org