public inbox for [email protected]
help / color / mirror / Atom feedRESET, NULL and empty-string valued settings and transaction isolation
2+ messages / 2 participants
[nested] [flat]
* RESET, NULL and empty-string valued settings and transaction isolation
@ 2024-10-19 14:07 Marcelo Zabani <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Marcelo Zabani @ 2024-10-19 14:07 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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.
Thanks in advance,
Marcelo.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: RESET, NULL and empty-string valued settings and transaction isolation
@ 2024-10-19 15:03 David G. Johnston <[email protected]>
parent: Marcelo Zabani <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2024-10-19 15:03 UTC (permalink / raw)
To: Marcelo Zabani <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Saturday, October 19, 2024, Marcelo Zabani <[email protected]> wrote:
> 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.
>
The observed behavior is also the documented behavior.
A setting value can never actually be the NULL value. NULL is only an
indicator that a setting doesn’t exist - i.e., when current_setting returns
null it is not giving you the value of the named setting but instead giving
you an alternative representation to an error message that the setting
doesn’t exist.
You can only reset a setting that does exist at which point it resets to
its default/initial value. For custom settings this is always text and the
default/initial value for a text setting is the empty string.
There is a pending documentation patch about NULL values that enhances the
coverage of this in the documentation.
If you design a system that has to deal with a setting not existing in the
session then you at least have the ability to test for that case without
provoking an error. But it’s better IMO to design the system to ensure the
setting always exists up front and let its absence be an error that
indicates a coding bug that needs to be fixed.
There is also an open patch to add proper session variables and formally
deprecate this hack-ish usage of the settings subsystem for this use case.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-10-19 15:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-19 14:07 RESET, NULL and empty-string valued settings and transaction isolation Marcelo Zabani <[email protected]>
2024-10-19 15:03 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox