From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | bryn(at)yugabyte(dot)com |
Subject: | BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8" |
Date: | 2023-02-07 00:41:23 |
Message-ID: | 17779-f6ef4a1fa1cbe564@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17779
Logged by: Bryn Llewellyn
Email address: bryn(at)yugabyte(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-02-07 02:17:33 | Re: BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8" |
Previous Message | PG Bug reporting form | 2023-02-07 00:28:09 | BUG #17778: the revocation function was unable to check revocation because the revocation server was offline |