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