Subject: psycopg3 transactions

From: Paolo De Stefani <[email-masked]>
Date: Mon, 11 Oct 2021 20:01:09 +0200

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

---

From: Daniele Varrazzo <[email-masked]>
Date: Mon, 11 Oct 2021 21:03:58 +0200

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,

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

---

From: Paolo De Stefani <[email-masked]>
Date: Wed, 13 Oct 2021 13:09:49 +0200

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')
...     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)

>>> 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')
...     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

-- 
Paolo De Stefani

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 13 Oct 2021 13:50:05 +0200

On Wed, 13 Oct 2021 at 13:09, Paolo De Stefani <[email-masked]> wrote:>
> statement is not commited.

If you are in a transaction, then `conn.transaction()` doesn't start a
new transaction (implying that it commits the previous one). It will
start a sub-transaction.

> user=pa_login_role database=test) at 0x35b3f40>

 As you can see from the cursor repr, here you have already started a
transaction, as per auto-transaction DBAPI default behaviour. This
type of transaction requires you to `commit()` at the end.

> test=# select code, description from system.app_user;

So far so good

> user=pa_login_role database=test) at 0x35ec360>

This didn't affect the previous transaction. It created a
sub-transaction with a savepoint and only discarded that savepoint.
The state of the connection is as it was before the block, i.e. in a
DBAPI transaction.

> user=pa_login_role database=test) at 0x35b3f40>

This session is still in the middle of that active transaction, so it
can see the data it changed.

> test=# select code, description from system.app_user;

But the transaction has not been committed, so psql can't see it yet.

> if i close the connection

You closed without the commit, so the transaction was discarded.

> easy to get wrong and get unexpected results

The problem you are seeing comes from the DBAPI transaction that
started automatically because you run a command outside a
transaction() block. It has been confusing so far, with people
classically expecting to see changes when the connection is closed,
while the changes are discarded because no commit() has been executed.

> ...     cur.execute('select code, description from system.app_user;')
BEGIN;
SELECT ...

> '1234' where code = 'utente';")

SAVEPOINT x
UPDATE ...
RELEASE SAVEPOINT x

> ...     cur.execute('select code, description from system.app_user;')
SELECT

> >>> con.close()
CIAO - no COMMIT so everything was discarded

Note: If you run with `conn.transaction()` and no other command had
run before (i.e. the connection is IDLE, not INTRANS), then
conn.transaction() does indeed start a new transaction, with BEGIN,
but it will also run the COMMIT at the end of the block you would
expect, leaving the connection back in IDLE state. But because of the
SELECT at the beginning things go differently.

My understanding is that the problem comes from the BEGIN run with
your first select. I think, in order to make psycopg behave the way
you want, you should have your connection in autocommit. This would

> ...     cur.execute('select code, description from system.app_user;')
SELECT ...

> '1234' where code = 'utente';")

BEGIN
UPDATE ...
COMMIT

> ...     cur.execute('select code, description from system.app_user;')
SELECT

> >>> con.close()
CIAO - but this time the data is safe.

I think we can improve the documentation there by extending more about
the effects of the interaction between DBAPI transactions and the
transaction() blocks. And things are definitely more intuitive if
transaction() is used in autocommit: we might want to advise people to
do that.

Suggestions are welcome.

-- Daniele

---

From: Reuben Rissler <[email-masked]>
Date: Wed, 13 Oct 2021 08:20:54 -0400

<snip>
> transaction() is used in autocommit: we might want to advise people to
I find this new behavior of psycopg(3) more sane, even if less 
?intuitive?, than the behavior of psycopg2. I had run across the fact 
that you can close the cursor and then commit afterwards. I found this 
odd, as I would have guessed closing a cursor with uncomitted data would 
have thrown the data away.

Disclaimer: I haven't actually used the new psycopg. I just actively 
follow this mail list while continuing to use psycopg2.

Reuben Rissler

---

From: Paolo De Stefani <[email-masked]>
Date: Wed, 13 Oct 2021 15:20:54 +0200

