You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "d33bs (via GitHub)" <gi...@apache.org> on 2023/04/21 18:45:51 UTC

[GitHub] [arrow-adbc] d33bs opened a new issue, #599: SQLite PRAGMA journal_mode modifications fail within transactions

d33bs opened a new issue, #599:
URL: https://github.com/apache/arrow-adbc/issues/599

   Hello, thank you so much for the great work with this project! I wanted to highlight an issue I've encountered when working with SQLite data from the Arrow-ADBC Python API. Please don't hesitate to let me know if I may clarify any of the below. Thanks for any help or guidance you can provide on this!
   
   My usecase involves wanting to modify the [journal_mode](https://www.sqlite.org/pragma.html#pragma_journal_mode) of an existing SQLite database from `DELETE` to `WAL` through a Python ADBC connection. When I try this I see an error:
   
   `adbc_driver_manager._lib.OperationalError: ADBC_STATUS_IO (10): [SQLite] AdbcStatementRelease: statement failed to finalize: (1) cannot change into wal mode from within a transaction`
   
   Example code which demonstrates this may be found here:
   ```python
   import adbc_driver_sqlite.dbapi
   
   # note: assume here that the database is set to
   # default journal_mode of DELETE
   database_path = "path_to_a_sqlite_file"
   
   conn = adbc_driver_sqlite.dbapi.connect(database_path)
   cursor = conn.cursor()
   # attempt to change the journal_mode to WAL
   cursor.execute("PRAGMA journal_mode=WAL;")
   cursor.close()
   conn.close()
   ```
   
   One workaround is to supply an explicit commit before attempting to change the SQLite PRAGMA (see below).
   
   ```python
   import adbc_driver_sqlite.dbapi
   
   # note: assume here that the database is set to
   # default journal_mode of DELETE
   database_path = "path_to_a_sqlite_file"
   
   conn = adbc_driver_sqlite.dbapi.connect(database_path)
   cursor = conn.cursor()
   # run explicit commit prior to pragma change
   cursor.execute("COMMIT;")
   # attempt to change the journal_mode to WAL
   cursor.execute("PRAGMA journal_mode=WAL;")
   cursor.close()
   conn.close()
   ```
   
   
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [arrow-adbc] lidavidm commented on issue #599: SQLite PRAGMA journal_mode modifications fail within transactions

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #599:
URL: https://github.com/apache/arrow-adbc/issues/599#issuecomment-1589684077

   #778 makes the error message in this situation more sensible, adds a way to enable autocommit on a connection to avoid this, and also adds an `executescript` so that you can use that on a regular connection.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [arrow-adbc] lidavidm commented on issue #599: SQLite PRAGMA journal_mode modifications fail within transactions

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #599:
URL: https://github.com/apache/arrow-adbc/issues/599#issuecomment-1518418557

   Ah.
   
   So DBAPI in Python requires that implementations _not_ use autocommit mode. But SQLite autocommits by default, so the Python ADBC bindings explicitly disable this. The SQLite driver handles this by starting a transaction...
   
   It sounds like the stdlib module does something similar, but only for certain kinds of statements. That sounds a little more brittle than I'd like: https://docs.python.org/3/library/sqlite3.html#sqlite3-controlling-transactions
   
   Would a nonstandard method like [`executescript`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executescript) be an option? Either that, or a way to disable this transaction handling with [`isolation_level`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executescript) instead of an explicit `COMMIT` (which may confuse the driver's own handling of COMMIT/BEGIN)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [arrow-adbc] lidavidm closed issue #599: c/driver/sqlite: SQLite PRAGMA journal_mode modifications fail within transactions

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm closed issue #599: c/driver/sqlite: SQLite PRAGMA journal_mode modifications fail within transactions
URL: https://github.com/apache/arrow-adbc/issues/599


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [arrow-adbc] lidavidm commented on issue #599: SQLite PRAGMA journal_mode modifications fail within transactions

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #599:
URL: https://github.com/apache/arrow-adbc/issues/599#issuecomment-1538582610

   That said: the driver doesn't implement either of my suggestions at the moment. So for now you'll have to live with the COMMIT.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [arrow-adbc] lidavidm commented on issue #599: SQLite PRAGMA journal_mode modifications fail within transactions

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #599:
URL: https://github.com/apache/arrow-adbc/issues/599#issuecomment-1538580268

   Yeah - we can use this issue to fix up the documentation. I do want to take a closer look at what the Python module does; perhaps it's reasonable for us to port some of that behavior (the main issues are: the underlying C driver isn't trying to follow DBAPI semantics, while the Python library is meant to be a generic wrapper, so if we want Python-and-driver-specific behavior we'll have to consider where exactly is best to put that)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [arrow-adbc] d33bs commented on issue #599: SQLite PRAGMA journal_mode modifications fail within transactions

Posted by "d33bs (via GitHub)" <gi...@apache.org>.
d33bs commented on issue #599:
URL: https://github.com/apache/arrow-adbc/issues/599#issuecomment-1538570817

   Thank you @lidavidm! I'll give those workarounds a try - they both seem reasonable. Would it make sense to document this for ADBC to make sure these special circumstances are visible to others?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org