On 9/7/24 12:44, Karsten Hilbert wrote:
Am Sat, Sep 07, 2024 at 09:46:03AM -0700 schrieb Adrian Klaver:
No I don't but - to my understanding - an ongoing transaction
is being closed upon termination of the hosting connection.
Unless .commit() is explicitely being issued somewhere in the
code that closing of a transaction will amount to a ROLLBACK.
In case of SQL having failed within a given transaction a
COMMIT will fail-but-rollback, too (explicit ROLLBACK would
succeed while a COMMIT would fail and, in-effect, roll back).
IOW, when SOME_SQL has failed it won't matter that I close
the connection with conn.commit() and it won't matter that
conn.commit() runs a COMMIT on the database -- an open
transaction having run that failed SQL will still roll back
as if ROLLBACK had been issued. Or else my mental model is
wrong.
https://www.psycopg.org/docs/connection.html#connection.close
Which says:
" Note that closing a connection without committing the changes first
will cause any pending change to be discarded as if a ROLLBACK was
performed"
That indicates the ROLLBACK is done on the close() not the commit() and
only if a commit() was not issued first.
NOTE: If you use the with context manager the transaction automatically
commits on success and rolls back exception, though it does not close
the connection. This is changed in psycopg3 where the connection is closed
In the case you show you are doing commit() before the close() so any
errors in the transactions will show up then. My first thought would be
to wrap the commit() in a try/except and deal with error there.
In the particular case I was writing about the SQL itself
succeeded but then the COMMIT failed due to serialization. I
was wondering about where to best place any needed
conn.commit(). My knee-jerk reaction was to then put it last
in the try: block...
All this is probably more related to Python than to PostgreSQL.
Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
--
Adrian Klaver
adrian.kla...@aklaver.com
