Here’s a cut-down version of Umair Shahid’s blog post here:
__________
create table t(k int primary key, v int not null);
create or replace procedure p()
language plpgsql
security invoker
as $$
begin
insert into t(k, v) values(1, 17);
rollback;
insert into t(k, v) values(1, 42);
commit;
end
$$;
call p();
select * from t order by k;
__________
It runs without error and shows that the effect of “rollback” and “commit” is what the names of those statements tells you to expect.
The post starts with “Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL… [with] transaction control – allowing us to COMMIT and ROLLBACK inside procedures.”. I believe that Umair is referring to work done by Peter Eisentraut.
But simply change “security invoker” to “security definer” and rerun the test. You get the notorious error “2D000: invalid transaction termination”.
Please tell me that this is a plain bug—and not the intended semantics.