public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniele Varrazzo <[email protected]>
To: Paolo De Stefani <[email protected]>
Cc: Psycopg <[email protected]>
Subject: Re: psycopg3 transactions
Date: Mon, 11 Oct 2021 21:03:58 +0200
Message-ID: <CA+mi_8bXjHTrzDLQ+n_6K2MHVsr0J32X1HA28Ey9O+-Zm6JaeQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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 <[email protected]> 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
>
>

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], [email protected]
  Subject: Re: psycopg3 transactions
  In-Reply-To: <CA+mi_8bXjHTrzDLQ+n_6K2MHVsr0J32X1HA28Ey9O+-Zm6JaeQ@mail.gmail.com>

* 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