Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j3ITh-0003k8-Dr for psycopg@arkaria.postgresql.org; Sun, 16 Feb 2020 11:52:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j3ITf-0001uw-87 for psycopg@arkaria.postgresql.org; Sun, 16 Feb 2020 11:52:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j3ITe-0001uo-IV for psycopg@lists.postgresql.org; Sun, 16 Feb 2020 11:52:46 +0000 Received: from mail-lf1-x12b.google.com ([2a00:1450:4864:20::12b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j3ITb-0001br-Oc for psycopg@postgresql.org; Sun, 16 Feb 2020 11:52:45 +0000 Received: by mail-lf1-x12b.google.com with SMTP id y19so9823587lfl.9 for ; Sun, 16 Feb 2020 03:52:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=w3DpgSKm6aZx5oWN9qbyNli1mOcFQUWITs/xUwzq3CM=; b=E6/bTJ5fhzcSCMQAzfwzEyO89PdbuCxYp51gA/0L2F+SyyQ7Q6/U4OkpocynOQMXor PEjmgb+ElFa2DdzJ7WNffeD/m0MnYVsB5A5l3B1iELEKLv9jwCoVLHeIjzDUrOywWss4 q1HgjyX9IWFRZ8doLGBLTuDAIuw8W9Ev3rPK26q5pSvJC+Otj9aQkkU/beFn6sWJvReW Wwkn8uQtMlbtK2TCNJd3obe+f7cYHJXWap7kCRVqZ4PCkZwT4EL6R9F0iLb+FvdckgVE DFqCNr6O8IoMWOkbCZS2zMNT8hfw3gwjqViWDQm7aHY89HsclY2IQvOLkmR8Lz+EDqWu BuZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=w3DpgSKm6aZx5oWN9qbyNli1mOcFQUWITs/xUwzq3CM=; b=cf7XO7j/UAjzR2ERIIsyqgfqq9XjYBcq0weU7BHAvwUoaBhENEXfmw4HLeIZcRUUjl cBLrAXC+WfXpvNamF8Pe1Byfnv+T40BcoJPHSFEoV6V2A2+d7FkgZzf/oGWyVUNhrIjk HTZtO8llxPtMUvRnOZDRYE5D0MEOBUvfL/XkhmNaBiXCUt9iQePqoJI3GLyr2f/NZARn 0Y6n1SomJyuAeYk8qNSHaHnKotHV7fpCtxmzmcCUXfE+0iojgrPjDgfdicd9vioHfrdX wTYY1MxsxvUkzjCKOkg5bLNAxJ4KcENgjeDLkU6AFTwP8duP1jF4sN+scbKNbrXFdR+1 gsmA== X-Gm-Message-State: APjAAAXeyvExkU9p+nF1NiTqgPPDDoBbCCoPR+T4TFLW77ZhBBo/GUmQ L3kqbWYfg76KTxO9mw6cLgNAXZmlGg12xFpZH83diBvF X-Google-Smtp-Source: APXvYqxg4yIuIBIsIYtURn+sGtAtQDG61LDWB6OVEcUabqnLf0CQyiKffOLLfpXTN/bRMaZBq8MksU1Yerfanl6SDv0= X-Received: by 2002:a19:7401:: with SMTP id v1mr5776279lfe.129.1581853960738; Sun, 16 Feb 2020 03:52:40 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Daniele Varrazzo Date: Sun, 16 Feb 2020 11:52:29 +0000 Message-ID: Subject: Re: Nested transactions support for code composability To: Daniel Fortunov Cc: psycopg@postgresql.org Content-Type: multipart/alternative; boundary="0000000000006a9b35059eb01322" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000006a9b35059eb01322 Content-Type: text/plain; charset="UTF-8" On Sat, 15 Feb 2020, 19:38 Daniel Fortunov, wrote: > Based on the motivations outlined in the below message to this list (3 > years ago), I have implemented a Transaction context manager that provides > what I think is the most intuitive way to deal with transactions. > (Functionally similar to xact / Django atomic, but available directly on > psycopg2 connections) > ... > Is this worthy of inclusion in psycopg2.extras (or elsewhere in psycopg2?) > Hello Daniel, thank you for the idea, and I would like to provide such functionality in psycopg. My doubts are around the interface: not so much relatively to your code/design, but rather relatively to the DBAPI requirements. In short, there had been an emerging pattern of drivers using the connection context manager to manage transactions (see e.g. psycopg behaviour: https://www.psycopg.org/docs/usage.html#with-statement). This is sort of a de-facto behaviour, but it never got written down in the DBAPI specs https://mail.python.org/pipermail/db-sig/2012-November/thread.html In hindsight, I think it was the wrong decision: - people expects Python objects to be closed on exit. Closing the tx but not the connection is a surprising behaviour - providing different connection factories, for instance a 'with pool.getconn():... ' which would return the connection to the pool on exit - a rather elegant design - would require an extra level of with, see the thoughts in https://github.com/psycopg/psycopg2/pull/17 So my thoughts are mostly: what is the best interface psycopg can present to offer: - transactions when requested (on autocommit requests too, see https://github.com/psycopg/psycopg2/issues/941) - nested transactions - a non surprising behaviour on __exit__ In my ideal world, the behaviour should be something like: with connect('dsn') as conn: # or with pool.getconn() etc. with conn.transaction() as tx: with conn.cursor() as curs: stuff() # conn.transaction() might be called again to create savepoint tons # tx might expose commit()/rollback() for explicit management # dispose if the cursor # commit on success, rollback on error # close the connection, return it to the pool, etc Would it be possible to introduce this behaviour in psycopg2 in a way that is not awful, which wouldn't break programs written for the 2.5-2.8 behaviour? I don't see an obvious way to do it. If not, and we had to introduce a non backwards compatible change, does the design above seem optimal (complete and easy to use)? For a full disclosure: in the next months my work situation should change and I might be able to start working on a new major psycopg version, so psycopg3 might actually become a real thing, if there is interest for it. -- Daniele --0000000000006a9b35059eb01322 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Sat, 15 Feb 2020, 19:38 Daniel Fortunov, <postgresql@danielfortunov.com> wrote:
Based on the motivations outlined in the = below message to this list (3 years ago), I have implemented a Transaction = context manager that provides what I think is the most intuitive way to dea= l with transactions. (Functionally similar to xact / Django atomic, but ava= ilable directly on psycopg2 connections)
<= /blockquote>

...


Is this = worthy of inclusion in psycopg2.extras (or elsewhere in psycopg2?)

<= div dir=3D"auto">Hello Daniel, thank you for the idea, and I would like to = provide such functionality in psycopg.

My doubts are around the interface: not so much relatively t= o your code/design, but rather relatively to the DBAPI requirements.
<= div dir=3D"auto">
In short, there had been an em= erging pattern of drivers using the connection context manager to manage tr= ansactions (see e.g. psycopg behaviour: https://www.psycopg.org/docs/usage.html#with-statement). This is s= ort of a de-facto behaviour, but it never got written down in the DBAPI spe= cs


=
In hindsight, I think it was the wrong decision:

- people expects Python ob= jects to be closed on exit. Closing the tx but not the connection is a surp= rising behaviour=C2=A0
- providing different connect= ion factories, for instance a 'with pool.getconn():... ' which woul= d return the connection to the pool on exit=C2=A0- a rather elegant design -=C2=A0 would require an extr= a level of with, see the thoughts in=C2=A0http= s://github.com/psycopg/psycopg2/pull/17

So my thoughts are mostly: what is the best interface p= sycopg can present to offer:

- transactions when requested (= on autocommit requests too, see=C2=A0https://github.com/psycopg/psycopg2/issues/941)<= /div>
- nested transactions=C2=A0
-= a non surprising behaviour on __exit__

In my ideal world, the behaviour should be something like:<= /div>

=C2=A0 =C2=A0 with conne= ct('dsn') as conn:=C2=A0 # or with pool.getconn() etc.
=C2=A0 =C2=A0 =C2=A0 =C2=A0 with conn.transaction() as tx:
=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 with conn.curso= r() as curs:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 stuff()
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # conn.transaction() might be called again = to create savepoint tons
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 # tx might expose commit()/rollback() for expl= icit management=C2=A0

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # dispose if the cursor
=C2=A0 =C2=A0 =C2=A0 =C2=A0 # commit on success, rollback on = error
=C2=A0 =C2=A0 # close the connection, return i= t to the pool, etc

Would= it be possible to introduce this behaviour in psycopg2 in a way that is no= t awful, which wouldn't break programs written for the 2.5-2.8 behaviou= r? I don't see an obvious way to do it.

If not, and we had to introduce a non backwards compati= ble change, does the design above seem optimal (complete and easy to use)?<= /div>

For a full disclosure: i= n the next months my work situation should change and I might be able to st= art working on a new major psycopg version, so psycopg3 might actually beco= me a real thing, if there is interest for it.

-- Daniele=C2=A0

--0000000000006a9b35059eb01322--