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
<pa...@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
