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(dot)klaver(at)aklaver(dot)com