Thread: RESET, NULL and empty-string valued settings and transaction isolation

Hi, according to the docs, RESET sets the value of a setting to

"The default value is defined as the value that the parameter would have had, if no SET had ever been issued for it in the current session"

Which confuses me given that the value starts being NULL in the session and then turns into an empty string:

$ psql

=> select current_setting('my.test', true) is null; -- true
=> set my.test = 'abc';
=> reset my.test;
=> select current_setting('my.test', true) is null; -- false
=> select current_setting('my.test', true)=''; -- true

A similar effect happens with transactions and SET LOCAL:

=> begin;
=*> set local my.test='abc';
=*> rollback; -- commit works too
=> select current_setting('my.test', true) = ''; -- true

Is this expected? I thought even if I misunderstand the docs, the effect isn't very nice because SQL like current_setting('my.some_boolean_setting')::boolean will fail after a transaction with SET LOCAL sets it, a side-effect that can be particularly confusing and basically requires usage of nullif(.., '') or other explicit checks around every current_setting call-site in practice.

Marcelo Zabani <mzabani@gmail.com> writes:
> Hi, according to the docs, RESET sets the value of a setting to
> "The default value is defined as the value that the parameter would have
> had, if no SET had ever been issued for it in the current session"

This is, I guess, strictly true only for built-in settings.
Custom settings act a little differently in that they don't
exist at all before you assign to them.  Thus

> $ psql
> => select current_setting('my.test', true) is null; -- true

my.test doesn't exist here.  current_setting(..., true) returns NULL
instead of throwing an error, although SHOW reacts differently:
regression=# show my.test;
ERROR:  unrecognized configuration parameter "my.test"

> => set my.test = 'abc';
> => reset my.test;

Now it does exist, but its reset value is an empty string.

> => select current_setting('my.test', true) is null; -- false
> => select current_setting('my.test', true)=''; -- true

> Is this expected? I thought even if I misunderstand the docs, the effect
> isn't very nice because SQL like
> current_setting('my.some_boolean_setting')::boolean will fail after a
> transaction with SET LOCAL sets it, a side-effect that can be particularly
> confusing and basically requires usage of nullif(.., '') or other explicit
> checks around every current_setting call-site in practice.

[ shrug... ]  This whole area is an undocumented, unsupported abuse
of a behavior that's only meant to support GUCs defined by loadable
extensions.  (To wit, allowing postgresql.conf to set values for
GUCs that aren't loaded yet.)  Without a way to declare a GUC's type,
reset value, etc, there's no way to have custom GUCs act really
consistently with built-in ones.

Pavel Stehule has spent years pushing forward a patch to invent
a better-thought-out implementation of custom session variables [1].
Every time I look at it, I come away with the feeling that it's
a giant patch with a much smaller patch struggling to get out.
But certainly the area needs some nontrivial thought, and I'm not
sure that extending the GUC mechanism is a better answer.

            regards, tom lane

[1] https://www.postgresql.org/message-id/flat/CAFj8pRD053CY_N4%3D6SvPe7ke6xPbh%3DK50LUAOwjC3jm8Me9Obg%40mail.gmail.com