The following bug has been logged on the website:
Bug reference: 17779
Logged by: Bryn Llewellyn
Email address: bryn@yugabyte.com
PostgreSQL version: 15.1
Operating system: Ubuntu 22.04
Description:
This code is adapted (but only very slightly) from the PostgreSQL 15 doc:
43.8. Transaction Management
https://www.postgresql.org/docs/current/plpgsql-transactions.html
The change is that "create schema s" is added and all references to
schema-objects use correspondingly qualified identifiers.
create schema s;
create table s.test1(a int);
create procedure s.transaction_test1()
--set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
begin
for i in 0..9 loop
insert into s.test1 (a) values (i);
if i % 2 = 0 then
commit;
else
rollback;
end if;
end loop;
end;
$body$;
call s.transaction_test1();
select a from s.test1 order by 1;
As presented, it runs without error and produces the expected result:
a
---
0
2
4
6
8
The test is done using Version 15.1 in a Ubuntu 22.04.1 LTS VM.
The plan is to follow the implicit recommendation from elsewhere in the docs
by making the value of "search_path" that the procedure sees at runtime
immune to the session's setting of this run-time parameter—and, as a
secondary point, to force the use of qualified identifiers to make the
code's intention explicit for the reader.
However, when the "set search_path" line is uncommented, and procedure
"s.transaction_test1()" is recompiled, it causes the 2D000 runtime error:
invalid transaction termination
Of course, now no rows are inserted into the target table.
The outcome is the same if this is used:
set search_path = pg_catalog, s, pg_temp
for those who prefer less cluttered code.
If this is a known bug, then please tell me the number.
