Hi,
On 2023-02-07 00:41:23 +0000, PG Bug reporting form wrote:
> 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.
It's documented, although not that easy to find:
https://www.postgresql.org/docs/devel/sql-createprocedure.html
If a SET clause is attached to a procedure, then that procedure cannot
execute transaction control statements (for example, COMMIT and ROLLBACK,
depending on the language).
Perhaps this should be a <warning>?
The relevant piece of code has an explanation as to why the restriction exists:
/*
* If proconfig is set we can't allow transaction commands because of the
* way the GUC stacking works: The transaction boundary would have to pop
* the proconfig setting off the stack. That restriction could be lifted
* by redesigning the GUC nesting mechanism a bit.
*/
if (!heap_attisnull(tp, Anum_pg_proc_proconfig, NULL))
callcontext->atomic = true;
This is in ExecuteCallStatement(), which basically means that this is a
general restriction for procedures, not plpgsql specific.
Seems like this should be mentioned in the plpgsql docs as well?
https://www.postgresql.org/docs/current/plpgsql-transactions.html
Greetings,
Andres Freund
