MIME-Version: 1.0
References: <eb5cc5d20c520e32b5963a4044b70af1@paolodestefani.it>
 <CA+mi_8bXjHTrzDLQ+n_6K2MHVsr0J32X1HA28Ey9O+-Zm6JaeQ@mail.gmail.com>
 <9a99d2d110b121542d1ea0a05066858b@paolodestefani.it>
In-Reply-To: <9a99d2d110b121542d1ea0a05066858b@paolodestefani.it>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Wed, 13 Oct 2021 13:50:05 +0200
Message-ID: 
 <CA+mi_8afF2jMifiLSvL99+Ztupy1me7xDNAv0g47WxHWbwN9vw@mail.gmail.com>
Subject: Re: psycopg3 transactions
To: Paolo De Stefani <paolo@paolodestefani.it>
Cc: Psycopg <psycopg@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bmi_8afF2jMifiLSvL99%2BZtupy1me7xDNAv0g47WxHWbwN9vw%40mail.gmail.com>
Precedence: bulk

On Wed, 13 Oct 2021 at 13:09, Paolo De Stefani <paolo@paolodestefani.it> wrote:>
> Well, everything is clear..
>
> BUT
>
> I don't understand why a new transaction (committed while in the 'with'
> context) is not commited (whitout any message) because a previous select
> statement is not commited.

If you are in a transaction, then `conn.transaction()` doesn't start a
new transaction (implying that it commits the previous one). It will
start a sub-transaction.

> For example:
> ...
> >>> with con.cursor() as cur:
> ...     cur.execute('select code, description from system.app_user;')
> ...
> <psycopg.Cursor [TUPLES_OK] [INTRANS] (host=localhost port=5433
> user=pa_login_role database=test) at 0x35b3f40>

 As you can see from the cursor repr, here you have already started a
transaction, as per auto-transaction DBAPI default behaviour. This
type of transaction requires you to `commit()` at the end.

> IN PSQL:
>
> test=# select code, description from system.app_user;
>    code  | description
> --------+-------------
>   system | test
>   utente | ABCD
> (2 righe)

So far so good

> IN PYTHON:
> >>> with con.transaction():
> ...     with con.cursor() as cur:
> ...             cur.execute("update system.app_user set description =
> '1234' where code = 'utente';")
> ...
> <psycopg.Cursor [COMMAND_OK] [INTRANS] (host=localhost port=5433
> user=pa_login_role database=test) at 0x35ec360>

This didn't affect the previous transaction. It created a
sub-transaction with a savepoint and only discarded that savepoint.
The state of the connection is as it was before the block, i.e. in a
DBAPI transaction.

> >>> with con.cursor() as cur:
> ...     cur.execute('select code, description from system.app_user;')
> ...
> <psycopg.Cursor [TUPLES_OK] [INTRANS] (host=localhost port=5433
> user=pa_login_role database=test) at 0x35b3f40>
> >>> for i in cur:
> ...     print(i)
> ...
> ('system', 'test')
> ('utente', '1234')

This session is still in the middle of that active transaction, so it
can see the data it changed.

> IN PSQL:
> test=# select code, description from system.app_user;
>    code  | description
> --------+-------------
>   system | test
>   utente | ABCD
> (2 righe)

But the transaction has not been committed, so psql can't see it yet.


> So nothing changed for the psql connection even if a transaction was
> commited (i think...)
> if i close the connection
>
> IN PYTHON:
> >>> con.close()

You closed without the commit, so the transaction was discarded.

> I tought that the "with con.transaction():" start a new transaction that
> will be commeted anyway.
> I have to do a con.commit() BEFORE the with con.transaction() block
> instead. Or i have to use a transaction for every sql statement that is
> what i'm doing now.
> So i don't know if this is the normal/correct behaviour but it's very
> easy to get wrong and get unexpected results

The problem you are seeing comes from the DBAPI transaction that
started automatically because you run a command outside a
transaction() block. It has been confusing so far, with people
classically expecting to see changes when the connection is closed,
while the changes are discarded because no commit() has been executed.

Your sequence of statements resulted in the execution of:

> >>> with con.cursor() as cur:
> ...     cur.execute('select code, description from system.app_user;')
BEGIN;
SELECT ...

> >>> with con.transaction():
> ...     with con.cursor() as cur:
> ...             cur.execute("update system.app_user set description =
> '1234' where code = 'utente';")

SAVEPOINT x
UPDATE ...
RELEASE SAVEPOINT x

> >>> with con.cursor() as cur:
> ...     cur.execute('select code, description from system.app_user;')
SELECT

> >>> con.close()
CIAO - no COMMIT so everything was discarded

Note: If you run with `conn.transaction()` and no other command had
run before (i.e. the connection is IDLE, not INTRANS), then
conn.transaction() does indeed start a new transaction, with BEGIN,
but it will also run the COMMIT at the end of the block you would
expect, leaving the connection back in IDLE state. But because of the
SELECT at the beginning things go differently.


My understanding is that the problem comes from the BEGIN run with
your first select. I think, in order to make psycopg behave the way
you want, you should have your connection in autocommit. This would
have resulted in:

> >>> with con.cursor() as cur:
> ...     cur.execute('select code, description from system.app_user;')
SELECT ...

> >>> with con.transaction():
> ...     with con.cursor() as cur:
> ...             cur.execute("update system.app_user set description =
> '1234' where code = 'utente';")

BEGIN
UPDATE ...
COMMIT

> >>> with con.cursor() as cur:
> ...     cur.execute('select code, description from system.app_user;')
SELECT

> >>> con.close()
CIAO - but this time the data is safe.

I think we can improve the documentation there by extending more about
the effects of the interaction between DBAPI transactions and the
transaction() blocks. And things are definitely more intuitive if
transaction() is used in autocommit: we might want to advise people to
do that.

Suggestions are welcome.

-- Daniele