From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bryn@yugabyte.com
Subject: BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8"
Date: Tue, 07 Feb 2023 00:41:23 +0000
Message-ID: <17779-f6ef4a1fa1cbe564@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, bryn@yugabyte.com
Subject: Re: BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8"
In-Reply-To: <17779-f6ef4a1fa1cbe564@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox