[SPARK-38116] Ability to turn off auto commit in JDBC source for read only operations

• Type: Improvement
• Status: In Progress
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 3.2.1
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Artem Kupchinskiy
• Created: 05/Feb/22 12:46
• Updated: 08/Feb/22 18:17

Description

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:

1. Add a boolean option "autocommit" to JDBC Source allowing a user to turn off autocommit mode for read only operations.
2. Add guards which prevent using this option in DML operations.  

 

Issue Links

• links to: [Github] Pull Request #35424 (yoda-mon)