Thread: set_config with is_local parameter true escapes transaction boundaries

When executing set_config with the parameter ‘is_local’ set to true within a transaction, it is expected that the
configvalue is not available after the transaction however once the config is set in any transaction, current_setting
willreturn an empty string instead of null 

If this is expected behaviour, it is not mentioned anywhere in the documentation.

Steps to reproduce / example


psql (13.9, server 15.1 (Debian 15.1-1.pgdg110+1))
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.

app=# select current_setting('custom.a', true), current_setting('custom.a', true) is null;
 current_setting | ?column?
-----------------+----------
                     | t
(1 row)

app=# BEGIN;
START TRANSACTION

app=*# select set_config('custom.a', 'vv', true);
 set_config
------------
 vv
(1 row)

app=*# COMMIT;
COMMIT

app=# select current_setting('custom.a', true), current_setting('custom.a', true) is null;
 current_setting | ?column?
-----------------+----------
                     | f
(1 row)

Ruslan Talpa <ruslan.talpa@subzero.cloud> writes:
> When executing set_config with the parameter ‘is_local’ set to true within a transaction, it is expected that the
configvalue is not available after the transaction however once the config is set in any transaction, current_setting
willreturn an empty string instead of null 

The GUC infrastructure doesn't recognize null as a value, so you
are making a distinction that the system doesn't.

            regards, tom lane

It’s true that null is not a valid value for GUC however the infrastructure does make a distinction between “the
settinghas no value, as in empty string” and “there is no such setting, null” and since the documentation for
set_configonly says “If is_local is true, the new value will only apply during the current transaction.” the
expectationis that after the transaction things should return to the previous state (i.e current_setting should return
null)

Maybe this is only a documentation issue, for example have a note in 9.27.1 or 20.16 along the lines “Note: Once a GUC
valuehas been set, even with is_local set to true, subsequent calls to current_setting will return an empty string” 

In any case, I am only reporting (what I view as) an inconsistency i came across that is not mentioned anywhere and
thoughtI’d bring it up so that PG developers are aware can make a decision either way. 

Thank you for your work (and reply to the email)

PS: A few more words about the context where this came up, feel free to skip this.
Per 20.16 it’s clear that this infrastructure was designed for PG extensions to use, however currently it’s being
(ab)usedby popular tools like PostgREST/Postgraphile/Hasura as a “transaction context”, i.e. store within a transaction
thingslike “who is executing the current query” or “from what ip” so that code from the database context
(triggers/views/storedprocedures) can act based on that information. Looking at it from this use case point of view,
onewould want to make a distinction between “that context value is not set” and “that context value is an empty string”
orat least be aware this issue exists. 


> On 11 Jan 2023, at 02:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ruslan Talpa <ruslan.talpa@subzero.cloud> writes:
>> When executing set_config with the parameter ‘is_local’ set to true within a transaction, it is expected that the
configvalue is not available after the transaction however once the config is set in any transaction, current_setting
willreturn an empty string instead of null 
>
> The GUC infrastructure doesn't recognize null as a value, so you
> are making a distinction that the system doesn't.
>
> regards, tom lane