set_config with is_local parameter true escapes transaction boundaries

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 config value is not available after the transaction however once the config is set in any transaction, current_setting will return 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 setting has no value, as in empty string” and “there is no such setting, null” and since the documentation for set_config only says “If is_local is true, the new value will only apply during the current transaction.” the expectation is 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 value has 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 thought I’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)used by popular tools like PostgREST/Postgraphile/Hasura as a “transaction context”, i.e. store within a transaction things like “who is executing the current query” or “from what ip” so that code from the database context (triggers/views/stored procedures) can act based on that information. Looking at it from this use case point of view, one would want to make a distinction between “that context value is not set” and “that context value is an empty string” or at least be aware this issue exists.

Yes, The behaviour changes after the parameter are used for the first time.
This could be a problem if the connection is reused (pool)
RESET/DISCARD also not bringing back the original behaviour.

--Please test this on a fresh connection
--Check the status before the first transaction
select current_setting('custom.a',true),current_setting('custom.a',true) is
null, current_setting('custom.a',true)='';

--First transaction where the parameter is set at the transaction level
BEGIN;
SELECT set_config('custom.a', 'vv', true);
COMMIT;

--Reset
RESET ALL;
DISCARD ALL;

--Check the status after the first transaction
select current_setting('custom.a',true),current_setting('custom.a',true) is
null, current_setting('custom.a',true)='';

-Jobin

On Wed, Jan 11, 2023 at 1:16 AM Ruslan Talpa <ruslan.talpa@subzero.cloud>
wrote:

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 value has been set, even
with is_local set to true, subsequent calls to current_setting will return
an empty string”

Yes, we really should get around to documenting this better. I haven't
seen any indication that it is going to change. It hasn't in the 5 years
or so since it last was brought to our attention.

/messages/by-id/CAKFQuwbQ40dUuNbQb8R9h53WD1LOV2dVEJv=LydJLwt+dxZQGw@mail.gmail.com

Though maybe with our recent performance enhancements, and a more accepting
mentality toward people creating custom settings, and the stalled nature of
the session variables feature, maybe someone will have a change of heart.

David J.