Path: ...!news-out.netnews.com!s1-1.netnews.com!peer03.iad!feed-me.highwinds-media.com!news.highwinds-media.com!nntp.club.cc.cmu.edu!fu-berlin.de!uni-berlin.de!not-for-mail
From: Rob Cliffe <rob.cliffe@btinternet.com>
Newsgroups: comp.lang.python
Subject: Re: psycopg2: proper positioning of .commit() within try: except:
 blocks
Date: Sat, 7 Sep 2024 17:11:57 +0100
Lines: 52
Message-ID: <mailman.43.1725725560.2917.python-list@python.org>
References: <Ztx1sZMGTnA3eLP1@hermes.hilbert.loc>
 <610d3e06-63f3-459a-b060-e0d2737dfa9e@btinternet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Trace: news.uni-berlin.de yj3v2mzC8D0yXNnWg7JdhAo0G9ZDQzuAkidsmjkJu1fA==
Cancel-Lock: sha1:wH6TTJpxJuH1BRVqGMZchGY1JIg= sha256:qvoT6ff7UrLmDONiQxv2Q7CM3Z4OiITxpgoOCdz/7DI=
Return-Path: <rob.cliffe@btinternet.com>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
Authentication-Results: mail.python.org; dkim=pass
 reason="2048-bit key; unprotected key"
 header.d=btinternet.com header.i=@btinternet.com header.b=hTG+6+/Z;
 dkim-adsp=pass; dkim-atps=neutral
X-Spam-Status: OK 0.159
X-Spam-Level: *
X-Spam-Evidence: '*H*': 0.69; '*S*': 0.01; 'dependencies': 0.09;
 'fails': 0.09; 'karsten': 0.09; 'canceled': 0.16; 'commit': 0.16;
 'failed,': 0.16; 'hilbert': 0.16; 'idiom': 0.16; 'insights,':
 0.16; 'read/write': 0.16; 'subject:() ': 0.16; 'subject:proper':
 0.16; 'subject:try': 0.16; 'tries': 0.16; 'wrote:': 0.16;
 'reached': 0.17; 'to:addr:python-list': 0.20; 'all,': 0.20;
 'code': 0.23; 'skip:p 30': 0.23; 'thinking': 0.28; 'header:User-
 Agent:1': 0.30; 'seem': 0.31; 'raise': 0.31; 'python-list': 0.32;
 'received:192.168.1': 0.32; 'header:In-Reply-To:1': 0.34;
 'failed': 0.35; 'yes,': 0.35; 'received:192.168': 0.37; 'way':
 0.38; 'could': 0.38; 'put': 0.38; 'thanks': 0.38; 'wishes': 0.40;
 'learn': 0.40; 'should': 0.40; 'in,': 0.60; 'best': 0.61; 'today':
 0.61; 'dear': 0.62; 'transaction': 0.64; 'among': 0.65; 'well':
 0.65; 'now,': 0.67; 'interested': 0.68; 'during': 0.69; 'and,':
 0.69; 'care': 0.71; 'free': 0.72; 'head': 0.73; 'placement': 0.81;
 'catch': 0.84; 'detail:': 0.84; 'exceptions': 0.84; 'rob': 0.84;
 'subject:within': 0.84; 'transactions': 0.84; 'safely': 0.91
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=btinternet.com;
 s=btmx2012311; t=1725725559; 
 bh=d89jgFPwRLDuHGCywhgtoNQbB5jYiQPQ6TV4nCCcf4E=;
 h=Message-ID:Date:MIME-Version:Subject:To:References:From:In-Reply-To;
 b=hTG+6+/Z7ZjG2gDjTdYWUEaDBznjGoCpeoH9BR1R47R68ZUtftD3gbwq52JWCn1fDN8TSSa0/Czc/ZVxNUeIPKLop6jBdBRJmiNCNslln8CFMZHrxl1kEUhKASHUVz82A7TfrTKhNhQdrYS4mBPLbA2/RHZ6mqKscqFF4x/Lt82LUOa4+016uMkwuJltVRZsBd4pxLuMTCkJ5ytf6FrY726lOQ8ylrO6suLZaKuDdgEAZb8isF+04mDHrQ8OtFydzYEBTxf+a/XzNnR0Ci0nt+m+Zx/bmxIS0VmA3JpasTvPMjFCYjb4PLStipKIBoplTUdOy+FQfGtArk80yCBJgw==
