Thread: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
I read this blog post
It starts with “Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL… once PostgreSQL 11 comes out”. It focuses on doing txn control from a stored proc.
In my initial test of the code that it presents, I got the runtime error “invalid transaction termination” from the first-encountered txn control statement (that happened to be commit). I presently realized that this was because I had \set AUTOCOMMIT OFF in my .psqlrc startup file. Once I changed this, the code worked as the blog described.
I’m hoping that someone from 2ndQuadrant can answer my questions about what my tests show. They use a trivial table created thus:
create table t(n integer);
My first test uses this:
create or replace procedure p1()
language plpgsql
as $$
begin
insert into t(n) values(17);
end;
$$;
language plpgsql
as $$
begin
insert into t(n) values(17);
end;
$$;
I test it first with AUTOCOMMIT OFF and then with in ON. The results are exactly as I’d expect.
When it’s ON, the effect of the insert shows up with a select immediately after the call finishes. Then rollback really does wipe out the effect of the insert, as is shown with a subsequent select.
And with AUTOCOMMIT ON, the effect of the insert again shows up with a select immediately after the call finishes. But now a subsequent rollback causes “WARNING: there is no transaction in progress”. The effect of the insert was already committed.
I could add a sleep after the insert and then watch from a second session. For now, I’m assuming that the effect of AUTOCOMMIT ON takes place when the call finishes and not immediately after the insert.
My second test uses this:
create or replace procedure p2()
language plpgsql
as $$
declare
levl_1 varchar(20);
levl_2 varchar(20);
begin
-- This "rollback" is critical.
-- Else "SET TRANSACTION ISOLATION LEVEL must be called before any query".
rollback;
set transaction isolation level repeatable read;
show transaction isolation level into levl_1;
insert into t(n) values(17);
rollback;
set transaction isolation level serializable;
show transaction isolation level into levl_2;
insert into t(n) values(42);
commit;
raise notice 'isolation level #1: %', levl_1;
raise notice 'isolation level #2: %', levl_2;
end;
$$;
language plpgsql
as $$
declare
levl_1 varchar(20);
levl_2 varchar(20);
begin
-- This "rollback" is critical.
-- Else "SET TRANSACTION ISOLATION LEVEL must be called before any query".
rollback;
set transaction isolation level repeatable read;
show transaction isolation level into levl_1;
insert into t(n) values(17);
rollback;
set transaction isolation level serializable;
show transaction isolation level into levl_2;
insert into t(n) values(42);
commit;
raise notice 'isolation level #1: %', levl_1;
raise notice 'isolation level #2: %', levl_2;
end;
$$;
(I got into this because I want to encapsulate all the logic that changes a table which has, in my functional spec, the table-level data rule: exactly one or two rows where column c1 has value ‘x’. The idea is to use the “serializable” isolation level and finish with a query that tests the rule. I’ll do this in an infinite loop with a sleep to that if a concurrent execution of the same proc pre-empts me and I get the “could not serialize…” error, I’ll hande the exception and go round the loop again, exiting only when I don’t get the exception.)
As mentioned above, I must call this with AUTOCOMMIT ON to avoid a runtime error. See my comment: the rollback as the proc’s very first executable statement is critical. The code runs without error and shows the result that I expect.
I’m attaching txn_control_in_plpgsql_proc.sql. It’s self-contained (can be run time and again with the same outcome) and implements what I just described. I’m also attaching txn_control_in_plpgsql_proc.txt. This is the stdout output, copied from the terminal window, produced when I invoke psql to start my .sql script from the command line.
B.t.w.. I have a real implementation of what I wanted to achieve and it seems to work perfectly. So I’ve achieved my goal. But I hate the fact that I got there by trial and error and cannot rehearse a mental model that informs me why what I wrote is the proper approach.
Please describe the rules for all this and reassure me that I can rely on the behavior that I implemented by starting with rollback in my proc.
.
Attachment
On 8/6/19 1:17 PM, Bryn Llewellyn wrote: > I read this blog post > > *PostgreSQL 11 – Server-side Procedures — Part 1 > <https://www.2ndquadrant.com/en/blog/postgresql-11-server-side-procedures-part-1/> and > Part 2 > <https://www.2ndquadrant.com/en/blog/postgresql-11-server-side-procedures-part-2/>* > > It starts with/ “Thanks to the work done by 2ndQuadrant contributors, we > now have the ability to write Stored Procedures in PostgreSQL… once > PostgreSQL 11 comes out”/. It focuses on doing txn control from a stored > proc. > > In my initial test of the code that it presents, I got the runtime error > “*invalid transaction termination*” from the first-encountered txn > control statement (that happened to be *commit*). I presently realized > that this was because I had *\set AUTOCOMMIT OFF*in my *.psqlrc*startup > file. Once I changed this, the code worked as the blog described. > > I’m hoping that someone from 2ndQuadrant can answer my questions about > what my tests show. They use a trivial table created thus: > > *create table t(n integer); > * > My first test uses this: > > *create or replace procedure p1() > language plpgsql > as $$ > begin > insert into t(n) values(17); > end; > $$; > * > I test it first with *AUTOCOMMIT OFF*and then with in *ON*. The results > are exactly as I’d expect. > > When it’s *ON*, the effect of the *insert*shows up with a > *select*immediately after the *call*finishes. Then *rollback*really does > wipe out the effect of the *insert*, as is shown with a subsequent *select*. > > And with *AUTOCOMMIT ON*, the effect of the *insert* again shows up with > a *select* immediately after the *call* finishes. But now a subsequent > *rollback*causes “*WARNING: there is no transaction in progress*”. > The effect of the *insert*was already committed. > > I could add a sleep after the *insert*and then watch from a second > session. For now, I’m assuming that the effect of *AUTOCOMMIT ON*takes > place when the *call*finishes and not immediately after the *insert*. > > My second test uses this: > > *create or replace procedure p2() > language plpgsql > as $$ > declare > levl_1 varchar(20); > levl_2 varchar(20); > begin > -- This "rollback" is critical. > -- Else "SET TRANSACTION ISOLATION LEVEL must be called before any > query". > rollback; > > set transaction isolation level repeatable read; > show transaction isolation level into levl_1; > insert into t(n) values(17); > rollback; > > set transaction isolation level serializable; > show transaction isolation level into levl_2; > insert into t(n) values(42); > commit; > > raise notice 'isolation level #1: %', levl_1; > raise notice 'isolation level #2: %', levl_2; > end; > $$; > * > (I got into this because I want to encapsulate all the logic that > changes a table which has, in my functional spec, the table-level data > rule: exactly one or two rows where column c1 has value ‘x’. The idea is > to use the “serializable” isolation level and finish with a query that > tests the rule. I’ll do this in an infinite loop with a sleep to that if > a concurrent execution of the same proc pre-empts me and I get > the “*could not serialize…*” error, I’ll hande the exception and go > round the loop again, exiting only when I don’t get the exception.) > > As mentioned above, I must call this with *AUTOCOMMIT ON*to avoid a > runtime error. See my comment: the *rollback*as the proc’s very first > executable statement is critical. The code runs without error and shows > the result that I expect. > > I’m attaching *txn_control_in_plpgsql_proc.sql*. It’s self-contained > (can be run time and again with the same outcome) and implements what I > just described. I’m also attaching *txn_control_in_plpgsql_proc.txt*. > This is the stdout output, copied from the terminal window, produced > when I invoke *psql*to start my *.sql*script from the command line. > > B.t.w.. I have a real implementation of what I wanted to achieve and it > seems to work perfectly. So I’ve achieved my goal. But I hate the fact > that I got there by trial and error and cannot rehearse a mental model > that informs me why what I wrote is the proper approach. > > *Please describe the rules for all this and reassure me that I can rely > on the behavior that I implemented by starting with **rollback**in my proc.* https://www.postgresql.org/docs/11/app-psql.html "AUTOCOMMIT When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM)." The way I understand it in your first case: \set AUTOCOMMIT off -- get clean start begin; delete from t; commit; call p1(); select n from t order by n; rollback; select n from t order by n; rollback; You have a implied BEGIN; before the begin; you wrap the delete in. Therefore you can do the rollback;. In the AUTOCOMMIT on there is only on transaction and it ends with the commit; after the delete. So when you attempt the rollback you get the error. REMEMBER in plpgsql Begin is not for transaction control: https://www.postgresql.org/docs/11/plpgsql-transactions.html Have not worked through the second case yet. > > > > > . > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks Adrian. My aim with p1() was to show that its behavior, in each AUTOCOMMIT mode, aligns with my intuition. I’ve noticed the system-generated “begin” that you mention when I add this to my “/usr/local/var/postgres/postgresql.conf”:
log_statement = 'all'
log_directory = 'log'
logging_collector = on
HERE, on “AUTOCOMMIT”.
“When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM).”
The way I understand it in your first case:
\set AUTOCOMMIT off
-- get clean start
begin;
delete from t;
commit;
call p1();
select n from t order by n;
rollback;
select n from t order by n;
rollback;
You have a implied BEGIN; before the begin; you wrap the delete in. Therefore you can do the rollback;. In the AUTOCOMMIT on there is only one transaction and it ends with the commit; after the delete. So when you attempt the rollback you get the error. REMEMBER in plpgsql Begin is not for transaction control, HERE (43.8. Transaction Management).
Have not worked through the second case yet.
--
Adrian Klaver
adrian.klaver@aklaver.com
log_directory = 'log'
logging_collector = on
and tail the most recent “/usr/local/var/postgres/log/postgresql-*.log” file. I assume that the “begin” is generated server-side—and not by the psql client-side program, other other such clients.
However, the intuition that informs my understanding of the behavior of p1() lets me down for p2(). My staring assumption was that if I want to do txn control in a plpgsql proc, then I must grant it that ability by stopping doing txn control at the outer level. But experiments—and what I’ve been told—tell me that I must do the opposite.
Here’s how I’ve tried to describe what I see for p2() with AUTOCOMMIT ON for myself:
1. my call p2() starts a txn.
2. However, during the execution of the proc, the usual autocommit behavior is programmatically turned off by explicit PostgreSQL code.
3. Other explicit PostgreSQL code makes “start transaction” inside a proc simply cause a runtime error under all circumstances. However, txns can be ended by “commit” or “rollback”. And new ones can be started—but only implicitly by executing a SQL statement that, as a top level SQL, would start a txn.
4. This is why “set transaction isolation level repeatable read” in my p2() is legal immediately after “rollback”—and produces the semantics I’d expect. At top level, and with autocommit turned on, it implicitly starts a txn—and you see the “begin” in the log file.
5. When the proc call ends, normal AUTOCOMMIT mode is turned on again, and a “commit” is issued automatically. This may, or may not, have something to do—as you can see by running p3() with AUTOCOMMIT ON.
create or replace procedure p3()
language plpgsql
as $$
begin
insert into t(n) values(17);
rollback;
insert into t(n) values(42);
end;
$$;
language plpgsql
as $$
begin
insert into t(n) values(17);
rollback;
insert into t(n) values(42);
end;
$$;
After calling it, you see just one row with the value 42—and it’s already durable.
This is why I want the folks who invented this behavior to describe the correct plplsql programmer’s mental model for me with the terminology that they designed.
adrian.klaver@aklaver.com wrote:
HERE, on “AUTOCOMMIT”.
“When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END. The autocommit-off mode works by issuing an implicit BEGIN for you, just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block (such as VACUUM).”
The way I understand it in your first case:
\set AUTOCOMMIT off
-- get clean start
begin;
delete from t;
commit;
call p1();
select n from t order by n;
rollback;
select n from t order by n;
rollback;
You have a implied BEGIN; before the begin; you wrap the delete in. Therefore you can do the rollback;. In the AUTOCOMMIT on there is only one transaction and it ends with the commit; after the delete. So when you attempt the rollback you get the error. REMEMBER in plpgsql Begin is not for transaction control, HERE (43.8. Transaction Management).
Have not worked through the second case yet.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn <bryn@yugabyte.com> wrote: > 1. my call p2() starts a txn. In my opinion this is the point: a procedure must be "owner" of the transaction to issue transaction control statements. You can watch different behaviors placing here and there txid_current() before and within p2 and re-run with autocommit off an on. On autocmmmit = on the call opens a transaction and the procedure is the only thing within the transaction, so it has full control. With autocommit off you gain an implicit begin and the procedure is possibly not the only thing you have within the transaction. You can test it with: testdb=# begin; BEGIN testdb=# call p2(); INFO: TXID 1300004994 ERROR: invalid transaction termination CONTEXT: PL/pgSQL function p2() line 9 at ROLLBACK testdb=# rollback; ROLLBACK Having said that, I believe there is no reason ever to begin a procedure with a rollback. Here's a small example that performs a few commits and rollback <https://github.com/fluca1978/PostgreSQL-11-Quick-Start-Guide/blob/master/Chapter04/Chapter04_Listing29.sql> Luca P.S: I don't believe that asking on a public mailing list for a company to answer is the right thing to do, you can always ask themselves on your own.
Hello, Bryn. At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn <bryn@yugabyte.com> wrote in <EE6D19C1-1CA6-424B-91AC-63A1A64A5921@yugabyte.com> > Here’s how I’ve tried to describe what I see for p2() with AUTOCOMMIT ON for myself: > > 1. my call p2() starts a txn. > > 2. However, during the execution of the proc, the usual autocommit behavior is programmatically turned off by explicitPostgreSQL code. > > 3. Other explicit PostgreSQL code makes “start transaction” inside a proc simply cause a runtime error under all circumstances.However, txns can be ended by “commit” or “rollback”. And new ones can be started—but only implicitly by executinga SQL statement that, as a top level SQL, would start a txn. In-procedure transaction control premises that no transaction is active before calling the procedure. https://www.postgresql.org/docs/11/sql-call.html > If CALL is executed in a transaction block, then the called > procedure cannot execute transaction control > statements. Transaction control statements are only allowed if > CALL is executed in its own transaction. With AUTOCOMMIT=off, implicit BEGIN is invoked just before CALLing p2() if no transaction is active. Thus p2() is always called having a transaction active, which inhibits in-procedure transaction control. I'm not sure why you want to turn AUTOCOMNIT off, but even with AUTOCOMMIT on, explict BEGIN prevents per-command COMMIT as you perhaps know. https://www.postgresql.org/docs/11/app-psql.html > When on (the default), each SQL command is automatically > committed upon successful completion. To postpone commit in > this mode, you must enter a BEGIN or START TRANSACTION SQL > command. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
On 8/7/19 12:15 AM, Luca Ferrari wrote: > On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn <bryn@yugabyte.com> wrote: >> 1. my call p2() starts a txn. > Luca > > P.S: > I don't believe that asking on a public mailing list for a company to > answer is the right thing to do, you can always ask themselves on your > own. Sort of moot as PROCEDURE is a Postgres feature not a company feature. -- Adrian Klaver adrian.klaver@aklaver.com
I have a version of the code that I attached with my first email in this thread where I’ve added “raise notice” invocations to show the txid as my p1() and p2() execute. Everything that I see is consistent with what I wrote separately in reply to adrian.klaver@aklaver.com:
<<
1. my call p2() starts a txn.
2. However, during the execution of the proc, the usual autocommit behavior is programmatically turned off by explicit PostgreSQL code.
3. Other explicit PostgreSQL code makes “start transaction” inside a proc simply cause a runtime error under all circumstances. However, txns can be ended by “commit” or “rollback”. And new ones can be started—but only implicitly by executing a SQL statement that, as a top level SQL, would start a txn.
4. This is why “set transaction isolation level repeatable read” in my p2() is legal immediately after “rollback”—and produces the semantics I’d expect. At top level, and with autocommit turned on, it implicitly starts a txn—and you see the “begin” in the log file.
5. When the proc call ends, normal AUTOCOMMIT mode is turned on again, and a “commit” is issued automatically. This may, or may not, have something to do—as you can see by running p3() with AUTOCOMMIT ON.
2. However, during the execution of the proc, the usual autocommit behavior is programmatically turned off by explicit PostgreSQL code.
3. Other explicit PostgreSQL code makes “start transaction” inside a proc simply cause a runtime error under all circumstances. However, txns can be ended by “commit” or “rollback”. And new ones can be started—but only implicitly by executing a SQL statement that, as a top level SQL, would start a txn.
4. This is why “set transaction isolation level repeatable read” in my p2() is legal immediately after “rollback”—and produces the semantics I’d expect. At top level, and with autocommit turned on, it implicitly starts a txn—and you see the “begin” in the log file.
5. When the proc call ends, normal AUTOCOMMIT mode is turned on again, and a “commit” is issued automatically. This may, or may not, have something to do—as you can see by running p3() with AUTOCOMMIT ON.
>>
I’m interested only in the case that my proc does txn control—i.e. my p2()—and so I’m not interested in how my p1() behaves.
About your “I believe there is no reason ever to begin a procedure with a rollback”, I already explained why I did that. My higher goal is to take advantage of the “serializable” isolation level to safely enforce a multi-row data rule. And I want my pspgsql proc—following the time-honored philosophy for stored procs—to own the complete implementation. I discovered that doing “rollback” as the first executable statement in my proc allowed me to do “set transaction isolation level serializable”. And I’ve found no other way to do this. As I mentioned, the “real” version of my proc, written this way does pass my functionality tests.
B.t.w., I noticed that “set transaction isolation level serializable” must be the very first statement after “rollback” (or “commit”). Even an invocation of txid_current() after the rollback and before the ““set transaction” causes this runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before any query”.
About your PS, I’m new to this list—so forgive me if I didn’t follow proper etiquette. But as adrian.klaver@aklaver.com pointed out, the functionality under discussion here is part of the core PostgreSQL implementation.
On 07-Aug-2019, at 00:15, Luca Ferrari <fluca1978@gmail.com> wrote:
On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn <bryn@yugabyte.com> wrote:
In my opinion this is the point: a procedure must be "owner" of the
transaction to issue transaction control statements. You can watch
different behaviors placing here and there txid_current() before and
within p2 and re-run with autocommit off an on.
On autocmmmit = on the call opens a transaction and the procedure is
the only thing within the transaction, so it has full control. With
autocommit off you gain an implicit begin and the procedure is
possibly not the only thing you have within the transaction.
You can test it with:
testdb=# begin;
BEGIN
testdb=# call p2();
INFO: TXID 1300004994
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function p2() line 9 at ROLLBACK
testdb=# rollback;
ROLLBACK
Having said that, I believe there is no reason ever to begin a
procedure with a rollback.
Here's a small example that performs a few commits and rollback
<https://github.com/fluca1978/PostgreSQL-11-Quick-Start-Guide/blob/master/Chapter04/Chapter04_Listing29.sql>
Luca
P.S:
I don't believe that asking on a public mailing list for a company to
answer is the right thing to do, you can always ask themselves on your
own.
1. my call p2() starts a txn.
In my opinion this is the point: a procedure must be "owner" of the
transaction to issue transaction control statements. You can watch
different behaviors placing here and there txid_current() before and
within p2 and re-run with autocommit off an on.
On autocmmmit = on the call opens a transaction and the procedure is
the only thing within the transaction, so it has full control. With
autocommit off you gain an implicit begin and the procedure is
possibly not the only thing you have within the transaction.
You can test it with:
testdb=# begin;
BEGIN
testdb=# call p2();
INFO: TXID 1300004994
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function p2() line 9 at ROLLBACK
testdb=# rollback;
ROLLBACK
Having said that, I believe there is no reason ever to begin a
procedure with a rollback.
Here's a small example that performs a few commits and rollback
<https://github.com/fluca1978/PostgreSQL-11-Quick-Start-Guide/blob/master/Chapter04/Chapter04_Listing29.sql>
Luca
P.S:
I don't believe that asking on a public mailing list for a company to
answer is the right thing to do, you can always ask themselves on your
own.
Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this rule is “an implementation restriction, for the most part.” See HERE.
About your “In-procedure transaction control premises that no transaction is active before calling the procedure”… yes. Nevertheless, as the code that Umair Sahid showed us in the blog post that I referenced in my email that started this thread, you can indeed start end end transactions from an executing proc (as long as the session’s AUTOCOMMIT mode s ON).
So, logic tells me that once a txn is ended by issuing “commit” or “rollback”, you should be allowed to start the next one explicitly with “start transaction”. However, as mentioned, this causes a runtime error. I’ve decided simply not to care because I’ve discovered how to write my proc so that it passes the functionality tests that it ought to. I have to rely on the fact the the statements I’m interested in doing (including setting the isolation level) all implicitly start a txn and so “start transaction” isn’t needed!
Thanks to all who responded. The synthesis of what you all wrote helped me enormously. Case closed.
On 07-Aug-2019, at 00:26, Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
Hello, Bryn.
At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn <bryn@yugabyte.com> wrote in <EE6D19C1-1CA6-424B-91AC-63A1A64A5921@yugabyte.com>
In-procedure transaction control premises that no transaction is
active before calling the procedure.
https://www.postgresql.org/docs/11/sql-call.html
With AUTOCOMMIT=off, implicit BEGIN is invoked just before
CALLing p2() if no transaction is active. Thus p2() is always
called having a transaction active, which inhibits in-procedure
transaction control.
I'm not sure why you want to turn AUTOCOMNIT off, but even with
AUTOCOMMIT on, explict BEGIN prevents per-command COMMIT as you
perhaps know.
https://www.postgresql.org/docs/11/app-psql.html
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn <bryn@yugabyte.com> wrote in <EE6D19C1-1CA6-424B-91AC-63A1A64A5921@yugabyte.com>
Here’s how I’ve tried to describe what I see for p2() with AUTOCOMMIT ON for myself:
1. my call p2() starts a txn.
2. However, during the execution of the proc, the usual autocommit behavior is programmatically turned off by explicit PostgreSQL code.
3. Other explicit PostgreSQL code makes “start transaction” inside a proc simply cause a runtime error under all circumstances. However, txns can be ended by “commit” or “rollback”. And new ones can be started—but only implicitly by executing a SQL statement that, as a top level SQL, would start a txn.
In-procedure transaction control premises that no transaction is
active before calling the procedure.
https://www.postgresql.org/docs/11/sql-call.html
If CALL is executed in a transaction block, then the called
procedure cannot execute transaction control
statements. Transaction control statements are only allowed if
CALL is executed in its own transaction.
With AUTOCOMMIT=off, implicit BEGIN is invoked just before
CALLing p2() if no transaction is active. Thus p2() is always
called having a transaction active, which inhibits in-procedure
transaction control.
I'm not sure why you want to turn AUTOCOMNIT off, but even with
AUTOCOMMIT on, explict BEGIN prevents per-command COMMIT as you
perhaps know.
https://www.postgresql.org/docs/11/app-psql.html
When on (the default), each SQL command is automatically
committed upon successful completion. To postpone commit in
this mode, you must enter a BEGIN or START TRANSACTION SQL
command.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On 8/7/19 11:46 AM, Bryn Llewellyn wrote: > Thanks for your response, Kyotaro. I’m happy, now, to accept the rule > that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT > is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter > Eisentraut, that this rule is “an implementation restriction, for the > most part.” See HERE > <https://twitter.com/petereisentraut/status/1158802910865756160>. > > About your “In-procedure transaction control premises that no > transaction is active before calling the procedure”… yes. Nevertheless, > as the code that Umair Sahid showed us in the blog post that I > referenced in my email that started this thread, you can indeed start > end end transactions from an executing proc (as long as the session’s > AUTOCOMMIT mode s ON). > The key is that the AUTOCOMMIT status is just a specific case of the general rule. The general rule being that a PROCEDURE cannot do transaction ending commands when it it called within an outer transaction. You can run into the same issue in other situations e.g. ORM's that start a transaction behind the scenes. In other words this is not psql specific. As long as you understand the general rule then things become clearer. -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn@yugabyte.com> wrote: > About your “I believe there is no reason ever to begin a procedure with a rollback”, I already explained why I did that.My higher goal is to take advantage of the “serializable” isolation level to safely enforce a multi-row data rule. AndI want my pspgsql proc—following the time-honored philosophy for stored procs—to own the complete implementation. I discoveredthat doing “rollback” as the first executable statement in my proc allowed me to do “set transaction isolationlevel serializable”. And I’ve found no other way to do this. As I mentioned, the “real” version of my proc, writtenthis way does pass my functionality tests. I'm sorry, I still don't get the point in issuing a rollback as first instruction because it restricts, at least in my opinion, the use case of your procedure, that in turns restrict the mean of a procedure (reusability). However, since you are dwealing with it, I'm fine. > > B.t.w., I noticed that “set transaction isolation level serializable” must be the very first statement after “rollback”(or “commit”). Even an invocation of txid_current() after the rollback and before the ““set transaction” causesthis runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before any query”. > Well, SET TRANSACTION ISOLATION must be the very first instruction of every transaction, not only within the case you describe. > About your PS, I’m new to this list—so forgive me if I didn’t follow proper etiquette. But as adrian.klaver@aklaver.compointed out, the functionality under discussion here is part of the core PostgreSQL implementation. Sorry, but in your original post you placed the sentence: "I’m hoping that someone from 2ndQuadrant can answer my questions", that's why I pointed out thrat, as people at 2ndquadrant have already told you, this has nothing to do with 2ndquadrant specifically. And that's why I replied that "hoping" for an answer is not as good as asking directly to them. And please stop quote posting and jumping to different part of the message, because it makes reading it very hard. Luca
Yes, I do believe that I understand this. But there’s no getting away from the fact that the AUTOCOMMIT mode, and what this implies, is a server-side phenomenon—at least as several PostgreSQL experts have assured me. For example, when you use client-side Python with the psycopg2 driver, then once you’ve done “my_session = psycopg2.connect(connect_str)”, you can then do “my_session.set_session(autocommit=False)”. And then everything we’ve been saying in the psql context now applies in that context—yes?
B.t.w., I’m guessing that the “begin” SQL command that you see in the log that I mentioned is actually issued by (some) clients—at least psql and Python-on-psycopg2—as an explicit call from the client. In other words, it isn’t the server that generates this. Does anyone know for sure how this works?
On 07-Aug-2019, at 11:56, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 8/7/19 11:46 AM, Bryn Llewellyn wrote:
The key is that the AUTOCOMMIT status is just a specific case of the general rule. The general rule being that a PROCEDURE cannot do transaction ending commands when it it called within an outer transaction. You can run into the same issue in other situations e.g. ORM's that start a transaction behind the scenes. In other words this is not psql specific. As long as you understand the general rule then things become clearer.
--
Adrian Klaver
adrian.klaver@aklaver.com
Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this rule is “an implementation restriction, for the most part.” See HERE <https://twitter.com/petereisentraut/status/1158802910865756160>.
About your “In-procedure transaction control premises that no transaction is active before calling the procedure”… yes. Nevertheless, as the code that Umair Sahid showed us in the blog post that I referenced in my email that started this thread, you can indeed start end end transactions from an executing proc (as long as the session’s AUTOCOMMIT mode s ON).
The key is that the AUTOCOMMIT status is just a specific case of the general rule. The general rule being that a PROCEDURE cannot do transaction ending commands when it it called within an outer transaction. You can run into the same issue in other situations e.g. ORM's that start a transaction behind the scenes. In other words this is not psql specific. As long as you understand the general rule then things become clearer.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 8/7/19 12:54 PM, Bryn Llewellyn wrote: > Yes, I do believe that I understand this. But there’s no getting away > from the fact that the AUTOCOMMIT mode, and what this implies, is a > server-side phenomenon—at least as several PostgreSQL experts have > assured me. For example, when you use client-side Python with > the psycopg2 driver, then once you’ve done “my_session = > psycopg2.connect(connect_str)”, you can then > do “my_session.set_session(autocommit=False)”. And then everything we’ve > been saying in the psql context now applies in that context—yes? The server responds to instructions from the client. General rule: https://www.postgresql.org/docs/11/sql-call.html "If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction. " > > B.t.w., I’m guessing that the “begin” SQL command that you see in the > log that I mentioned is actually issued by (some) clients—at least psql > and Python-on-psycopg2—as an explicit call from the client. In other > words, it isn’t the server that generates this. Does anyone know for > sure how this works? psql: https://doxygen.postgresql.org/bin_2psql_2common_8c.html Starting ~ line 1355 if (transaction_status == PQTRANS_IDLE && !pset.autocommit && !command_no_begin(query)) { results = PQexec(pset.db, "BEGIN"); if (PQresultStatus(results) != PGRES_COMMAND_OK) ... psycopg2: https://github.com/psycopg/psycopg2/blob/master/psycopg/connection_int.c ~line 1294 > > On 07-Aug-2019, at 11:56, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 8/7/19 11:46 AM, Bryn Llewellyn wrote: >> Thanks for your response, Kyotaro. I’m happy, now, to accept the rule >> that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT >> is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter >> Eisentraut, that this rule is “an implementation restriction, for the >> most part.” See HERE >> <https://twitter.com/petereisentraut/status/1158802910865756160>. >> About your “In-procedure transaction control premises that no >> transaction is active before calling the procedure”… yes. >> Nevertheless, as the code that Umair Sahid showed us in the blog post >> that I referenced in my email that started this thread, you can indeed >> start end end transactions from an executing proc (as long as the >> session’s AUTOCOMMIT mode s ON). > > The key is that the AUTOCOMMIT status is just a specific case of the > general rule. The general rule being that a PROCEDURE cannot do > transaction ending commands when it it called within an outer > transaction. You can run into the same issue in other situations e.g. > ORM's that start a transaction behind the scenes. In other words this is > not psql specific. As long as you understand the general rule then > things become clearer. > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
From
"David G. Johnston"
Date:
On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari <fluca1978@gmail.com> wrote:
On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> B.t.w., I noticed that “set transaction isolation level serializable” must be the very first statement after “rollback” (or “commit”). Even an invocation of txid_current() after the rollback and before the ““set transaction” causes this runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before any query”.
>
Well, SET TRANSACTION ISOLATION must be the very first instruction of
every transaction, not only within the case you describe.
IMHO, The documentation, probably in chapter 13, could use some exposition on this topic.
What is being described here is basically:
[implicit] BEGIN (using default transaction options)
CALL
>>>>BEGIN (with inherited default transactions options)
>>>>ROLLBACK (to get rid of the nested transaction setup by the call with the inherited default options)
>>>>START WITH OPTIONS
>>>>COMMIT;
<<<<CALL END
[implicit] COMMIT
As far as psql is concerned there is only one statement and once its executed psql issues the implicit commit to match the implicit begin is sent previously.
It should be better documented which combinations of outer and inner transaction commands are considered valid and which are not. WIth examples. The current scattering of words leaves the user to perform trial-and-error, just as the OP has, to determine what is allowed.
The nested transaction seems like it has to be correct since otherwise the rollback as a first statement would attempt to rollback the transaction the call itself is executing within...
Note I only have access to v10 at the moment so I haven't tried my own experiments. To my main point I shouldn't have to - the expected behavior should be something I could directly interpret from the documentation and in my admitted brief attempt I could not do so.
David J.
All stored procedures run in their own transaction. My guess is that when you turn off autocommit, you are not committing something after your transaction ends. Also, I have never seen a rollback at the start of a proc. A pure hypothetical is that it is doing nothing or definitely not what you think it is.
BTW, this is why you cant close a transaction within a stored procedure. It doesnt make sense.
Thanks,
~Ben
~Ben
On Wed, Aug 7, 2019, 4:41 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari <fluca1978@gmail.com> wrote:On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> B.t.w., I noticed that “set transaction isolation level serializable” must be the very first statement after “rollback” (or “commit”). Even an invocation of txid_current() after the rollback and before the ““set transaction” causes this runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before any query”.
>
Well, SET TRANSACTION ISOLATION must be the very first instruction of
every transaction, not only within the case you describe.IMHO, The documentation, probably in chapter 13, could use some exposition on this topic.What is being described here is basically:[implicit] BEGIN (using default transaction options)CALL>>>>BEGIN (with inherited default transactions options)>>>>ROLLBACK (to get rid of the nested transaction setup by the call with the inherited default options)>>>>START WITH OPTIONS>>>>COMMIT;<<<<CALL END[implicit] COMMITAs far as psql is concerned there is only one statement and once its executed psql issues the implicit commit to match the implicit begin is sent previously.It should be better documented which combinations of outer and inner transaction commands are considered valid and which are not. WIth examples. The current scattering of words leaves the user to perform trial-and-error, just as the OP has, to determine what is allowed.The nested transaction seems like it has to be correct since otherwise the rollback as a first statement would attempt to rollback the transaction the call itself is executing within...Note I only have access to v10 at the moment so I haven't tried my own experiments. To my main point I shouldn't have to - the expected behavior should be something I could directly interpret from the documentation and in my admitted brief attempt I could not do so.David J.
Thanks, Adrian. On 07-Aug-2019, at 13:19, Adrian Klaver <adrian.klaver@aklaver.com> wrote: On 8/7/19 12:54 PM, Bryn Llewellyn wrote: > Yes, I do believe that I understand this. But there’s no getting away from the fact that the AUTOCOMMIT mode, and whatthis implies, is a server-side phenomenon—at least as several PostgreSQL experts have assured me. For example, when youuse client-side Python with the psycopg2 driver, then once you’ve done “my_session = psycopg2.connect(connect_str)”, youcan then do “my_session.set_session(autocommit=False)”. And then everything we’ve been saying in the psql context nowapplies in that context—yes? The server responds to instructions from the client. General rule: https://www.postgresql.org/docs/11/sql-call.html "If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transactioncontrol statements are only allowed if CALL is executed in its own transaction. " > B.t.w., I’m guessing that the “begin” SQL command that you see in the log that I mentioned is actually issued by (some)clients—at least psql and Python-on-psycopg2—as an explicit call from the client. In other words, it isn’t the serverthat generates this. Does anyone know for sure how this works? psql: https://doxygen.postgresql.org/bin_2psql_2common_8c.html Starting ~ line 1355 if (transaction_status == PQTRANS_IDLE && !pset.autocommit && !command_no_begin(query)) { results = PQexec(pset.db, "BEGIN"); if (PQresultStatus(results) != PGRES_COMMAND_OK) ... psycopg2: https://github.com/psycopg/psycopg2/blob/master/psycopg/connection_int.c ~line 1294 > On 07-Aug-2019, at 11:56, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > On 8/7/19 11:46 AM, Bryn Llewellyn wrote: >> Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that “call proc_that_does_txn_control()” is legalonly when AUTOCOMMIT is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this rule is “animplementation restriction, for the most part.” See HERE <https://twitter.com/petereisentraut/status/1158802910865756160>. >> About your “In-procedure transaction control premises that no transaction is active before calling the procedure”… yes.Nevertheless, as the code that Umair Sahid showed us in the blog post that I referenced in my email that started thisthread, you can indeed start end end transactions from an executing proc (as long as the session’s AUTOCOMMIT mode sON). > The key is that the AUTOCOMMIT status is just a specific case of the general rule. The general rule being that a PROCEDUREcannot do transaction ending commands when it it called within an outer transaction. You can run into the same issuein other situations e.g. ORM's that start a transaction behind the scenes. In other words this is not psql specific. As long as you understand the general rule then things become clearer. > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> -- Adrian Klaver adrian.klaver@aklaver.com
Thanks David. I’m relieved to hear that I’m not the only one who finds it hard to predict the behavior of some things in PostgreSQL just from reading the docs. Hypothesising and experimenting seem to be needed for the trickier cases.
You use the phrase "nested transaction”. This Google search gets no hits:
"nested transaction" site:www.postgresql.org/docs/11/
And as I’ve come to understand the phrase in the wider world, "nested transaction” is synonymous with “autonomous transaction”. PostgreSQL 11.2 doesn’t support these. But I’ve heard that some future release might. So, as I see it, a single session can do only one transaction after another with no overlapping. My experiments using txid_current()—mentioned elsewhere in the offshoots from my original post—are consistent with this hypothesis.
On 07-Aug-2019, at 13:40, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari <fluca1978@gmail.com> wrote:
On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
> B.t.w., I noticed that “set transaction isolation level serializable” must be the very first statement after “rollback” (or “commit”). Even an invocation of txid_current() after the rollback and before the ““set transaction” causes this runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before any query”.
>
Well, SET TRANSACTION ISOLATION must be the very first instruction of
every transaction, not only within the case you describe.
IMHO, The documentation, probably in chapter 13, could use some exposition on this topic.
What is being described here is basically:
[implicit] BEGIN (using default transaction options)
CALL
>>>>BEGIN (with inherited default transactions options)
>>>>ROLLBACK (to get rid of the nested transaction setup by the call with the inherited default options)
>>>>START WITH OPTIONS
>>>>COMMIT;
<<<<CALL END
[implicit] COMMIT
As far as psql is concerned there is only one statement and once its executed psql issues the implicit commit to match the implicit begin is sent previously.
It should be better documented which combinations of outer and inner transaction commands are considered valid and which are not. WIth examples. The current scattering of words leaves the user to perform trial-and-error, just as the OP has, to determine what is allowed.
The nested transaction seems like it has to be correct since otherwise the rollback as a first statement would attempt to rollback the transaction the call itself is executing within...
Note I only have access to v10 at the moment so I haven't tried my own experiments. To my main point I shouldn't have to - the expected behavior should be something I could directly interpret from the documentation and in my admitted brief attempt I could not do so.
David J.
On Wed, Aug 7, 2019 at 11:36 PM Benedict Holland <benedict.m.holland@gmail.com> wrote: > > Also, I have never seen a rollback at the start of a proc. A pure hypothetical is that it is doing nothing or definitelynot what you think it is. That's my point, thanks. Luca
Bryn Llewellyn wrote: > B.t.w., I’m guessing that the “begin” SQL command that you see in the log > that I mentioned is actually issued by (some) clients—at least psql and > Python-on-psycopg2—as an explicit call from the client. In other words, it > isn’t the server that generates this. Does anyone know for sure how this > works? Well, that's the point of Autocommit, and moreover it does nothing else. Maybe you're still confused about this. * Autocommit off = the client automatically adds a "BEGIN" when it thinks a transaction must be started on behalf of the user. * Autocommit on = the client does nothing. The fact that "off" implies doing something and "on" implies not interfering is counterintuitive, but that's how it is. Autocommit is for compatibility with other databases. If it was only for Postgres, I guesss either it wouldn't exist in the first place or it should be called "AutoStartTransactionBlock" or something like that, because that's just what it really does. Anyway, the server only know whether a BEGIN has been issued. It never knows or cares whether it has been added implicitly or explicitly, which is why it can be quite confusing to reason about server-side differences in terms of Autocommit, as you do in some of your previous messages. It should be stressed that Autocommit is not a parameter of the session between Postgres and the SQL client, but rather it's a parameter of the session between the user and their SQL client. So when you're hypothesizing that a plpgsql block in a procedure would look at this parameter or change it temporarily (your points #2 and #5 in your analysis of p2's execution), you should see that it's impossible, because on the server-side, this parameter just does not exist. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
Please try the following.
Stop PostgreSQL with this:
pg_ctl -D /usr/local/var/postgres stop
Edit the file "/usr/local/var/postgres/postgresql.conf” and add these lines at the end:
log_statement = 'all'
log_directory = 'log'
logging_collector = on
log_directory = 'log'
logging_collector = on
Then start PostgreSQL with this:
pg_ctl -D /usr/local/var/postgres start
Then, in one terminal window, find the latest log file with this:
ls -l /usr/local/var/postgres/log/postgresql-*.log
Then, in a second terminal window, start psql and do these tests. By all means, add your own.
-- Test 1.
\set AUTOCOMMIT ON
insert into t(n) values(42);
insert into t(n) values(42);
I see _only_ the “insert” in the log—in other words, no “commit”. (And no “BEGIN” for that matter.) If it were the case the psql sends the commit after every statement when AUTOCOMMIT is ON, then I’d see this in the log. Yet the effect of the “insert” has indeed been committed. You can see this by issuing “rollback” (you get the "there is no transaction in progress” warning). And a subsequent “select” confirms that the “insert” was committed.
My interpretation is that AUTOCOMMIT is a server-side phenomenon (but, as we shall see, the client does know that this mode has been set).
-- Test 2.
\set AUTOCOMMIT OFF
insert into t(n) values(42);
insert into t(n) values(42);
rollback;
I see this in the log:
…LOG: statement: BEGIN
…LOG: statement: insert into t(n) values(42);
…LOG: statement: rollback;
…LOG: statement: insert into t(n) values(42);
…LOG: statement: rollback;
(I also see this when I do the test using Python on psycopg2 after doing “my_session.set_session(autocommit=False)”. The “rollback” doesn’t draw a warning. And, indeed, the effect of my “insert” has been undone, as has been shown by a subsequent “select”.)
-- Test 3.
\set AUTOCOMMIT OFF
start transaction;
\set AUTOCOMMIT OFF
start transaction;
insert into t(n) values(42);
rollback;
I see this in the log:
rollback;
I see this in the log:
…LOG: statement: start transaction;
…LOG: statement: insert into t(n) values(42);
…LOG: statement: rollback;
…LOG: statement: insert into t(n) values(42);
…LOG: statement: rollback;
Same outcome as with Test 2. Test 2 and Test 3 are consistent with the hypothesis that it’s the client that issues the “BEGIN” before your first SQL command after setting AUTOCOMMIT to OFF. But it doesn’t _prove_ the hypothesis. However, experts on this list have said that this is how it works. And this is consistent with the fact that, when AUTOCOMMIT is ON, I don’t see this in the log:
BEGIN;
insert into t(n) values(42);
COMMIT;
COMMIT;
If AUTOCOMMIT were entirely a client-side phenomenon, then you _would_ have to see this to get the defined semantics.
In summary, my hypothesis is that AUTOCOMMIT is a server side phenomenon. But, because to set it with a call from the client, the client does know what mode you’re in and adds its own BEGIN before the first SQL command that follows setting AUTOCOMMIT to ON.
Test 4 is more elaborate. Please run the attached setup_for_test_4.sql, and then do this by hand. But do read the definition of "p2()" carefully first. Then do this:
-- Test 4.
\set AUTOCOMMIT ON
\set AUTOCOMMIT ON
do $$ begin raise notice 'txid at top level before "p2()" ..... %', txid_current(); end $$;
call p2();
do $$ begin raise notice 'txid at top level after "p1()" ...... %', txid_current(); end $$;
select n from t order by n;
call p2();
do $$ begin raise notice 'txid at top level after "p1()" ...... %', txid_current(); end $$;
select n from t order by n;
This is the output:
NOTICE: txid at top level before "p2()" ..... 9478
NOTICE: txid in "p2()" at start ............. 9479
NOTICE: txid in "p2()" after "set txn"....... 9480, level = repeatable read
NOTICE: txid in "p2()" after "set txn"....... 9481, level = serializable
NOTICE: txid at top level after "p1()" ...... 9482
n
----
42
NOTICE: txid in "p2()" at start ............. 9479
NOTICE: txid in "p2()" after "set txn"....... 9480, level = repeatable read
NOTICE: txid in "p2()" after "set txn"....... 9481, level = serializable
NOTICE: txid at top level after "p1()" ...... 9482
n
----
42
And this is what the log shows:
…LOG: statement: do $$ begin raise notice 'txid at top level before "p2()" ..... %', txid_current(); end $$;
…LOG: statement: call p2();
…LOG: statement: do $$ begin raise notice 'txid at top level after "p1()" ...... %', txid_current(); end $$;
…LOG: statement: select n from t order by n;
…LOG: statement: call p2();
…LOG: statement: do $$ begin raise notice 'txid at top level after "p1()" ...... %', txid_current(); end $$;
…LOG: statement: select n from t order by n;
Notice that there’s no visible “BEGIN” between my first “do" block and the “call”. And (again) no visible “COMMIT” after the “call" finishes. But the txid values show that “call” did indeed start a new txt, the “rollback” in the proc ended it so that "set transaction isolation level” in the proc can (implicitly) start a new txn. And so on. All this is consistent with the hypothesis that there is no such thing as a “nested transaction”. Rather, there’s just a series of non-overlapping ordinary txns. The BLOG POST that I quoted with my first post on this topic explains that this is intended.
This is consistent with the hypothesis that AUTOCOMMIT is indeed a server-side phenomenon and that, during the execution of a procedure, its usual effect is suspended—and is resumed when the procedure execution ends.
I’ve said several times that my goal is to set the isolation level from inside a proc so that the proc can own the entire logic for concurrent txns that might violate a multi-row data-rule.
And I’ve said several times that I discovered that starting my proc with “rollback” allows this—and that I've
found no other way to meet my goal. I don’t understand, therefore, why some people (but not you, Daniel!) who’ve responded to my questions say that starting my proc with “rollback” is pointless.
I tried, earlier, to say “case closed”. I’ll say it again now.
On 08-Aug-2019, at 06:53, Daniel Verite <daniel@manitou-mail.org> wrote:
Bryn Llewellyn wrote:
Well, that's the point of Autocommit, and moreover it does nothing
else. Maybe you're still confused about this.
* Autocommit off = the client automatically adds a "BEGIN" when
it thinks a transaction must be started on behalf of the user.
* Autocommit on = the client does nothing.
The fact that "off" implies doing something and "on" implies not
interfering is counterintuitive, but that's how it is. Autocommit is
for compatibility with other databases. If it was only for Postgres, I
guesss either it wouldn't exist in the first place or it should be
called "AutoStartTransactionBlock" or something like that, because
that's just what it really does.
Anyway, the server only know whether a BEGIN has been issued.
It never knows or cares whether it has been added implicitly or explicitly,
which is why it can be quite confusing to reason about server-side
differences in terms of Autocommit, as you do in some of your previous
messages.
It should be stressed that Autocommit is not a parameter of the
session between Postgres and the SQL client, but rather it's a
parameter of the session between the user and their SQL client.
So when you're hypothesizing that a plpgsql block in a procedure
would look at this parameter or change it temporarily (your
points #2 and #5 in your analysis of p2's execution), you should
see that it's impossible, because on the server-side, this parameter
just does not exist.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
B.t.w., I’m guessing that the “begin” SQL command that you see in the log
that I mentioned is actually issued by (some) clients—at least psql and
Python-on-psycopg2—as an explicit call from the client. In other words, it
isn’t the server that generates this. Does anyone know for sure how this
works?
Well, that's the point of Autocommit, and moreover it does nothing
else. Maybe you're still confused about this.
* Autocommit off = the client automatically adds a "BEGIN" when
it thinks a transaction must be started on behalf of the user.
* Autocommit on = the client does nothing.
The fact that "off" implies doing something and "on" implies not
interfering is counterintuitive, but that's how it is. Autocommit is
for compatibility with other databases. If it was only for Postgres, I
guesss either it wouldn't exist in the first place or it should be
called "AutoStartTransactionBlock" or something like that, because
that's just what it really does.
Anyway, the server only know whether a BEGIN has been issued.
It never knows or cares whether it has been added implicitly or explicitly,
which is why it can be quite confusing to reason about server-side
differences in terms of Autocommit, as you do in some of your previous
messages.
It should be stressed that Autocommit is not a parameter of the
session between Postgres and the SQL client, but rather it's a
parameter of the session between the user and their SQL client.
So when you're hypothesizing that a plpgsql block in a procedure
would look at this parameter or change it temporarily (your
points #2 and #5 in your analysis of p2's execution), you should
see that it's impossible, because on the server-side, this parameter
just does not exist.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
Attachment
On 8/8/19 11:35 AM, Bryn Llewellyn wrote:
> Please try the following.
>
> Stop PostgreSQL with this:
>
> pg_ctl -D /usr/local/var/postgres stop
>
> Edit the file "/usr/local/var/postgres/postgresql.conf” and add these
> lines at the end:
>
> log_statement = 'all'
> log_directory = 'log'
> logging_collector = on
>
> Then start PostgreSQL with this:
>
> pg_ctl -D /usr/local/var/postgres start
>
> Then, in one terminal window, find the latest log file with this:
>
> ls -l /usr/local/var/postgres/log/postgresql-*.log
>
> Then, in a second terminal window, start psql and do these tests. By all
> means, add your own.
>
> -- Test 1.
> \set AUTOCOMMIT ON
> insert into t(n) values(42);
>
> I see _only_ the “insert” in the log—in other words, no “commit”. (And
> no “BEGIN” for that matter.) If it were the case the psql sends the
> commit after every statement when AUTOCOMMIT is ON, then I’d see this in
> the log. Yet the effect of the “insert” has indeed been committed. You
> can see this by issuing “rollback” (you get the "there is no transaction
> in progress” warning). And a subsequent “select” confirms that the
> “insert” was committed.
Yeah if you do:
log_min_messages = debug5
you see it:
postgres-2019-08-08 13:54:26.842 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 13:54:26.842 PDT-0LOG: statement: insert into t
values (1);
postgres-2019-08-08 13:54:26.843 PDT-38496DEBUG: CommitTransaction(1)
name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
38496/1/0 (used)
>
> My interpretation is that AUTOCOMMIT is a server-side phenomenon (but,
> as we shall see, the client does know that this mode has been set).
Yes it is:
https://www.postgresql.org/docs/11/sql-begin.html:
"BEGIN initiates a transaction block, that is, all statements after a
BEGIN command will be executed in a single transaction until an explicit
COMMIT or ROLLBACK is given. By default (without BEGIN), PostgreSQL
executes transactions in “autocommit” mode, that is, each statement is
executed in its own transaction and a commit is implicitly performed at
the end of the statement (if execution was successful, otherwise a
rollback is done)."
Seems the 7.2 --> 7.3 --> 7.4 server setting for
autocommit(change/change back) planted the idea in my head that it was not:
https://www.postgresql.org/docs/7.4/release-7-4.html
"The server-side autocommit setting was removed and reimplemented in
client applications and languages. Server-side autocommit was causing
too many problems with languages and applications that wanted to control
their own autocommit behavior, so autocommit was removed from the server
and added to individual client APIs as appropriate."
>
> -- Test 2.
> \set AUTOCOMMIT OFF
> insert into t(n) values(42);
> rollback;
>
> I see this in the log:
>
> …LOG: statement: BEGIN
> …LOG: statement: insert into t(n) values(42);
> …LOG: statement: rollback;
>
> (I also see this when I do the test using Python on psycopg2 after doing
> “my_session.set_session(autocommit=False)”. The “rollback” doesn’t draw
> a warning. And, indeed, the effect of my “insert” has been undone, as
> has been shown by a subsequent “select”.)
>
> -- Test 3.
> \set AUTOCOMMIT OFF
> start transaction;
> insert into t(n) values(42);
> rollback;
>
> I see this in the log:
>
> …LOG: statement: start transaction;
> …LOG: statement: insert into t(n) values(42);
> …LOG: statement: rollback;
>
> Same outcome as with Test 2. Test 2 and Test 3 are consistent with the
> hypothesis that it’s the client that issues the “BEGIN” before your
> first SQL command after setting AUTOCOMMIT to OFF. But it doesn’t
> _prove_ the hypothesis. However, experts on this list have said that
> this is how it works. And this is consistent with the fact that, when
> AUTOCOMMIT is ON, I don’t see this in the log:
As shown before that is done in /src/bin/psql/common.c:
if (transaction_status == PQTRANS_IDLE &&
!pset.autocommit &&
!command_no_begin(query))
{
results = PQexec(pset.db, "BEGIN");
Same thing in psycopg2 code, see my link in previous post.
>
> BEGIN;
> insert into t(n) values(42);
> COMMIT;
>
> If AUTOCOMMIT were entirely a client-side phenomenon, then you _would_
> have to see this to get the defined semantics.
>
> In summary, my hypothesis is that AUTOCOMMIT is a server side
> phenomenon. But, because to set it with a call from the client, the
> client does know what mode you’re in and adds its own BEGIN before the
> first SQL command that follows setting AUTOCOMMIT to ON.
You mean OFF. As you have shown autocommit is the default and you either
turn it off by AUTOCOMMIT OFF in psql or equivalent in other clients or
by starting an explicit transaction with BEGIN;
>
> Test 4 is more elaborate. Please run the attached setup_for_test_4.sql,
> and then do this by hand. But do read the definition of "p2()"
> carefully first. Then do this:
>
> -- Test 4.
> \set AUTOCOMMIT ON
> do $$ begin raise notice 'txid at top level before "p2()" ..... %',
> txid_current(); end $$;
> call p2();
> do $$ begin raise notice 'txid at top level after "p1()" ...... %',
> txid_current(); end $$;
> select n from t order by n;
>
> This is the output:
>
> NOTICE: txid at top level before "p2()" ..... 9478
> NOTICE: txid in "p2()" at start ............. 9479
> NOTICE: txid in "p2()" after "set txn"....... 9480, level = repeatable read
> NOTICE: txid in "p2()" after "set txn"....... 9481, level = serializable
> NOTICE: txid at top level after "p1()" ...... 9482
> n
> ----
> 42
>
> And this is what the log shows:
>
> …LOG: statement: do $$ begin raise notice 'txid at top level before
> "p2()" ..... %', txid_current(); end $$;
> …LOG: statement: call p2();
> …LOG: statement: do $$ begin raise notice 'txid at top level after
> "p1()" ...... %', txid_current(); end $$;
> …LOG: statement: select n from t order by n;
Run with debug5 and you will see:
postgres-2019-08-08 14:09:46.171 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 14:09:46.171 PDT-0LOG: statement: do $$ begin raise
notice 'txid at top level before "p2()" ..... %', txid_current(); end $$;
postgres-2019-08-08 14:09:46.172 PDT-38501NOTICE: txid at top level
before "p2()" ..... 38501
postgres-2019-08-08 14:09:46.172 PDT-38501CONTEXT: PL/pgSQL function
inline_code_block line 1 at RAISE
postgres-2019-08-08 14:09:46.172 PDT-38501STATEMENT: do $$ begin raise
notice 'txid at top level before "p2()" ..... %', txid_current(); end $$;
postgres-2019-08-08 14:09:46.172 PDT-38501DEBUG: CommitTransaction(1)
name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
38501/1/0
postgres-2019-08-08 14:09:46.172 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 14:09:46.172 PDT-0LOG: statement: call p2();
postgres-2019-08-08 14:09:46.172 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 14:09:46.172 PDT-0CONTEXT: PL/pgSQL function p2()
line 8 at ROLLBACK
postgres-2019-08-08 14:09:46.172 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 14:09:46.172 PDT-0CONTEXT: PL/pgSQL function p2()
line 13 at ROLLBACK
postgres-2019-08-08 14:09:46.172 PDT-38503DEBUG: CommitTransaction(1)
name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
38503/1/1
postgres-2019-08-08 14:09:46.172 PDT-38503CONTEXT: PL/pgSQL function
p2() line 18 at COMMIT
postgres-2019-08-08 14:09:46.197 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 14:09:46.197 PDT-0CONTEXT: PL/pgSQL function p2()
line 18 at COMMIT
postgres-2019-08-08 14:09:46.197 PDT-0NOTICE: isolation level #1:
repeatable read
postgres-2019-08-08 14:09:46.197 PDT-0CONTEXT: PL/pgSQL function p2()
line 20 at RAISE
postgres-2019-08-08 14:09:46.197 PDT-0STATEMENT: call p2();
postgres-2019-08-08 14:09:46.197 PDT-0NOTICE: isolation level #2:
serializable
postgres-2019-08-08 14:09:46.197 PDT-0CONTEXT: PL/pgSQL function p2()
line 21 at RAISE
postgres-2019-08-08 14:09:46.197 PDT-0STATEMENT: call p2();
postgres-2019-08-08 14:09:46.197 PDT-0DEBUG: CommitTransaction(1) name:
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 14:09:46.198 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 14:09:46.198 PDT-0LOG: statement: do $$ begin raise
notice 'txid at top level after "p1()" ...... %', txid_current(); end $$;
postgres-2019-08-08 14:09:46.199 PDT-38504NOTICE: txid at top level
after "p1()" ...... 38504
postgres-2019-08-08 14:09:46.199 PDT-38504CONTEXT: PL/pgSQL function
inline_code_block line 1 at RAISE
postgres-2019-08-08 14:09:46.199 PDT-38504STATEMENT: do $$ begin raise
notice 'txid at top level after "p1()" ...... %', txid_current(); end $$;
postgres-2019-08-08 14:09:46.199 PDT-38504DEBUG: CommitTransaction(1)
name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
38504/1/0
postgres-2019-08-08 14:09:46.774 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
postgres-2019-08-08 14:09:46.774 PDT-0LOG: statement: select n from t
order by n;
postgres-2019-08-08 14:09:46.774 PDT-0DEBUG: CommitTransaction(1) name:
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
>
> Notice that there’s no visible “BEGIN” between my first “do" block and
> the “call”. And (again) no visible “COMMIT” after the “call" finishes.
> But the txid values show that “call” did indeed start a new txt, the
> “rollback” in the proc ended it so that "set transaction isolation
> level” in the proc can (implicitly) start a new txn. And so on. All this
> is consistent with the hypothesis that there is no such thing as a
> “nested transaction”. Rather, there’s just a series of non-overlapping
> ordinary txns. The BLOG POST
> <https://www.2ndquadrant.com/en/blog/postgresql-11-server-side-procedures-part-1/> that
> I quoted with my first post on this topic explains that this is intended.
>
> This is consistent with the hypothesis that AUTOCOMMIT is indeed a
> server-side phenomenon and that, during the execution of a procedure,
> its usual effect is suspended—and is resumed when the procedure
> execution ends.
No that is not the case as the debug5 information shows. The DO's are in
their own transactions. Each step in your code runs as a separate
transaction so 'call p2()' is running in its own transaction and all is
well.
>
> I’ve said several times that my goal is to set the isolation level from
> inside a proc so that the proc can own the entire logic for concurrent
> txns that might violate a multi-row data-rule.
>
> And I’ve said several times that I discovered that starting my proc with
> “rollback” allows this—and that I've
This is only a problem if you do 'call p2()' inside another transaction:
test=# \set AUTOCOMMIT ON
test=# do $$ begin raise notice 'txid at top level before "p2()" .....
%', txid_current(); end $$;
NOTICE: txid at top level before "p2()" ..... 38519
DO
test=# BEGIN;
BEGIN
test=# call p2();
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function p2() line 8 at ROLLBACK
test=# ROLLBACK;
ROLLBACK
test=# do $$ begin raise notice 'txid at top level after "p1()" ......
%', txid_current(); end $$;
NOTICE: txid at top level after "p1()" ...... 38520
DO
test=# select n from t order by n;
n
---
(0 rows)
>
> found no other way to meet my goal. I don’t understand, therefore, why
> some people (but not you, Daniel!) who’ve responded to my questions say
> that starting my proc with “rollback” is pointless.
>
> I tried, earlier, to say “case closed”. I’ll say it again now.
>
> On 08-Aug-2019, at 06:53, Daniel Verite <daniel@manitou-mail.org
> <mailto:daniel@manitou-mail.org>> wrote:
>
> Bryn Llewellyn wrote:
>
>> B.t.w., I’m guessing that the “begin” SQL command that you see in the log
>> that I mentioned is actually issued by (some) clients—at least psql and
>> Python-on-psycopg2—as an explicit call from the client. In other words, it
>> isn’t the server that generates this. Does anyone know for sure how this
>> works?
>
> Well, that's the point of Autocommit, and moreover it does nothing
> else. Maybe you're still confused about this.
>
> * Autocommit off = the client automatically adds a "BEGIN" when
> it thinks a transaction must be started on behalf of the user.
>
> * Autocommit on = the client does nothing.
>
> The fact that "off" implies doing something and "on" implies not
> interfering is counterintuitive, but that's how it is.Autocommit is
> for compatibility with other databases. If it was only for Postgres, I
> guesss either it wouldn't exist in the first place or it should be
> called "AutoStartTransactionBlock" or something like that, because
> that's just what it really does.
>
> Anyway, the server only know whether a BEGIN has been issued.
> It never knows or cares whether it has been added implicitly or explicitly,
> which is why it can be quite confusing to reason about server-side
> differences in terms of Autocommit, as you do in some of your previous
> messages.
>
> It should be stressed that Autocommit is not a parameter of the
> session between Postgres and the SQL client, but rather it's a
> parameter of the session between the user and their SQL client.
> So when you're hypothesizing that a plpgsql block in a procedure
> would look at this parameter or change it temporarily (your
> points #2 and #5 in your analysis of p2's execution), you should
> see that it's impossible, because on the server-side, this parameter
> just does not exist.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>
--
Adrian Klaver
adrian.klaver@aklaver.com