Re: psycopg2: proper positioning of .commit() within try: except: blocks

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