> transaction() is used in autocommit: we might want to advise people to

I think the problem is only for people like me that come from psycopg2.

I was used to wrote a "with con.cursor() as cur:" that now i hato to 
replace with:

with con.transaction():
     with con.cursor() as cur:
         cur.execute("ANY SQL STATEMENT")

and everything works as expected (or i expect...)

Thanks for clarifying

-- 
Paolo De Stefani

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 13 Oct 2021 15:46:46 +0200

> and everything works as expected (or i expect...)

You can even write:

    with con.transaction():
        con.execute("ANY SQL STATEMENT")

But is it even useful to wrap a transaction around any single
statement? I still think you'd be better off with an autocommit
connection.

-- Daniele

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 13 Oct 2021 19:28:29 +0200

On Wed, 13 Oct 2021 at 15:21, Paolo De Stefani <[email-masked]> wrote:
> > transaction() blocks.

> I think the problem is only for people like me that come from psycopg2.

Pretty much everyone comes from psycopg2, so we should definitely
improve the docs :D

https://github.com/psycopg/psycopg/issues/110

-- Daniele

---

From: Paolo De Stefani <[email-masked]>
Date: Wed, 13 Oct 2021 20:40:07 +0200

> statement? I still think you'd be better off with an autocommit

In my small application i use only one connection (per client). I manage 
the customer orders of a food stand. The orders have header and lines. 
In that case i do a commit only after insert/update of the 2 table's 
records so i can not use an autocommit connection.

But what i wrote before and your suggestion are just fine for my needs, 
thanks

-- 
Paolo De Stefani

---

From: Daniel Fortunov <[email-masked]>
Date: Thu, 14 Oct 2021 00:28:11 +0100

> transaction() is used in autocommit: we might want to advise people to

Agree. The DBAPI choice to mandate that autocommit be off by default is a
strange and unfortunate choice that in my experience leads to a never
ending series of "surprises" such as this one.

My conclusion is that the only sane thing to do is:
1. Only ever create connections in autocommit mode.
2. Only ever use `with connection.transaction()` to control transactions.
3. Forget that `connection.commit()` and `connection.rollback()` exist, and
never use them.

I'm in favour of recommending this as strongly as reasonably possible in
the documentation.

It's a shame that 1 is not the default. So you still have to remember to
not forget to do this explicitly, every time you create a connection.

Dani

---

From: Reuben Rissler <[email-masked]>
Date: Wed, 13 Oct 2021 20:14:53 -0400

>
> Agree. The DBAPI choice to mandate that autocommit be off by default 
> is a strange and unfortunate choice that in my experience leads to a 
> never ending series of "surprises" such as this one.
>
> 3. Forget that `connection.commit()` and `connection.rollback()` 
> exist, and never use them.
>
> I'm in favour of recommending this as strongly as reasonably possible 
> in the documentation.
>
> It's a shame that 1 is not the default. So you still have to remember 
> to not forget to do this explicitly, every time you create a connection.
>
> Dani

I was going to disagree with this, as some things don't work in an ACID 
way in autocommit. Then I read point 2, now I'm going to try this and 
see how I like transactions vs commit/rollback.

Reuben Rissler

---

From: Karsten Hilbert <[email-masked]>
Date: Thu, 14 Oct 2021 12:05:03 +0200

>3. Forget that `connection.commit()` and `connection.rollback()` exist, and never use them.

What if you need to rollback a hitherto valid transaction ?

Transactions are there for a reason. It seems best to explicitely use them ?

Karsten

---

From: Daniel Fortunov <[email-masked]>
Date: Thu, 14 Oct 2021 12:08:11 +0100

> Transactions are there for a reason. It seems best to explicitely use them

I'm not sure I fully understand your question/statement but let me try to
respond.

We are still "using transactions", just with more precise, more explicit*,
and more flexible* semantics, represented by a context manager.

Rolling back a transaction is possible by raising a Rollback exception
within a block.

I hope this answers your question but if not please describe the scenario
you are thinking about.

