Обсуждение: psycopg3 transactions

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

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

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

-- 
Paolo De Stefani

On Wed, 13 Oct 2021 at 13:09, Paolo De Stefani <paolo@paolodestefani.it> wrote:>
> 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.

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.

> For example:
> ...
> >>> 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>

 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.

> IN PSQL:
>
> test=# select code, description from system.app_user;
>    code  | description
> --------+-------------
>   system | test
>   utente | ABCD
> (2 righe)

So far so good

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

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.

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

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

> IN PSQL:
> test=# select code, description from system.app_user;
>    code  | description
> --------+-------------
>   system | test
>   utente | ABCD
> (2 righe)

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


> So nothing changed for the psql connection even if a transaction was
> commited (i think...)
> if i close the connection
>
> IN PYTHON:
> >>> con.close()

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

> 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

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.

Your sequence of statements resulted in the execution of:

> >>> with con.cursor() as cur:
> ...     cur.execute('select code, description from system.app_user;')
BEGIN;
SELECT ...

> >>> with con.transaction():
> ...     with con.cursor() as cur:
> ...             cur.execute("update system.app_user set description =
> '1234' where code = 'utente';")

SAVEPOINT x
UPDATE ...
RELEASE SAVEPOINT x

> >>> with con.cursor() as cur:
> ...     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
have resulted in:

> >>> with con.cursor() as cur:
> ...     cur.execute('select code, description from system.app_user;')
SELECT ...

> >>> with con.transaction():
> ...     with con.cursor() as cur:
> ...             cur.execute("update system.app_user set description =
> '1234' where code = 'utente';")

BEGIN
UPDATE ...
COMMIT

> >>> with con.cursor() as cur:
> ...     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

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

Il 13/10/2021 13:50 Daniele Varrazzo ha scritto:
> 
> 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

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

On Wed, 13 Oct 2021 at 15:21, Paolo De Stefani <paolo@paolodestefani.it> wrote:

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

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

On Wed, 13 Oct 2021 at 15:21, Paolo De Stefani <paolo@paolodestefani.it> wrote:
>
> Il 13/10/2021 13:50 Daniele Varrazzo ha scritto:
> >
> > I think we can improve the documentation there by extending more about
> > the effects of the interaction between DBAPI transactions and the
> > 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

Il 13/10/2021 15:46 Daniele Varrazzo ha scritto:
> On Wed, 13 Oct 2021 at 15:21, Paolo De Stefani 
> <paolo@paolodestefani.it> wrote:
> 
>> 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...)
> 
> 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.

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

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

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.

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

What if you need to rollback a hitherto valid transaction ?

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

Karsten

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

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

On 10/14/21 15:16, Karsten Hilbert wrote:
>> 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.
> 
> 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.

+1

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

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

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 13 Oct 2021 at 19:28, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:

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

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

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

Il 15/10/2021 12:56 Daniele Varrazzo ha scritto:
> On Wed, 13 Oct 2021 at 19:28, Daniele Varrazzo
> <daniele.varrazzo@gmail.com> wrote:
> 
>> Pretty much everyone comes from psycopg2, so we should definitely
>> improve the docs :D
> 
> 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

-- 
Paolo De Stefani

On Fri, 15 Oct 2021 at 23:43, Daniel Fortunov
<postgresql@danielfortunov.com> wrote:

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

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


-- Daniele

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

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

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

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

> I have expanded the documentation about transaction behaviour.
> Feedback is welcome.
>
> 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
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Am Sun, Oct 17, 2021 at 12:48:40AM +0200 schrieb Karsten Hilbert:

> > 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 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
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

> The behaviour of a dbapi connection, without context block, is to just close the communication. The fact that this
resultsin 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

On Thu, 21 Oct 2021 at 12:06, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> > The behaviour of a dbapi connection, without context block, is to just close the communication. The fact that this
resultsin 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.

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


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

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

> On Oct 21, 2021, at 09:18, Magnus Hagander <magnus@hagander.net> wrote:
> But yes, the biggest issue I have with it is the part that's outlined in the Warning section of the docs there --
usingit 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
psycopg2experience.