MIME-Version: 1.0
References: 
 <CAH1rg6YS5n6PjiV=9cBr2E--oKXtUuL=S7fNPs+Y6BuwRA0iGA@mail.gmail.com>
 <CAH1rg6bzuNNcJGn94fLA2FTo08O3xxMDd-jos3g_5JEbhuTOqA@mail.gmail.com>
In-Reply-To: 
 <CAH1rg6bzuNNcJGn94fLA2FTo08O3xxMDd-jos3g_5JEbhuTOqA@mail.gmail.com>
From: Daniel Fortunov <postgresql@danielfortunov.com>
Date: Sat, 15 Feb 2020 19:38:32 +0000
Message-ID: 
 <CAH1rg6bgc7ukCNeQFF1acc6cRdk5Mh1yc6NSig5UkA8Vb0twoQ@mail.gmail.com>
Subject: Nested transactions support for code composability
To: psycopg@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004711d0059ea27881"
Precedence: bulk

--0000000000004711d0059ea27881
Content-Type: text/plain; charset="UTF-8"

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)

We have been using this in production for over a year in a fairly large
codebase that does a lot of database interactions directly using psycopg2
(without any other libraries layered on top).

I'd always though that this would be a good candidate for inclusion in
psycopg2, and now that it is stable, I'd like to solicit feedback.

This Transaction context manager replaces the DB-API model of handling
transactions (which I have found to be not very intuitive, with frequent
unexpected pitfalls, and does not lend itself to code composability) with a
context manager that provides:
1. Seamless support for nested transactions (implemented using Postgres
savepoints).
2. Inner transactions are truly independent (meaning that a unit of code
can decide whether to commit or rollback its own changes, without
influencing any outer transaction(s), or even needing to be aware if outer
transactions exist or not)
3. Transaction contexts function correctly inside "classic" transaction
management, meaning that you can implement this incrementally, starting at
the innermost libraries and working outward.

For a motivating example, please see my original email to this list (below).

For details on the library, see the README at
https://github.com/asqui/psycopg-nestedtransactions

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

Thanks in advance,
Daniel Fortunov


On Mon, 16 Jan 2017 at 23:26, Daniel Fortunov <
psycopg-list@danielfortunov.com> wrote:

> I'd like to implement support for nested transactions in psycopg2 using a
> context manager that internally uses postgres savepoints to implement the
> ability to nest transactions within each other, with sensible commit and
> rollback semantics.
>
> The end goal is to allow code composability through support for nested
> transactions.
>
> Before I go ahead and implement this I wanted to solicit feedback on the
> suggestion, because I find it hard to imagine I'm the first person to dream
> up this idea, so I imagine I'm missing something. (Perhaps it's already
> implemented somewhere that I've not found, or maybe it's not implemented
> because it's a terrible idea, and if so I'd like to understand why!)
>
> The rest of this email describes what I'm trying to achieve, using some
> motivating examples.
>
> Please share your thoughts and suggestions.
>
> Thanks in advance,
> Daniel Fortunov
>
> ------------------
>
> Suppose we have a database storing data about widgets, and we'd like to
> implement a function that does some updates on a few related widget tables.
> We'd like to do this atomically, so we wrap it in a transaction.
>
> Let's assume that `Transaction` is a psycopg transaction context manager
> with support for nested transactions. It begins a transaction on __enter__,
> commits it on __exit__, and rolls back if there is an exception. (This is a
> simplified version of what I propose to implement.)
>
> def update_widget(cxn, widget):
>     with Transaction(cxn):
>         cur = cxn.cursor()
>         cur.execute(...)  # Update table A
>         cur.execute(...)  # Update table B
>         cur.execute(...)  # Update table C
>
> So far this works fine, and is also currently achievable with the
> 'autocommit' paradigm. Nothing new here. Anyone can call this code, provide
> a connection, and get an atomic update of tables A, B, and C.
>
> Now, I'd like to write a maintenance job that updates a whole load of
> widgets. If the updates on any one widget fail, I'd still like the job to
> continue, and process as many widgets as it can. Specifically, I want to
> implement this job _without_ changing the existing maintain_widget()
> function, which is already in use.
>
> maintain_widgets(cxn):
>     with Transaction(cxn):
>         for widget in widgets:
>             try:
>                 update_widget(cxn, widget)
>             except:
>                 pass  # log exception and continue with other widgets
>
> The Transaction context manager would implement the begin/commit/rollback
> transaction semantics by beginning/releasing/rolling back to a savepoint,
> respectively. In this context, update_widget would seamlessly switch to
> using savepoints is it is not the outermost transaction.
>
> This is not possible to achieve with the 'autocommit' paradigm, because
> you need the ability to rollback the update of a single widget, and then
> continue the transaction that updates the rest. It is possible to implement
> with explicit savepoints, however that requires maintain_widget() to be
> updated, and breaks existing code, because it will no longer be possible to
> call maintain_widget outside of a transaction!
>
> Now I want to take it a level further, and write a database integration
> test for my widget maintainer script, which executes the test scenario
> within a transaction, guaranteeing that the database is left untouched
> after my test, regardless of whether the test succeeds, fails, or aborts
> unexpectedly (e.g. maybe the test is running in an interactive debugger and
> the I abort the debugging session! :-)
>
> def test_maintain_widget_successful_run(self):
>     with Transaction(cxn) as txn:
>         # Set up starting database state
>         # ....
>         maintaint_widgets(cxn)  # Call code under test
>         # Check database state against expectations
>         # ...
>         txn.rollback()  # Rollback all changes
>
> Note that for this final ROLLBACK to work, none of the called code is
> allowed to execute COMMIT at any point. Which is why this breaks down
> completely with the autocommit = False; cxn.commit() paradigm, because we'd
> be calling commit() explicitly all over the place!
>
> With the current paradigm you're forced to manage transactions at the
> uppermost level, which means callers to maintain_widgets() are obliged to
> call it within a pre-existing transaction in order to get correct behaviour
> (namely, atomic update of all three tables in the face of errors). And even
> then, this precludes having a test one level above, which executes that
> "uppermost level" code within a transaction and then rolls everything back!
>
> So, what am I missing? Am I the first to have this crazy idea? Is this
> already implemented? How do others solve the problem of code composability
> and transactions? Is this a terrible idea? (if so, why?)
>
> Feedback and comments welcome.
> Thanks for reading this far!
>

