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

Karsten Hilbert Karsten.Hilbert at gmx.net
Sat Sep 7 11:48:01 EDT 2024


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


More information about the Python-list mailing list