public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Karsten Hilbert <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: psycopg2: proper positioning of .commit() within try: except: blocks
Date: Sat, 7 Sep 2024 13:03:34 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

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
[email protected]

view thread (10+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: psycopg2: proper positioning of .commit() within try: except: blocks
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox