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 =3D psycopg2.connection(...)
curs =3D 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 r=
ead/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commi=
t 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 n=
ot 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 On=
Init
frame =3D gmTopLevelFrame(None, id =3D -1, title =3D _('GNUmed client=
'), size =3D (640, 440))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^=
^^^^^^^^^^^^^^^^
File "/usr/share/gnumed/Gnumed/wxpython/gmGuiMain.py", line 191, in __i=
nit__
self.LayoutMgr =3D gmHorstSpace.cHorstSpaceLayoutMgr(self, -1)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/share/gnumed/Gnumed/wxpython/gmHorstSpace.py", line 215, in =
__init__
self.top_panel =3D gmTopPanel.cTopPnl(self, -1)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/share/gnumed/Gnumed/wxpython/gmTopPanel.py", line 52, in __i=
nit__
wxgTopPnl.wxgTopPnl.__init__(self, *args, **kwargs)
File "/usr/share/gnumed/Gnumed/wxGladeWidgets/wxgTopPnl.py", line 33, i=
n __init__
self._TCTRL_patient_selector =3D cActivePatientSelector(self, wx.ID_A=
NY, "")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^=
^^^^^
File "/usr/share/gnumed/Gnumed/wxpython/gmPatSearchWidgets.py", line 12=
95, 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 r=
ead/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commi=
t attempt.
TIP: The transaction might succeed if retried.

2024-08-20 22:17:04 INFO gm.cfg [140274204403392 UpdChkThrea=
d-148728] (/usr/share/gnumed/Gnumed/pycommon/gmCfg.py::get2() #148): crea= ting 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_con= nection_loss() #667): interpreting: could not serialize access due to read= /write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commi=
t 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): e= xception: could not serialize access due to read/write dependencies among = transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commi=
t 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): t= ype: <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): l=
ist 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 0x7= f942a3c9cf0>
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 t= ransactions\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 dependencie=
s among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commi=
t 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 o= bject at 0x7f942a3c9cf0>

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

--- MBSE BBS v1.0.8.4 (Linux-x86_64)
* Origin: ---:- FTN<->UseNet Gate -:--- (3:633/280.2@fidonet)

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

Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote or quoted:

except PSYCOPG2-Exception:

The expression after "except" seems to be calculating a difference.

(Lately, I've been seeing some super long subject lines around here,
so here's a thought on how this one could've been worded shorter:
"Positioning of a database .commit() within a try block")



--- MBSE BBS v1.0.8.4 (Linux-x86_64)
* Origin: Stefan Ram (3:633/280.2@fidonet)

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

Jon Ribbens <jon+usenet@unequivocal.eu> wrote or quoted:

What if there's an exception in your exception handler? I'd put the
rollback in the 'finally' handler, so it's always called.

To make this happen, you'd better kick off with the rollback right
at the beginning of the "finally:" block. Otherwise, some gnarly
exception might rear its ugly head before the rollback gets called!



--- MBSE BBS v1.0.8.4 (Linux-x86_64)
* Origin: Stefan Ram (3:633/280.2@fidonet)