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

Am Sun, Sep 08, 2024 at 12:48:50PM +1200 schrieb Greg Ewing via Python-list:

On 8/09/24 9:20 am, Karsten Hilbert wrote:

try:
do something
except:
log something
finally:
.commit()
>
cadence is fairly Pythonic and elegant in that it ensures the
the .commit() will always be reached regardless of exceptions
being thrown or not and them being handled or not.

>
That seems wrong to me. I would have thought the commit should only
be attempted if everything went right.

It is only attempted when "everything" went right. The fault
in my thinking was what the "everything" might encompass.
When some SQL fails it won't matter whether I say
conn.commit() or conn.rollback() or, in fact, nothing at all
-- the (DB !) transaction will be rolled back in any case.

However, that reasoning missed this:

What if there's a problem in your code that causes a non-SQL-related
exception when some but not all of the SQL statements in the
transaction bave been [-- even successfully --] issued?

Still, in this code pattern:

  try:
    do something
    .commit()
  except:
    log something

it doesn't technically matter whether I say .commit or .rollback here:

    .rollback()

... but ...

Doing an explicit rollback ensures that the transaction is always
rolled back if it is interrupted for any reason.

explicit is better than implicit ;-)

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B