why does postgres set_config's is_local = true not persist the variable for the whole transaction?

Question

I have a function that uses set_config with is_local = true to set a variable. Now I expected that a select statement using the variable with current_settings within the same transaction would be able to access the variable, because the docs state:

set_config(setting_name, new_value, is_local) ... set parameter and return new value

set_config sets the parameter setting_name to new_value. If is_local is true, the new value will only apply to the current transaction.

But that doesn't work in my case and I get

ERROR:  unrecognized configuration parameter "auth.tenant_id"
SQL state: 42704

Any ideas where I am wrong here?

This is the function:

CREATE OR REPLACE FUNCTION auth.authorize(IN a_user_id uuid)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    SECURITY DEFINER 
AS $BODY$
declare
v_tenant_id uuid;
v_user_role auth.user_role;
begin
select tenant_id, user_role into strict v_tenant_id, v_user_role from auth.authorizations where user_id = a_user_id;
perform set_config('auth.tenant_id', v_tenant_id::text, true);
perform set_config('auth.user_role', v_user_role::text, true);
return true;
exception when no_data_found then return false;
end;
$BODY$;

And this is the transaction in which the second select statement fails

begin;
select * from auth.authorize(uuid('180e1b14-21e5-4e66-a9b8-db09139d6278'));
select current_setting('auth.tenant_id') as tenant_id, current_setting('auth.user_role') as user_role;
commit;

Answer 1

The effect of set_config(is_local => true) seems to be restrained to the implicit subtransaction created by your BEGIN … EXCEPTION block.

(Skip to the end of my answer for my TL;DR solution to your problem.)

Here's what happens when I do something similar, but with the call to set_config() outside of the BEGIN … EXCEPTION subtransaction:

create or replace function set_config_outside_of_begin_except_block(denominator int)
    returns bool
    language 'plpgsql'
as $body$
begin
    perform set_config('my.setting', 'set before subtransaction', true);

    begin
        perform 10 / denominator;
        return true;
    exception when division_by_zero then
        return false;
    end;
end;
$body$;

begin
select current_setting('my.setting') as my_setting;
select set_config_outside_of_begin_except_block(1);
rollback;

CREATE FUNCTION
BEGIN
ERROR:  unrecognized configuration parameter "my.setting"
 set_config_outside_of_begin_except_block
------------------------------------------
 t
(1 row)

        my_setting
---------------------------
 set before subtransaction
(1 row)

ROLLBACK

Having moved the set_config() call to before the BEGIN … EXCEPTION block, the new setting for my.setting has persisted outside our function call.

And, as you will next see, it doesn't even matter if an exception occurs in the above BEGIN … EXCEPTION block, which makes sense, given that the set_config() is called before that block/subtransaction is entered:

begin
select current_setting('my.setting') as my_setting;
rollback;

BEGIN
 set_config_outside_of_begin_except_block
------------------------------------------
 f
(1 row)
        my_setting

---------------------------
 set before subtransaction
(1 row)

ROLLBACK

Now, I will modify the code a bit to come closer to your example, and closer to reproducing the behavior you describe:

create or replace function set_config_in_begin_except_block(denominator int)
    returns bool
    language 'plpgsql'
as $body$
begin
    perform set_config('my.setting', 'set in subtransaction', true);
    perform 10 / denominator;
    return true;
exception when division_by_zero then
    return false;
end;
$body$;

begin;
select set_config_in_begin_except_block(0);
select current_setting('my.setting') as my_setting;
rollback;

CREATE FUNCTION
BEGIN
 set_config_in_begin_except_block
----------------------------------
 f
(1 row)

 my_setting
------------

(1 row)

ROLLBACK

begin
select set_config_in_begin_except_block(1);
select current_setting('my.setting') as my_setting;
rollback;

BEGIN
 set_config_in_begin_except_block
----------------------------------
 t
(1 row)

      my_setting
-----------------------
 set in subtransaction
(1 row)

ROLLBACK

Note that, now,

1. the BEGIN … EXCEPTION block does cause the change to my.setting to get lost,
2. but only if an exception (division_by_zero) indeed got raised,
3. even though the division_by_zero was triggered by the statement after set_config().

So, it's somewhat close to the behaviour you describe, but not quite. Your setting didn't persist outside of your BEGIN … EXCEPTION block regardless of whether a no_data_found exception was hit or not.

As a little detour: I stumbled upon your question, because I was interested in whether I could treat the settings like a stack that unwinds together with the (sub)transaction stack. It turns out that I could:

create or replace function set_config_stacked(denominator int)
    returns bool
    language 'plpgsql'
as $body$
begin
    perform set_config('my.setting', 'set before substraction', true);

    begin
        perform set_config('my.setting', 'set within subtransaction before division', true);
        perform 10 / denominator;
        return true;
    exception when division_by_zero then
        return false;
    end;
end;
$body$;

begin;
select set_config_stacked(1);
select current_setting('my.setting') as my_setting;
rollback;

CREATE FUNCTION
BEGIN
 set_config_stacked
--------------------
 t
(1 row)

                my_setting
-------------------------------------------
 set within subtransaction before division
(1 row)

ROLLBACK

begin;
select set_config_stacked(0);
select current_setting('my.setting') as my_setting;
rollback;

BEGIN
 set_config_stacked
--------------------
 f
(1 row)

       my_setting
-------------------------
 set before substraction
(1 row)

ROLLBACK

So my.setting is actually restored to its previous setting when the subtransaction of the BEGIN … EXCEPTION block closes.

I need one final change to reproduce your function's behaviour:

create table tab (id uuid primary key, stuff text);
insert into tab (id, stuff) values (uuid('180e1b14-21e5-4e66-a9b8-db09139d6278'), 'some stuff');

create or replace function set_config_stacked(id$ uuid)
    returns bool
    language 'plpgsql'
as $body$
declare
    rec tab%rowtype;
begin
    perform set_config('my.setting', 'set before substraction', true);

    begin
        perform set_config('my.setting', 'set within subtransaction before SELECT', true);
        select * into strict rec from tab where id = id$;
        perform set_config('my.setting', 'set within subtransaction after SELECT', true);
        return true;
    exception when no_data_found then
        return false;
    end;
end;
$body$;

begin;
select set_config_stacked(uuid('180e1b14-21e5-4e66-a9b8-db09139d6278'));  -- exist
select current_setting('my.setting') as my_setting;
rollback;

CREATE TABLE
INSERT 1 0
CREATE FUNCTION
 set_config_stacked
--------------------
 t
(1 row)

               my_setting
----------------------------------------
 set within subtransaction after SELECT
(1 row)

ROLLBACK

begin;
select set_config_stacked(uuid('cc7ad0c3-7e3a-49a0-b7d8-7b4093ae0028'));  -- doesn't exist
select current_setting('my.setting') as my_setting;
rollback;

BEGIN
 set_config_stacked
--------------------
 f
(1 row)

       my_setting
-------------------------
 set before substraction
(1 row)

ROLLBACK

As you can see, there is still one aspect of the behavior that you describe which I cannot reproduce. This version of set_config_stacked() that operates around a no_data_found exception behaves the same as the previous version based around the division_by_zero exception.

However, your example suggests that your settings also don't persist outside of auth.authorize() when no exception is hit. That confounds me, and is not something I am able to reproduce, at least here on Postgres 14.

Regardless of this, your problem can be solved by moving the calls to set_config() below the BEGIN … EXCEPTION … END block. You will still want to set your variables within that block, but do these calls and the return true from a new to be created outer BEGIN … END block (without EXCEPTION).