Re: psycopg2 positioning of .commit() (Posting On Python-List Prohibited)

On 2024-09-09, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Mon, 9 Sep 2024 09:13:40 -0000 (UTC), Jon Ribbens wrote:

On 2024-09-08, Lawrence D'Oliveiro <ldo@nz.invalid> wrote:

On Sun, 8 Sep 2024 11:03:21 -0000 (UTC), Jon Ribbens wrote:

What if there's an exception in your exception handler? I'd put the
rollback in the 'finally' handler, so it's always called. If you've
already called 'commit' then the rollback does nothing of course.

>
In any DBMS worth its salt, rollback is something that happens
automatically if the transaction should fail to complete for any
reason.
>
This applies for any failure reason, up to and including a program or
system crash.

 
If it's a program or system crash, sure, but anything less than that -
how would the database even know, unless the program told it?

>
The database only needs to commit when it is explicitly told. Anything
less -- no commit.

So the Python code is half-way through a transaction when it throws
a (non-database-related) exception and that thread of execution is
aborted. The database connection returns to the pool, and is re-used
by another thread which continues using it to perform a different
sequence of operations ... ending in a COMMIT, which commits
one-and-a-half transactions.