psycopg2: proper positioning of .commit() within try: except: blocks - Mailing list psycopg

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]
withdefault [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
serializeaccess 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/writedependencies 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
SerializationFailureobject 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/writedependencies 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
at0x7f942a2b9e10> 
    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
toread/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
ofSerializationFailure object at 0x7f942a3c9cf0> 

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