public inbox for [email protected]  

help / color / mirror / Atom feed

From: Paolo De Stefani <[email protected]>
To: Psycopg <[email protected]>
Subject: psycopg3 transactions
Date: Mon, 11 Oct 2021 20:01:09 +0200
Message-ID: <[email protected]> (raw)

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

view thread (28+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: psycopg3 transactions
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox