Thread: 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
On Wed, 13 Oct 2021 at 20:06, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote:
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.
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
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.
Reuben Rissler
>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
On Thu, 14 Oct 2021, 11:05 Karsten Hilbert, <Karsten.Hilbert@gmx.net> wrote:
>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 ?
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.)
> 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
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 asconnect()parameter too;
Is this meant to convey:
use an autocommit connection: Set
conn.autocommit = True(or you can provide this as aconnect()parameter);
Or have I misunderstood?
Dani
On Fri, 15 Oct 2021 at 21:59, Paolo De Stefani <paolo@paolodestefani.it> wrote:
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
On Sun, Oct 17, 2021 at 12:59 AM Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
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.
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.
On Sat, 16 Oct 2021, 23:48 Karsten Hilbert, <Karsten.Hilbert@gmx.net> wrote:
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 ?
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
> 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 Thu, Oct 21, 2021 at 2:00 PM Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote:
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 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.
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).
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.
>
> > 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.
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*.
> 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.