Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8" - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8"
Date
Msg-id CAKFQuwYgw1XRRtwydbSGg2hn8X=J3HUiCA+AbWEaUSNySuaFcQ@mail.gmail.com
Whole thread Raw
In response to BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8"  (PG Bug reporting form <noreply@postgresql.org>)
List pgsql-bugs
Tree view
On Mon, Feb 6, 2023 at 5:47 PM PG Bug reporting form <noreply@postgresql.org> wrote:
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

create procedure s.transaction_test1()
  --set search_path = pg_catalog, pg_temp

call s.transaction_test1();

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
If this is a known bug, then please tell me the number.


The CALL command documentation must be considered as well.

https://www.postgresql.org/docs/current/sql-call.html

"If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction."

And also an implementation detail that may be under-documented...in order for the SET specification (which is LOCAL) to go into effect when CALL is executed a transaction must already exist and so, if it doesn't, PostgreSQL creates one inside which the CALL is executed thus preventing transaction control commands from working within the procedure.

The way around this behavior is to add the "SET LOCAL search_path TO ..." after the BEGIN inside the procedure body.

David J.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8"
Next
From: Andres Freund
Date:
Subject: Re: BUG #17779: "commit" causes error 2D000 when "set search_path" is added to the first example in section "43.8"