Dani

(*More explicit because in the conventional autocommit=false approach, the
beginning of the transaction is an implicit side-effect of executing
another statement, which may or may not actually begin a transaction
depending on whether one is in progress. More flexible because you can have
nested transaction blocks which operate in an independent and composable
way.)

---

From: Karsten Hilbert <[email-masked]>
Date: Fri, 15 Oct 2021 00:16:25 +0200

> I hope this answers your question but if not please describe the scenario

Personally, I think the autocommit=False approach is somewhat
safer (more conservative) for the data:

	One *always* is inside a transaction, and the default
	behaviour is to rollback.

	Nothing is by accident automatically committed -- which can
	happen with autocommit=True.

I would certainly suggest that a context manager calls
.rollback() during teardown rather than .commit() -- the
context manager cannot know whether actions really are to
be committed, even if technically possible.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 14 Oct 2021 15:38:21 -0700

> 	happen with autocommit=True.

+1

> be committed, even if technically possible.

If I'm following that option exists:

https://www.psycopg.org/psycopg3/docs/api/connections.html#psycopg.Connection.transaction

force_rollback (bool) – Roll back the transaction at the end of the 
block even if there were no error (e.g. to try a no-op process).

-- 
Adrian Klaver
[email-masked]

---

From: Daniele Varrazzo <[email-masked]>
Date: Fri, 15 Oct 2021 12:56:25 +0200

> Pretty much everyone comes from psycopg2, so we should definitely

I have expanded the documentation about transaction behaviour.
Feedback is welcome.

https://www.psycopg.org/psycopg3/docs/basic/transactions.html

Paolo, Daniel: I am especially keen to have your feedback as one
newcomer and the other pretty much the authority in transaction
management :)

Cheers,

-- Daniele

---

From: Paolo De Stefani <[email-masked]>
Date: Fri, 15 Oct 2021 22:59:38 +0200

It seems much clearer to me now
This makes me think that maybe it's worth trying to use an autocommit 
connection...

-- 
Paolo De Stefani

---

From: Daniel Fortunov <[email-masked]>
Date: Fri, 15 Oct 2021 22:43:37 +0100

Looks good to me too.

I like the progression from basic use, to connection context, to
transaction context. Really nice logical progression into
progressive levels of granular control.

One thing I didn't understand was the wording of this suggestion:

>
>    -
>
>    use an autocommit connection: conn.autocommit = True, eventually as
>    connect() parameter too;
>
>
Is this meant to convey:

>
>    -
>
>    use an autocommit connection: Set conn.autocommit = True (or you can
>    provide this as a connect() parameter);
>
>
Or have I misunderstood?

Dani

---

From: Daniele Varrazzo <[email-masked]>
Date: Sat, 16 Oct 2021 11:59:33 +0200

On Fri, 15 Oct 2021 at 23:43, Daniel Fortunov
<[email-masked]> wrote:

>> use an autocommit connection: conn.autocommit = True, eventually as connect() parameter too;
>
>
> Is this meant to convey:
>>
>> use an autocommit connection: Set conn.autocommit = True (or you can provide this as a connect() parameter);

Yes, I will try to use some wording like that, thank you :)

-- Daniele

---

From: Karsten Hilbert <[email-masked]>
Date: Sun, 17 Oct 2021 00:29:45 +0200

Am Thu, Oct 14, 2021 at 03:38:21PM -0700 schrieb Adrian Klaver:

> force_rollback (bool) – Roll back the transaction at the end of the block even if there
> were no error (e.g. to try a no-op process).

Yes, but I would argue that force_rollback=True should be the
default.

Best,
Karsten

---

From: Karsten Hilbert <[email-masked]>
Date: Sun, 17 Oct 2021 00:48:40 +0200

> https://www.psycopg.org/psycopg3/docs/basic/transactions.html

I am sorry to say this but this concept:

	This way we don’t have to remember to call neither
	close() nor commit() and the database operation have
	actually a persistent effect.

