MIME-Version: 1.0
Date: Wed, 13 Oct 2021 13:09:49 +0200
From: Paolo De Stefani <paolo@paolodestefani.it>
To: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Cc: Psycopg <psycopg@postgresql.org>
Subject: Re: psycopg3 transactions
In-Reply-To: 
 <CA+mi_8bXjHTrzDLQ+n_6K2MHVsr0J32X1HA28Ey9O+-Zm6JaeQ@mail.gmail.com>
References: <eb5cc5d20c520e32b5963a4044b70af1@paolodestefani.it>
 <CA+mi_8bXjHTrzDLQ+n_6K2MHVsr0J32X1HA28Ey9O+-Zm6JaeQ@mail.gmail.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <9a99d2d110b121542d1ea0a05066858b@paolodestefani.it>
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/9a99d2d110b121542d1ea0a05066858b%40paolodestefani.it>
Precedence: bulk

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.
For example:

IN PYTHON:
PS C:\Users\p.destefani> python
Python 3.8.9 (tags/v3.8.9:a743f81, Apr  6 2021, 14:02:34) [MSC v.1928 64 
bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg
>>> con = psycopg.connect(host='localhost', port=5433, dbname='test', 
>>> user='pa_login_role', password='pa_login_password')
>>> 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', 'ABCD')
>>> 

IN PSQL:
PS C:\Program Files\PostgreSQL\13\bin> .\psql.exe -U postgres -d test -h 
localhost -p 5433
Inserisci la password per l'utente postgres:
psql (13.4)
ATTENZIONE: Il code page della console (850) differisce dal code page
             di Windows (1252). I caratteri a 8-bit potrebbero non
             funzionare correttamente. Vedi le pagine di riferimento
             psql "Note per utenti Windows" per i dettagli.
Digita "help" per avere un aiuto.

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

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

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

So nothing changed for the psql connection even if a transaction was 
commited (i think...)
if i close the connection

IN PYTHON:
>>> con.close()
>>> 

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

i don't see the modified description!
if i start a new connection:

IN PYTHON:
>>> 
>>> con = psycopg.connect(host='localhost', port=5433, dbname='test', 
>>> user='pa_login_role', password='pa_login_password')
>>> 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 0x35ec2c0>
>>> for i in cur:
...     print(i)
...
('system', 'test')
('utente', 'ABCD')

looks like i didn't modify any description!

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


Il 11/10/2021 21:03 Daniele Varrazzo ha scritto:
> Hi Paolo,
> 
> in psycopg 3, the normal transaction behaviour demanded by the dbapi
> is by default enabled. So, even if you don't use `conn.transaction()`,
> a transaction will be started anyway (just, it won't finish at the end
> of a block but will need an explicit commit).
> 
> So,
> 
>     with con.cursor() as cur:
>         cur.execute("SELECT * FROM table;")
> 
> does actually start a transaction, and if you don't commit it will not
> be terminated.
> 
> If you want to use *only* `conn.transaction()` to manage your
> transactions, and leave everything outside a block as autocommit, you
> need an autocommit connection, which you can create passing
> `autocommit=True` on `connect()` or setting `conn.autocommit = True`
> after connection.
> 
> Does it help?
> 
> Cheers
> 
> -- Daniele
> 
> On Mon, 11 Oct 2021 at 20:01, Paolo De Stefani 
> <paolo@paolodestefani.it> wrote:
>> 
>> Hi all
>> 
>> In psycopg3 i read and understood the new transaction management
>> behaviour. With the use of context managers i have to do something 
>> like
>> this:
>> 
>> con = psycopg.connect()
>> with con.transaction():
>>      with con.cursor() as cur:
>>          cur.execute("INSERT INTO table VALUES (1, 2, 3);")
>> 
>> and this works as expected.
>> But if i don't need a transaction because i don't need to commit
>> anything i can do something like this:
>> 
>> with con.cursor() as cur:
>>      cur.execute("SELECT * FROM table;")
>> 
>> BUT if a do a select without a transaction the next command that 
>> require
>> a transaction don't works until i do a specific commit
>> 
>> with con.transaction():
>>      with con.cursor() as cur:
>>          cur.execute("DELETE FROM table;")
>> 
>> the delete is effective only for the current connection, i mean other 
>> db
>> user continue to see the <table> without the delete command
>> modifications
>> Looks like the previous select statement (uncommited) block following
>> delete statement even if i use the with con.transaction() statement.
>> If a do a con.commit() everything works as expected.
>> 
>> That means i need to use a transaction even for a select statement.
>> I can't use autocommit connection in my application.
>> 
>> Is it correct or am i losing anything ???
>> 
>> 
>> --
>> Paolo De Stefani
>> 
>> 

-- 
Paolo De Stefani