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

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

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.

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

andres@anarazel.de wrote:

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/15/sql-createprocedure.html

«

If a SET clause is attached to a procedure… However, an ordinary SET command (without LOCAL) overrides the SET clause, much as it would do for a previous SET LOCAL command: the effects of such a command will persist after procedure exit, unless the current transaction is rolled back. 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:

/*
 * …That restriction could be lifted by redesigning the GUC nesting mechanism a bit.
*/

...this is a general restriction for procedures, not plpgsql specific.


Seems like this should be mentioned in the plpgsql docs as well [here]:

https://www.postgresql.org/docs/current/plpgsql-transactions.html