From Newsgroup: comp.lang.python

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
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

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")


--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

Am Sun, Sep 08, 2024 at 12:48:50PM +1200 schrieb Greg Ewing via Python-list:

On 8/09/24 9:20 am, Karsten Hilbert wrote:

try:
do something
except:
log something
finally:
.commit()

cadence is fairly Pythonic and elegant in that it ensures the
the .commit() will always be reached regardless of exceptions
being thrown or not and them being handled or not.

That seems wrong to me. I would have thought the commit should only
be attempted if everything went right.

It is only attempted when "everything" went right. The fault
in my thinking was what the "everything" might encompass.
When some SQL fails it won't matter whether I say
conn.commit() or conn.rollback() or, in fact, nothing at all
-- the (DB !) transaction will be rolled back in any case.
However, that reasoning missed this:

What if there's a problem in your code that causes a non-SQL-related exception when some but not all of the SQL statements in the
transaction bave been [-- even successfully --] issued?

Still, in this code pattern:

try:
do something
.commit()
except:
log something

it doesn't technically matter whether I say .commit or .rollback here:

.rollback()

... but ...

Doing an explicit rollback ensures that the transaction is always
rolled back if it is interrupted for any reason.

explicit is better than implicit ;-)
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
--- Synchronet 3.20a-Linux NewsLink 1.114

From Newsgroup: comp.lang.python

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!


--- Synchronet 3.20a-Linux NewsLink 1.114