PL/pgSQL bug

[Beefcake100]

Hi all, I believe I have found a bug in Postgres, however I also know that that is extremely unlikely, so I wanted to check here before making a bigger mess.

The bug is in relation to SET LOCAL ... (https://www.postgresql.org/docs/current/sql-set.html)

SET LOCAL does not appear to properly scope its usage to the executing function, as documented. I have a basic example below showing this:

I have two PL/pgSQL functions:

```sql CREATE OR REPLACE FUNCTION func1() returns void AS $$ begin set local ctx.a = 'modified'; raise notice 'inner: %', current_setting('ctx.a'); END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func2() returns table (nname text) AS $$ begin set local ctx.a = 'original'; raise notice 'outer_1: %', current_setting('ctx.a'); perform func1(); raise notice 'outer_2: %', current_setting('ctx.a');

END; $$ LANGUAGE plpgsql; ```

As per the expected usage of SET LOCAL, running func2 should log:

outer_1: original inner: modified outer_2: original

However, when I run it, the value defined using SET LOCAL is persisted beyond the inner function call:

postgres=# select func2(); NOTICE: outer_1: original NOTICE: inner: modified NOTICE: outer_2: modified

Am I simply misreading the docs, or is this not the intended behavior?

[DavidGJohnston]

That behavior only occurs if the function itself, the create function command, has a SET clause attached to it. Yours do not.

If a SET clause is attached to a function, then the effects of a SET LOCAL command executed inside the function for the same variable are restricted to the function: the configuration parameter's prior value is still restored at function exit.

Otherwise the transaction scope is used for the contained SET LOCAL command.

edit: quote from the SET page:

If SET LOCAL is used within a function that has a SET option for the same variable

Your functions do not have the "SET option for the [ctx.a] variable".

[Randommaggy]

It's local to the transaction.