Re: Nested transactions support for code composability

On Sat, 15 Feb 2020, 19:38 Daniel Fortunov, <postgresql(at)danielfortunov(dot)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