psycopg2: proper positioning of .commit() within try: except: blocks
Rob Cliffe
rob.cliffe at btinternet.com
Sat Sep 7 12:11:57 EDT 2024
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
More information about the Python-list
mailing list