seems fundamentally flawed to me in terms of databases.
Actually changing the content of a database should be a
conscious decision by program(mer) logic.

I agree

that writing to files seems similar, and is persistent by
default, but then files don't sport transaction semantics.

I also agree

that a transaction being started by the first SQL command can
be surprising to newcomers. It could be explained away by
stating "once you start doing something to the database you
*are* inside a transaction -- unless you have taken very
specific action not to be" very prominently in the docs.

However,

the fact that "plain" use of psycopg3 and context manager use
of psycopg3 yields opposite behaviour (default-rollback vs
default-commit) seems to violate the Principle Of Least
Astonishment ?

Best,
Karsten

---

From: Karsten Hilbert <[email-masked]>
Date: Sun, 17 Oct 2021 00:58:48 +0200

> conscious decision by program(mer) logic.

I could, perhaps, agree with the *transaction* context
manager being default-commit because:

	- after the context block the transaction has gone away

	- hence what happens inside either failed (auto-rollback)

	- or is to be committed

Not so for the *connection* context manager.

Best,
Karsten

---

From: Magnus Hagander <[email-masked]>
Date: Wed, 20 Oct 2021 22:07:13 +0200

> Not so for the *connection* context manager.

Just to chime in here as well, but this is pretty much what I was trying to
say in the thread on Twitter (before I realized I was on this list and
should've replied here). I have no problem with the transaction context
manager working like that, but for the "connectoin" one it's very strange
and it's dangerous in that it behaves differently when it's used as a
context manager and when it's not.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

---

From: Daniele Varrazzo <[email-masked]>
Date: Thu, 21 Oct 2021 11:52:40 +0100

> default-commit) seems to violate the Principle Of Least

Thank you for your feedback, Karsten (and Magnus and others), and I
apologise for the late response.

I understand your observation and I see the inconsistency with the
behaviour of a connection without context manager. However, by far, the
biggest astonishment using psycopg is the surprise of finding that the
operations executed didn't commit.

The behaviour of a dbapi connection, without context block, is to just
close the communication. The fact that this results in a rollback stems
only from the behaviour of the server, and it would be a strange design for
the connection block to issue an explicit rollback to emulate the same
behaviour on the client.

I think that using 'execute("INSERT....")' is already quite a conscious
decision of operating on the database. The use of transactions (implicitly,
explicitly) guarantees the atomicity of the operation performed, should the
program fail halfway through a sequence, and I think it is the most
important thing to respect, in the dbapi philosophy (I wasn't around when
it was designed, but I assume that the goal was to be atomic by default).
Committing the operations requested is by large the most common outcome a
programmer would want: asking that the program to 'conn.commit()'
explicitly seems an unrequested, kinda ritual, final blessing. Sending an
explicit ROLLBACK is an occurrence much more rare, and more conscious, than
just expecting that the commands already executed worked for real: people
wanting to do that I doubt they just rely on the side effect of 'close()'
or of the GC.

Also note that the block behaviour wrt transaction is the same as psycopg2;
the difference in Psycopg 3 is that the connection gets closed too at the
end of the block. Not doing that was the most surprising thing happening
and the cause of more than an argument, but nobody ever argued that they
didn't expect the transaction to be committed at the end of the block.

So yes, I acknowledge the inconsistency of the use without or with context,
but for me that's the difference between a mechanical, emerging, behaviour
(there is an implicit BEGIN, the connection is closed unclear, hence the
server discards the operations) and a conscious RAII decision (I create a
resource, I operate on the resource, the resource is clearly closed and
disposed of). Being a feature designed to be used to have a specific
effect, and wanting such effect to be as useful as possible, rolling back
on exit only to emulate the behaviour of a classic dbapi connection closed
in unclean state would have seemed the wrong design guideline.

Cheers

-- Daniele

---

From: Karsten Hilbert <[email-masked]>
Date: Thu, 21 Oct 2021 13:06:34 +0200

> The behaviour of a dbapi connection, without context block, is to just close the communication. The fact that this results in a rollback > stems only from the behaviour of the server

