BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8"

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.

Responses

Browse pgsql-bugs by date

  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