X-SNCR-Rigid: 669441D80669D58C
X-Originating-IP: [86.175.96.244]
X-OWM-Source-IP: 86.175.96.244
X-OWM-Env-Sender: rob.cliffe@btinternet.com
X-VadeSecure-score: verdict=clean score=0/300, class=clean
X-RazorGate-Vade: gggruggvucftvghtrhhoucdtuddrgeeftddrudeifedguddtudcutefuodetggdotefrodftvfcurfhrohhfihhlvgemuceutffkvffkuffjvffgnffgvefqofdpqfgfvfenuceurghilhhouhhtmecufedtudenucenucfjughrpegtkfffgggfuffvfhfhjgesrgdtreertddvjeenucfhrhhomheptfhosgcuvehlihhffhgvuceorhhosgdrtghlihhffhgvsegsthhinhhtvghrnhgvthdrtghomheqnecuggftrfgrthhtvghrnhepvdfgiefhueeiieejjeeiueeiteetkedvvefhjeejtdduuefgvdelkeffhfevhedtnecukfhppeekiedrudejhedrleeirddvgeegnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehhvghloheplgduledvrdduieekrddurddvtdekngdpihhnvghtpeekiedrudejhedrleeirddvgeegpdhmrghilhhfrhhomheprhhosgdrtghlihhffhgvsegsthhinhhtvghrnhgvthdrtghomhdpnhgspghrtghpthhtohepuddprhgtphhtthhopehphihthhhonhdqlhhishhtsehphihthhhonhdrohhrghdprhgvvhfkrfephhhoshhtkeeiqddujeehqdeliedqvdeggedrrhgrnhhgvgekiedqudejhedrsghttggvnhhtrhgrlhhplhhushdrtghomhdprghuthhhpghushgvrheprhhosgdrtghlihhffhgvsegsthhinhhtvghrnhgvthdrtghomhdpghgvohfkrfepifeupdfovfetjfhoshhtpegsthhprhgurhhgohdtuddu
X-RazorGate-Vade-Verdict: clean 0
X-RazorGate-Vade-Classification: clean
X-SNCR-hdrdom: btinternet.com
X-SNCR-hdrdom: btinternet.com
User-Agent: Mozilla Thunderbird
Content-Language: en-GB
In-Reply-To: <Ztx1sZMGTnA3eLP1@hermes.hilbert.loc>
X-Content-Filtered-By: Mailman/MimeDel 2.1.39
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.39
Precedence: list
List-Id: General discussion list for the Python programming language
 <python-list.python.org>
List-Unsubscribe: <https://mail.python.org/mailman/options/python-list>,
 <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <https://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <https://mail.python.org/mailman/listinfo/python-list>,
 <mailto:python-list-request@python.org?subject=subscribe>
X-Mailman-Original-Message-ID: <610d3e06-63f3-459a-b060-e0d2737dfa9e@btinternet.com>
X-Mailman-Original-References: <Ztx1sZMGTnA3eLP1@hermes.hilbert.loc>
X-Received-Bytes: 6777
Bytes: 6947

On 07/09/2024 16:48, Karsten Hilbert via Python-list wrote:
> 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
I would put the curs.execute and the conn.commit in separate 
try...except blocks.  That way you know which one failed, and can put 
appropriate info in the log, which may help trouble-shooting.
(The general rule is to keep try...except blocks small.  And of course 
only catch the exceptions you are interested in, which you seem to be 
already doing.)
Best wishes
Rob Cliffe