--0000000000004711d0059ea27881
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote"><div dir=3D"ltr"><div dir=3D"lt=
r"><div>Based on the motivations outlined in the below message to this list=
 (3 years ago), I have implemented a Transaction context manager that provi=
des what I think is the most intuitive way to deal with transactions. (Func=
tionally similar to xact / Django atomic, but available directly on psycopg=
2 connections)<br></div><div><br></div><div>We have been using this in prod=
uction for over a year in a fairly large codebase that does a lot of databa=
se interactions directly using psycopg2 (without any other libraries layere=
d on top).</div><div><br></div><div>I&#39;d always though that this would b=
e a good candidate for inclusion in psycopg2, and now that it is stable, I&=
#39;d like to solicit feedback.</div><div><br></div><div>This Transaction c=
ontext manager replaces the DB-API model of handling transactions (which I =
have found to be not very intuitive, with frequent unexpected pitfalls, and=
 does not lend itself to code composability) with a context manager that pr=
ovides:</div><div>1. Seamless support for nested transactions (implemented =
using Postgres savepoints).</div><div>2. Inner transactions are truly indep=
endent (meaning that a unit of code can decide whether to commit or rollbac=
k its own changes, without influencing any outer transaction(s), or even ne=
eding to be aware if outer transactions exist or not)</div><div>3. Transact=
ion contexts function correctly inside &quot;classic&quot; transaction mana=
gement, meaning that you can implement this incrementally, starting at the =
innermost libraries and working outward.</div><div><br></div><div>For a mot=
ivating example, please see my original email to this list (below).</div><d=
iv><br></div><div>For details on the library, see the README at <a href=3D"=
https://github.com/asqui/psycopg-nestedtransactions" target=3D"_blank">http=
s://github.com/asqui/psycopg-nestedtransactions</a></div><div><br></div><di=
v>Is this worthy of inclusion in psycopg2.extras (or elsewhere in psycopg2?=
)</div><div><br></div><div>Thanks in advance,</div><div>Daniel Fortunov</di=
v><div><br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" clas=
s=3D"gmail_attr">On Mon, 16 Jan 2017 at 23:26, Daniel Fortunov &lt;<a href=
=3D"mailto:psycopg-list@danielfortunov.com" target=3D"_blank">psycopg-list@=
danielfortunov.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex"><div dir=3D"ltr"><div>I&#39;d like to implement support f=
or nested transactions in psycopg2 using a context manager that internally =
uses postgres savepoints to implement the ability to nest transactions with=
in each other, with sensible commit and rollback semantics.<br></div><div><=
br></div><div>The end goal is to allow code composability through support f=
or nested transactions.</div><div><br></div><div>Before I go ahead and impl=
ement this I wanted to solicit feedback on the suggestion, because I find i=
t hard to imagine I&#39;m the first person to dream up this idea, so I imag=
ine I&#39;m missing something. (Perhaps it&#39;s already implemented somewh=
ere that I&#39;ve not found, or maybe it&#39;s not implemented because it&#=
39;s a terrible idea, and if so I&#39;d like to understand why!)</div><div>=
<br></div><div>The rest of this email describes what I&#39;m trying to achi=
eve, using some motivating examples.<br></div><div><br></div><div>Please sh=
are your thoughts and suggestions.</div><div><br></div><div>Thanks in advan=
ce,</div><div>Daniel Fortunov</div><div><br></div><div>------------------</=
div><div><br></div><div>Suppose we have a database storing data about widge=
ts, and we&#39;d like to implement a function that does some updates on a f=
ew related widget tables. We&#39;d like to do this atomically, so we wrap i=
t in a transaction.</div><div><br></div><div>Let&#39;s assume that `Transac=
tion` is a psycopg transaction context manager with support for nested tran=
sactions. It begins a transaction on __enter__, commits it on __exit__, and=
 rolls back if there is an exception. (This is a simplified version of what=
 I propose to implement.)</div><div><br></div><div>def update_widget(cxn, w=
idget):</div><div>=C2=A0 =C2=A0 with Transaction(cxn):</div><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 cur =3D cxn.cursor()</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 cur.execute(...) =C2=A0# Update table A</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 cur.execute(...) =C2=A0# Update table B<br></div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 cur.execute(...) =C2=A0# Update table C<br></div><div><br></d=
iv><div>So far this works fine, and is also currently achievable with the &=
#39;autocommit&#39; paradigm. Nothing new here. Anyone can call this code, =
provide a connection, and get an atomic update of tables A, B, and C.</div>=
<div><br></div><div>Now, I&#39;d like to write a maintenance job that updat=
es a whole load of widgets. If the updates on any one widget fail, I&#39;d =
still like the job to continue, and process as many widgets as it can. Spec=
ifically, I want to implement this job _without_ changing the existing main=
tain_widget() function, which is already in use.</div><div><br></div><div>m=
aintain_widgets(cxn):<br></div><div>=C2=A0 =C2=A0 with Transaction(cxn):</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0for widget in widgets:</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0try:</div><div>=C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0update_widget(cxn, widget)<=
/div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0except:</div>=
<div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0pass =C2=
=A0# log exception and continue with other widgets</div><div><br></div><div=
><div>The Transaction context manager would implement the begin/commit/roll=
back transaction semantics by beginning/releasing/rolling back to a savepoi=
nt, respectively. In this context, update_widget would seamlessly switch to=
 using savepoints is it is not the outermost transaction.</div></div><div><=
br></div><div>This is not possible to achieve with the &#39;autocommit&#39;=
 paradigm, because you need the ability to rollback the update of a single =
widget, and then continue the transaction that updates the rest. It is poss=
ible to implement with explicit savepoints, however that requires maintain_=
widget() to be updated, and breaks existing code, because it will no longer=
 be possible to call maintain_widget outside of a transaction!</div><div><b=
r></div><div>Now I want to take it a level further, and write a database in=
tegration test for my widget maintainer script, which executes the test sce=
nario within a transaction, guaranteeing that the database is left untouche=
d after my test, regardless of whether the test succeeds, fails, or aborts =
unexpectedly (e.g. maybe the test is running in an interactive debugger and=
 the I abort the debugging session! :-)</div><div><br></div><div>def test_m=
aintain_widget_successful_run(self):</div><div>=C2=A0 =C2=A0 with Transacti=
on(cxn) as txn:</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 # Set up starting dat=
abase state</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 # ....</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 maintaint_widgets(cxn) =C2=A0# Call code under test</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 # Check database state against expectat=
ions</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 # ...</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 txn.rollback() =C2=A0# Rollback all changes</div><div><br></d=
iv><div>Note that for this final ROLLBACK to work, none of the called code =
is allowed to execute COMMIT at any point. Which is why this breaks down co=
mpletely with the autocommit =3D False; cxn.commit() paradigm, because we&#=
39;d be calling commit() explicitly all over the place!</div><div><br></div=
><div>With the current paradigm you&#39;re forced to manage transactions at=
 the uppermost level, which means callers to maintain_widgets() are obliged=
 to call it within a pre-existing transaction in order to get correct behav=
iour (namely, atomic update of all three tables in the face of errors). And=
 even then, this precludes having a test one level above, which executes th=
at &quot;uppermost level&quot; code within a transaction and then rolls eve=
rything back!</div><div><br></div><div>So, what am I missing? Am I the firs=
t to have this crazy idea? Is this already implemented? How do others solve=
 the problem of code composability and transactions? Is this a terrible ide=
a? (if so, why?)</div><div><br></div><div>Feedback and comments welcome.</d=
iv><div>Thanks for reading this far!</div></div>
</blockquote></div>
</div>
</div></div>

--0000000000004711d0059ea27881--