Thread: [psycopg] Nested transactions support for code composability

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!

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 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