Re: psycopg3 transactions

On Sat, 16 Oct 2021, 23:48 Karsten Hilbert, <Karsten(dot)Hilbert(at)gmx(dot)net> 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