Re: psycopg3 transactions

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(at)paolodestefani(dot)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