Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mdamT-00016P-W0 for psycopg@arkaria.postgresql.org; Thu, 21 Oct 2021 16:19:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mdamS-0007U8-Gu for psycopg@arkaria.postgresql.org; Thu, 21 Oct 2021 16:19:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mdamS-0007Tz-2Y for psycopg@lists.postgresql.org; Thu, 21 Oct 2021 16:19:00 +0000 Received: from mail-lf1-x134.google.com ([2a00:1450:4864:20::134]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mdamO-0005yU-VV for psycopg@postgresql.org; Thu, 21 Oct 2021 16:18:59 +0000 Received: by mail-lf1-x134.google.com with SMTP id bq11so335129lfb.10 for ; Thu, 21 Oct 2021 09:18:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hagander-net.20210112.gappssmtp.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=xmKUWm7Bhf2Jyz9TCsugwYKiix/7/NYyzMaF4pLrrdk=; b=gb4DHfFWVGaSrzudq2VXJXVH/SCZZpBmN+DduAtV8Wwg2+PBDTClaVS8KZo2DKabPb MMFWhg5OEAsgQdIbxEe9QHxt8ly3dVXqyHRJt3w4e8I6Z7Pf9ta5x0tnyH4oHSUPxjeN tOPnx/bC+4h7NNrCMvvevqKYqQRk74R7hJ4ve9Fu+rd2xkTYVWa5NNg5uj/UirW9lfod TPTIRYVHHHSDzYLEM90QNC4I87iKdcOlPoG1rJKERsN7Y4HQlWx3NuCSmhjX0W9XE/q/ rF+u0Z7lkcNvU+Oi/Ilvt5c5nOqfVYwYA/M8DkWaq2JAsOzxTVQZQ65mPanWiGtgglAc rhLg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=xmKUWm7Bhf2Jyz9TCsugwYKiix/7/NYyzMaF4pLrrdk=; b=BHnXb9mpMBtK26AZJX7fhNj7j2O6oaSw09hQd7Eku84Vwb6OfgO0MJt3te518EE+eb 8Min4cutEaejdYf7UsJMRJPadRHyJvEMNYRAtwg3a3LssdufDPWB97k5bjj2qjSfPzWX LX1usjvEvdajdObpTbS/cuDH0oSTfJEAM92S+VnZSdQpiuq5MTqNaGUK7qMDPLHSRLgI Y8831i3xIen4KXgC33qTNl9fvvzpVQLiBP+Qn+gcQedlBS5FpEOGYMh4frm0RSS+xS/G xPgBxA21dRvbBBBMlB1qfIC5EoYv68DorRzyCY2cQ9tix2MpSgdAhcrFXF2mVLpveP9e qVdQ== X-Gm-Message-State: AOAM5316pIFCd8D83bQqtR89OrRyAOHSItYYDAxoXbXLZKAs62pDlLyF f0uQiQw8X/cFprY+ufGEgHiXXeH0nOkUD4x29p46TQ== X-Google-Smtp-Source: ABdhPJyK4Q6+si0suJCg6gXkTO1cqMq/WGka5sjLdbH5WjyonKj/H9wEZlKoeXlHCHRKAnbAHpfUa/oxw9aqR8KTCpU= X-Received: by 2002:ac2:5e37:: with SMTP id o23mr6366118lfg.496.1634833135671; Thu, 21 Oct 2021 09:18:55 -0700 (PDT) MIME-Version: 1.0 References: <9a99d2d110b121542d1ea0a05066858b@paolodestefani.it> <6683df3e65fe961aa478e61db7ad687d@paolodestefani.it> In-Reply-To: From: Magnus Hagander Date: Thu, 21 Oct 2021 18:18:43 +0200 Message-ID: Subject: Re: Re: psycopg3 transactions To: Daniele Varrazzo Cc: Karsten Hilbert , Paolo De Stefani , Psycopg Content-Type: multipart/alternative; boundary="00000000000051afca05cedf40d5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000051afca05cedf40d5 Content-Type: text/plain; charset="UTF-8" On Thu, Oct 21, 2021 at 2:00 PM Daniele Varrazzo wrote: > On Thu, 21 Oct 2021 at 12:06, Karsten Hilbert > wrote: > > > > > The behaviour of a dbapi connection, without context block, is to just > close the communication. The fact that this results in a rollback > stems > only from the behaviour of the server > > > > But that's the whole point? A driver should _not_ (by default) alter > the default > > behaviour of the other end, IMO, without extremely good reason. There > _is_ good > > reason for the transaction context manager, but not for the connection > context > > manager or plain use. > > The implicit BEGIN is also a surprising change from the "natural > course of events". Psycopg can still be used as a driver and not alter > the natural course of events. I have extended the documentation > (https://www.psycopg.org/psycopg3/docs/basic/usage.html#connection-context > ) > both highlighting the difference in behaviour you reported and > suggesting to not use 'with' in case more control is needed (using > psycopg more as a driver than as the end user). > Yes, the implicit BEGIN definitely is surprising -- but more easily detected. If the behaviour when used as a context block isn't actually defined and required by dbapi, I would suggest that the default of opening a new connection as context manager *wouldn't* do the BEGIN -- it would just open a connection in autocommit=on mode, mimicking the default on the server side. I would've personally liked that for non-context-manager one as well, but AIUI that's actually required to behave the other way by dbapi. But yes, the biggest issue I have with it is the part that's outlined in the Warning section of the docs there -- using it in different ways provides completely and fundamentally different behaviour in a way that can cause bad data. Things would be a lot more clear if the *connection* context didn't do *anything* with transactions whatsoever, and the *transaction* one did all of it. When it comes to transactions, explicit is always better than implicit IMNSHO. > > asking that the program to 'conn.commit()' explicitly seems an > unrequested, kinda ritual, final blessing. > > > > Exactly. > > > > > Sending an explicit ROLLBACK is an occurrence much more rare, > > > > Exactly, and thusly easily forgotten, with possibly dire consequences > > (in the case of default-commit, as opposed to default-rollback). > > On this I disagree. People forget to do things when they are a > repetitive common occurrence, not when they are extraordinary. > > I don't really see a disaster occurring there, especially because > porting code from psycopg2 results in pretty much the same operations. > Another thing to consider is the failure scenarios. If it implicitly rolls back at the end if you didn't do the right thing, then you will notice immediately, because none of your changes get saved to the database. If it implicitly commits at the end it will *look* fine if you didn't do the right thing, but more unpredictable things will happen if there's a failure and you might not notice until much much later that you actually had a transaction *at all*. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/ --00000000000051afca05cedf40d5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Thu, Oct 21, 2021 at 2:00 PM Danie= le Varrazzo <daniele.varra= zzo@gmail.com> wrote:
On Thu, 21 Oct 2021 at 12:06, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> > The behaviour of a dbapi connection, without context block, is to= just close the communication. The fact that this results in a rollback >= ; stems only from the behaviour of the server
>
> But that's the whole point?=C2=A0 =C2=A0A driver should _not_ (by = default) alter the default
> behaviour of the other end, IMO, without extremely good reason. There = _is_ good
> reason for the transaction context manager, but not for the connection= context
> manager or plain use.

The implicit BEGIN is also a surprising change from the "natural
course of events". Psycopg can still be used as a driver and not alter=
the natural course of events. I have extended the documentation
(https://www.psycopg.org/ps= ycopg3/docs/basic/usage.html#connection-context)
both highlighting the difference in behaviour you reported and
suggesting to not use 'with' in case more control is needed (using<= br> psycopg more as a driver than as the end user).


Yes, the implicit BEGIN definitely is surprising --= but more easily detected.

If the behaviour=C2=A0 = when used as a context block isn't actually defined and required by dba= pi, I would suggest that the default of opening a new connection as context= manager *wouldn't* do the BEGIN -- it would just open a connection in = autocommit=3Don mode, mimicking the default on the server side.
<= br>
I would've personally liked that for non-context-manager = one as well, but AIUI that's actually required to behave the other way = by dbapi.

But yes, the biggest issue I have w= ith it is the part that's outlined in the Warning section of the docs t= here -- using it in different ways provides completely and fundamentally di= fferent behaviour in a way that can cause bad data.

Things would be a lot more clear if the *connection* context didn&#= 39;t do *anything* with transactions whatsoever, and the *transaction* one = did all of it. When it comes to transactions, explicit is always better tha= n implicit IMNSHO.


> > asking that the program to 'conn= .commit()' explicitly seems an unrequested, kinda ritual, final blessin= g.
>
> Exactly.
>
> > Sending an explicit ROLLBACK is an occurrence much more rare,
>
> Exactly, and thusly easily forgotten, with possibly dire consequences<= br> > (in the case of default-commit, as opposed to default-rollback).

On this I disagree. People forget to do things when they are a
repetitive common occurrence, not when they are extraordinary.

I don't really see a disaster occurring there, especially because
porting code from psycopg2 results in pretty much the same operations.
<= /blockquote>

Another thing to consider is the failure sc= enarios.

If it implicitly rolls back at the end if= you didn't do the right thing, then you will notice immediately, becau= se none of your changes get saved to the database.

If it implicitly commits at the end it will *look* fine if you didn't = do the right thing, but more unpredictable things will happen if there'= s a failure and you might not notice until much much later that you actuall= y had a transaction *at all*.=C2=A0


--
= =C2=A0Magnus Hagander
=C2=A0Me: https://www.hagander.net/
=C2=A0Work: https://www.redpill-linpro.co= m/
--00000000000051afca05cedf40d5--