On Sat, 15 Feb 2020, 19:38 Daniel Fortunov, <postgre...@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
