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

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

#-------------------------------
^1:

This particular code is writing configuration defaults
supplied in-code when no value is yet to be found in the
database. If it fails, no worries, the supplied default
is used by follow-on code and storing it is re-tried next
time around.

#-------------------------------
Exception details:

Traceback (most recent call last):
File "/usr/share/gnumed/Gnumed/wxpython/gmGuiMain.py", line 3472, in OnInit
frame = gmTopLevelFrame(None, id = -1, title = _('GNUmed client'), size = (640, 440))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/share/gnumed/Gnumed/wxpython/gmGuiMain.py", line 191, in __init__
self.LayoutMgr = gmHorstSpace.cHorstSpaceLayoutMgr(self, -1)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/share/gnumed/Gnumed/wxpython/gmHorstSpace.py", line 215, in __init__
self.top_panel = gmTopPanel.cTopPnl(self, -1)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/share/gnumed/Gnumed/wxpython/gmTopPanel.py", line 52, in __init__
wxgTopPnl.wxgTopPnl.__init__(self, *args, **kwargs)
File "/usr/share/gnumed/Gnumed/wxGladeWidgets/wxgTopPnl.py", line 33, in __init__
self._TCTRL_patient_selector = cActivePatientSelector(self, wx.ID_ANY, "")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/share/gnumed/Gnumed/wxpython/gmPatSearchWidgets.py", line 1295, in __init__
cfg.get2 (
File "/usr/share/gnumed/Gnumed/pycommon/gmCfg.py", line 248, in get2
self.set (
File "/usr/share/gnumed/Gnumed/pycommon/gmCfg.py", line 367, in set
rw_conn.commit() # will rollback if transaction failed
^^^^^^^^^^^^^^^^
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.

2024-08-20 22:17:04 INFO gm.cfg [140274204403392 UpdChkThread-148728] (/usr/share/gnumed/Gnumed/pycommon/gmCfg.py::get2() #148): creating option [horstspace.update.consider_latest_branch] with default [True]
2024-08-20 22:17:04 DEBUG gm.db_pool [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmConnectionPool.py::exception_is_connection_loss() #667): interpreting: 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.

2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #170): exception: 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.

2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #171): type: <class 'psycopg2.errors.SerializationFailure'>
2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #172): list of attributes:
2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #178): add_note: <built-in method add_note of SerializationFailure object at 0x7f942a3c9cf0>
2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #178): args: ('could not serialize access due to read/write dependencies among transactions\nDETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.\nTIP: The transaction might succeed if retried.\n',)
2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #178): cursor: None
2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #178): diag: <psycopg2.extensions.Diagnostics object at 0x7f942a2b9e10>
2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #178): pgcode: 40001
2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #178): pgerror: ERROR: 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.

2024-08-20 22:17:04 DEBUG gm.logging [140274459512896 MainThread] (/usr/share/gnumed/Gnumed/pycommon/gmLog2.py::log_stack_trace() #178): with_traceback: <built-in method with_traceback of SerializationFailure object at 0x7f942a3c9cf0>

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