MIME-Version: 1.0
References: <eb5cc5d20c520e32b5963a4044b70af1@paolodestefani.it>
In-Reply-To: <eb5cc5d20c520e32b5963a4044b70af1@paolodestefani.it>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Mon, 11 Oct 2021 21:03:58 +0200
Message-ID: 
 <CA+mi_8bXjHTrzDLQ+n_6K2MHVsr0J32X1HA28Ey9O+-Zm6JaeQ@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_8bXjHTrzDLQ%2Bn_6K2MHVsr0J32X1HA28Ey9O%2B-Zm6JaeQ%40mail.gmail.com>
Precedence: bulk

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