Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? |
Date | |
Msg-id | c864806b-6df5-5a28-4969-35f9ae36e360@aklaver.com Whole thread Raw |
In response to | Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? (Bryn Llewellyn <bryn@yugabyte.com>) |
Responses |
Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
|
List | pgsql-general |
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
pgsql-general by date: