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@xxxxxxxxxxx
