Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? |
| Date | |
| Msg-id | 9E141BBF-29E8-4825-9E7D-5F96BAD4365B@yugabyte.com Whole thread Raw |
| Responses |
Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
|
| List | pgsql-general |
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
pgsql-general by date: