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.klaver@aklaver.com
