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

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