Re: psycopg3 transactions - Mailing list psycopg
| From | Daniele Varrazzo |
|---|---|
| Subject | Re: psycopg3 transactions |
| Date | |
| Msg-id | CA+mi_8bY1HJrAVrUFsdDCqJT4+UjK7i7Lfso4dN_0nNf7RU4nQ@mail.gmail.com Whole thread Raw |
| In response to | Re: psycopg3 transactions (Karsten Hilbert <Karsten.Hilbert@gmx.net>) |
| Responses |
Aw: Re: psycopg3 transactions
(Karsten Hilbert <Karsten.Hilbert@gmx.net>)
|
| List | psycopg |
On Sat, 16 Oct 2021, 23:48 Karsten Hilbert, <Karsten.Hilbert@gmx.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