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

On 07/09/2024 16:48, Karsten Hilbert via Python-list wrote:

Dear all,
>
unto now I had been thinking this is a wise idiom (in code
that needs not care whether it fails to do what it tries to
do^1):
>
conn = psycopg2.connection(...)
curs = conn.cursor()
try:
curs.execute(SOME_SQL)
except PSYCOPG2-Exception:
some logging being done, and, yes, I
can safely inhibit propagation^1
finally:
conn.commit() # will rollback, if SOME_SQL failed
conn.close()
>
So today I head to learn that conn.commit() may very well
raise a DB related exception, too:
>
psycopg2.errors.SerializationFailure: could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
TIP:  The transaction might succeed if retried.
>
Now, what is the proper placement of the .commit() ?
>
(doing "with ... as conn:" does not free me of committing appropriately)
>
Should I
>
try:
curs.execute(SOME_SQL)
conn.commit()
except PSYCOPG2-Exception:
some logging being done, and, yes, I
can safely inhibit propagation^1
finally:
conn.close() # which should .rollback() automagically in case we had not reached to .commit()
>
?
>
Thanks for insights,
Karsten

I would put the curs.execute and the conn.commit in separate try...except blocks.  That way you know which one failed, and can put appropriate info in the log, which may help trouble-shooting.
(The general rule is to keep try...except blocks small.  And of course only catch the exceptions you are interested in, which you seem to be already doing.)
Best wishes
Rob Cliffe