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

On 2024-09-08, Greg Ewing <greg.ewing@canterbury.ac.nz> wrote:

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.
>
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 issued? The database doesn't know something
has gone wrong, so it will happily commit a partially-completed
transaction and possibly corrupt your data.
>
This is how I normally do things like this:
>
   try:
     do something
     .commit()
   except:
     log something
     .rollback()
>
Doing an explicit rollback ensures that the transaction is always
rolled back if it is interrupted for any reason.

What if there's an exception in your exception handler? I'd put the
rollback in the 'finally' handler, so it's always called. If you've
already called 'commit' then the rollback does nothing of course.