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

On Mon, Feb 6, 2023 at 5:47 PM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> 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
>
> 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.