[jira] [Commented] (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=17489052#comment-17489052 ] Artem Kupchinskiy commented on SPARK-38116: --- [~yoda-mon] Nice catch - Indeed, for postgres, it appears not be a problem because it is handled on the dialect layer. Theoretically, it would be nice to have some control on a connection in a generic case. However, as a downside, users can encounter some vague results having this option activated during DML operations (that is why in proposal I mentioned the necessity of guards preventing an illegal option combination). I guess some of maintainers should decide whether it could be a useful feature in general case. > 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-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (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=17488047#comment-17488047
]
Leona Yoda commented on SPARK-38116:
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-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (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=17488036#comment-17488036 ] Apache Spark commented on SPARK-38116: -- User 'yoda-mon' has created a pull request for this issue: https://github.com/apache/spark/pull/35424 > 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-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (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=17487841#comment-17487841 ] Leona Yoda commented on SPARK-38116: I would like to work on this. > 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-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
