Re: Re: psycopg3 transactions

On Thu, Oct 21, 2021 at 2:00 PM Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
wrote:

> On Thu, 21 Oct 2021 at 12:06, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
> wrote:
> >
> > > The behaviour of a dbapi connection, without context block, is to just
> close the communication. The fact that this results in a rollback > stems
> only from the behaviour of the server
> >
> > But that's the whole point? A driver should _not_ (by default) alter
> the default
> > behaviour of the other end, IMO, without extremely good reason. There
> _is_ good
> > reason for the transaction context manager, but not for the connection
> context
> > manager or plain use.
>
> The implicit BEGIN is also a surprising change from the "natural
> course of events". Psycopg can still be used as a driver and not alter
> the natural course of events. I have extended the documentation
> (https://www.psycopg.org/psycopg3/docs/basic/usage.html#connection-context
> )
> both highlighting the difference in behaviour you reported and
> suggesting to not use 'with' in case more control is needed (using
> psycopg more as a driver than as the end user).
>

Yes, the implicit BEGIN definitely is surprising -- but more easily
detected.

If the behaviour when used as a context block isn't actually defined and
required by dbapi, I would suggest that the default of opening a new
connection as context manager *wouldn't* do the BEGIN -- it would just open
a connection in autocommit=on mode, mimicking the default on the server
side.

I would've personally liked that for non-context-manager one as well, but
AIUI that's actually required to behave the other way by dbapi.

But yes, the biggest issue I have with it is the part that's outlined in
the Warning section of the docs there -- using it in different ways
provides completely and fundamentally different behaviour in a way that can
cause bad data.

Things would be a lot more clear if the *connection* context didn't do
*anything* with transactions whatsoever, and the *transaction* one did all
of it. When it comes to transactions, explicit is always better than
implicit IMNSHO.

> > asking that the program to 'conn.commit()' explicitly seems an
> unrequested, kinda ritual, final blessing.
> >
> > Exactly.
> >
> > > Sending an explicit ROLLBACK is an occurrence much more rare,
> >
> > Exactly, and thusly easily forgotten, with possibly dire consequences
> > (in the case of default-commit, as opposed to default-rollback).
>
> On this I disagree. People forget to do things when they are a
> repetitive common occurrence, not when they are extraordinary.
>
> I don't really see a disaster occurring there, especially because
> porting code from psycopg2 results in pretty much the same operations.
>

Another thing to consider is the failure scenarios.

If it implicitly rolls back at the end if you didn't do the right thing,
then you will notice immediately, because none of your changes get saved to
the database.

If it implicitly commits at the end it will *look* fine if you didn't do
the right thing, but more unpredictable things will happen if there's a
failure and you might not notice until much much later that you actually
had a transaction *at all*.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>