[DB-SIG] Adding Connection.autocommit as standard extension to DB-API 2.0 (PEP 249)

Daniele Varrazzo daniele.varrazzo at gmail.com
Sun Oct 30 12:28:52 EDT 2022


On Fri, 28 Oct 2022 at 15:58, Marc-Andre Lemburg <mal at egenix.com> wrote:

My few pence:

> If there is a pending transaction, though, there are three approaches
> we could take:
>
> 1. The database module raises an exception, to force an explicit
>     .commit() or .rollback() by the programmer.
>
> 2. The module automatically commits the pending transaction,
>     since that's what autocommit is all about.
>
> 3. We leave these semantics open and up to the database module
>     to determine.
>
> My preference would be option 2, since this makes things clear for
> everyone and is intuitive (well, at least for me :-)).

2 is a surprising side effect and loaded foot-gun. Either the user has
an intent, in which case 1 is an useful indication that there's a
ProgrammingError there, or there is a bug, let's say in a
multithreaded program, in which case 1 is an useful indication that
something is broken.

I believe psycopg2 has implemented 2 for the first few years.
Sometimes, maybe 2011, maybe before, the behaviour switched to 1.
There was some drama around the fact that some programs previously
broken started erroring out because they had transactions open in
moments in which nobody thought there were, but quickly it was
acknowledged that fixing those programs was a good idea. Nobody has
complained since, or argued that an automatic commit of the current
transaction on setting autocommit is a good idea, or a desired, or
intuitive, behaviour.

On a related tangent (if you like a good oxymoron), should the DBAPI
ever suggest an async interface, an `autocommit` setter performing I/O
(in order to commit) would be impossible to implement, as Python
doesn't offer async attribute access. In Psycopg 3 it turned out that,
even without I/O access, an autocommit setter was impossible to
implement anyway, because the lock that the connection uses to guard
its state from concurrent access should be likely async too. For this
reason, the async version of psycopg 3 connection has an `async def
set_autocommit()` method instead
(https://www.psycopg.org/psycopg3/docs/api/connections.html#psycopg.AsyncConnection).

Pragmatically, I doubt that the dbapi can do anything different than
3, and maybe should warn the user that setting autocommit with a
transaction open has an undetermined behaviour.

Cheers

-- Daniele


More information about the DB-SIG mailing list