Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

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