But that's the whole point?   A driver should _not_ (by default) alter the default
behaviour of the other end, IMO, without extremely good reason. There _is_ good
reason for the transaction context manager, but not for the connection context
manager or plain use.

> I think that using 'execute("INSERT....")' is already quite a conscious decision of operating on the database.

I agree. But the decision is not "this IS to be in the database" (or else no need for
transactions) but rather "this is to be in the database IF other, perhaps not even
database related, things suceed".

> asking that the program to 'conn.commit()' explicitly seems an unrequested, kinda ritual, final blessing.

Exactly.

> Sending an explicit ROLLBACK is an occurrence much more rare,

Exactly, and thusly easily forgotten, with possibly dire consequences
(in the case of default-commit, as opposed to default-rollback).

Karsten

---

From: Daniele Varrazzo <[email-masked]>
Date: Thu, 21 Oct 2021 13:00:10 +0100

> reason for the transaction context manager, but not for the connection context

The implicit BEGIN is also a surprising change from the "natural
course of events". Psycopg can still be used as a driver and not alter
the natural course of events. I have extended the documentation
(https://www.psycopg.org/psycopg3/docs/basic/usage.html#connection-context)
both highlighting the difference in behaviour you reported and
suggesting to not use 'with' in case more control is needed (using
psycopg more as a driver than as the end user).

> (in the case of default-commit, as opposed to default-rollback).

On this I disagree. People forget to do things when they are a
repetitive common occurrence, not when they are extraordinary.

I don't really see a disaster occurring there, especially because
porting code from psycopg2 results in pretty much the same operations.

-- Daniele

---

From: Magnus Hagander <[email-masked]>
Date: Thu, 21 Oct 2021 18:18:43 +0200

On Thu, Oct 21, 2021 at 2:00 PM Daniele Varrazzo <[email-masked]>
wrote:

> close the communication. The fact that this results in a rollback > stems
> only from the behaviour of the server
> >

Yes, the implicit BEGIN definitely is surprising -- but more easily
detected.

If the behaviour  when used as a context block isn't actually defined and
required by dbapi, I would suggest that the default of opening a new
connection as context manager *wouldn't* do the BEGIN -- it would just open
a connection in autocommit=on mode, mimicking the default on the server
side.

I would've personally liked that for non-context-manager one as well, but
AIUI that's actually required to behave the other way by dbapi.

But yes, the biggest issue I have with it is the part that's outlined in
the Warning section of the docs there -- using it in different ways
provides completely and fundamentally different behaviour in a way that can
cause bad data.

Things would be a lot more clear if the *connection* context didn't do
*anything* with transactions whatsoever, and the *transaction* one did all
of it. When it comes to transactions, explicit is always better than
implicit IMNSHO.

> > asking that the program to 'conn.commit()' explicitly seems an
> unrequested, kinda ritual, final blessing.
> >
> > Exactly.
> >

Another thing to consider is the failure scenarios.

If it implicitly rolls back at the end if you didn't do the right thing,
then you will notice immediately, because none of your changes get saved to
the database.

If it implicitly commits at the end it will *look* fine if you didn't do
the right thing, but more unpredictable things will happen if there's a
failure and you might not notice until much much later that you actually
had a transaction *at all*.

---

From: Christophe Pettus <[email-masked]>
Date: Thu, 21 Oct 2021 09:22:07 -0700

> On Oct 21, 2021, at 09:18, Magnus Hagander <[email-masked]> wrote:
> But yes, the biggest issue I have with it is the part that's outlined in the Warning section of the docs there -- using it in different ways provides completely and fundamentally different behaviour in a way that can cause bad data.
> 
> Things would be a lot more clear if the *connection* context didn't do *anything* with transactions whatsoever, and the *transaction* one did all of it. When it comes to transactions, explicit is always better than implicit IMNSHO.

+1.  I have to say that had I not read the warning, the behavior would have taken me by surprise, even with a lot of psycopg2 experience.