Re: psycopg3 transactions

[Daniele Varrazzo]

On Sat, 16 Oct 2021, 23:48 Karsten Hilbert,  wrote:

>
> I am sorry to say this but this concept:
>
> This way we don’t have to remember to call neither
> close() nor commit() and the database operation have
> actually a persistent effect.
>
> seems fundamentally flawed to me in terms of databases.
> Actually changing the content of a database should be a
> conscious decision by program(mer) logic.
>
> I agree
>
> that writing to files seems similar, and is persistent by
> default, but then files don't sport transaction semantics.
>
> I also agree
>
> that a transaction being started by the first SQL command can
> be surprising to newcomers. It could be explained away by
> stating "once you start doing something to the database you
> *are* inside a transaction -- unless you have taken very
> specific action not to be" very prominently in the docs.
>
> However,
>
> the fact that "plain" use of psycopg3 and context manager use
> of psycopg3 yields opposite behaviour (default-rollback vs
> default-commit) seems to violate the Principle Of Least
> Astonishment ?
>

Thank you for your feedback, Karsten (and Magnus and others), and I
apologise for the late response.

I understand your observation and I see the inconsistency with the
behaviour of a connection without context manager. However, by far, the
biggest astonishment using psycopg is the surprise of finding that the
operations executed didn't commit.

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, and it would be a strange design for
the connection block to issue an explicit rollback to emulate the same
behaviour on the client.

I think that using 'execute("INSERT")' is already quite a conscious
decision of operating on the database. The use of transactions (implicitly,
explicitly) guarantees the atomicity of the operation performed, should the
program fail halfway through a sequence, and I think it is the most
important thing to respect, in the dbapi philosophy (I wasn't around when
it was designed, but I assume that the goal was to be atomic by default).
Committing the operations requested is by large the most common outcome a
programmer would want: asking that the program to 'conn.commit()'
explicitly seems an unrequested, kinda ritual, final blessing. Sending an
explicit ROLLBACK is an occurrence much more rare, and more conscious, than
just expecting that the commands already executed worked for real: people
wanting to do that I doubt they just rely on the side effect of 'close()'
or of the GC.

Also note that the block behaviour wrt transaction is the same as psycopg2;
the difference in Psycopg 3 is that the connection gets closed too at the
end of the block. Not doing that was the most surprising thing happening
and the cause of more than an argument, but nobody ever argued that they
didn't expect the transaction to be committed at the end of the block.

So yes, I acknowledge the inconsistency of the use without or with context,
but for me that's the difference between a mechanical, emerging, behaviour
(there is an implicit BEGIN, the connection is closed unclear, hence the
server discards the operations) and a conscious RAII decision (I create a
resource, I operate on the resource, the resource is clearly closed and
disposed of). Being a feature designed to be used to have a specific
effect, and wanting such effect to be as useful as possible, rolling back
on exit only to emulate the behaviour of a classic dbapi connection closed
in unclean state would have seemed the wrong design guideline.

Cheers

-- Daniele

Aw: Re: psycopg3 transactions

[Karsten Hilbert]

> 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.

> I think that using 'execute("INSERT")' is already quite a conscious 
> decision of operating on the database.

I agree. But the decision is not "this IS to be in the database" (or else no 
need for
transactions) but rather "this is to be in the database IF other, perhaps not 
even
database related, things suceed".

> 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).

Karsten

Re: Re: psycopg3 transactions

[Daniele Varrazzo]

On Thu, 21 Oct 2021 at 12:06, Karsten Hilbert  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).


> > 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.


-- Daniele

Re: Re: psycopg3 transactions

[Magnus Hagander]

On Thu, Oct 21, 2021 at 2:00 PM Daniele Varrazzo 
wrote:

> On Thu, 21 Oct 2021 at 12:06, Karsten Hilbert 
> 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/ 
 Work: https://www.redpill-linpro.com/ 

Re: psycopg3 transactions

[Christophe Pettus]

> On Oct 21, 2021, at 09:18, Magnus Hagander  wrote:
> 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.

+1.  I have to say that had I not read the warning, the behavior would have 
taken me by surprise, even with a lot of psycopg2 experience.