MIME-Version: 1.0
In-Reply-To: <CACgY3QbXpCBpM9ShSo2-VM9x=5GiXJeY-4hqhCXa8bkEGuXGvw@mail.gmail.com>
References: <CACgY3QbXpCBpM9ShSo2-VM9x=5GiXJeY-4hqhCXa8bkEGuXGvw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 19 Oct 2024 08:03:33 -0700
Message-ID: <CAKFQuwYN72-k=OQ9FxL0BawvOTfp7si++m4dvMLzE3Xbf0sg+A@mail.gmail.com>
Subject: Re: RESET, NULL and empty-string valued settings and transaction isolation
To: Marcelo Zabani <mzabani@gmail.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003504d90624d5b921"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYN72-k%3DOQ9FxL0BawvOTfp7si%2B%2Bm4dvMLzE3Xbf0sg%2BA%40mail.gmail.com>
Precedence: bulk

--0000000000003504d90624d5b921
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, October 19, 2024, Marcelo Zabani <mzabani@gmail.com> 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
> =3D> select current_setting('my.test', true) is null; -- true
> =3D> set my.test =3D 'abc';
> =3D> reset my.test;
> =3D> select current_setting('my.test', true) is null; -- false
> =3D> select current_setting('my.test', true)=3D''; -- true
>
> A similar effect happens with transactions and SET LOCAL:
> =3D> begin;
> =3D*> set local my.test=3D'abc';
> =3D*> rollback; -- commit works too
> =3D> select current_setting('my.test', true) =3D ''; -- 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=E2=80=99t 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=E2=80=99t 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=E2=80=99s better IMO to design the system to en=
sure 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.

--0000000000003504d90624d5b921
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, October 19, 2024, Marcelo Zabani &lt;<a href=3D"mailto:mzabani=
@gmail.com">mzabani@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_=
quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1=
ex"><div dir=3D"ltr"><div>Hi, according to the docs, RESET sets the value o=
f a setting to</div><div>&quot;The default value is defined as the value th=
at the parameter would have had, if no <code>SET</code> had ever been issue=
d for it in the current session&quot;</div><div><br></div><div>Which confus=
es me given that the value starts being NULL in the session and then turns =
into an empty string:</div><div>$ psql<br></div><div>=3D&gt; select current=
_setting(&#39;my.test&#39;, true) is null; -- true<br>=3D&gt; set my.test =
=3D &#39;abc&#39;;<br>=3D&gt; reset my.test;<br>=3D&gt; select current_sett=
ing(&#39;my.test&#39;, true) is null; -- false<br>=3D&gt; select current_se=
tting(&#39;my.test&#39;, true)=3D&#39;&#39;; -- true</div><div><br></div><d=
iv>A similar effect happens with transactions and SET LOCAL:</div><div>=3D&=
gt; begin;<br>=3D*&gt; set local my.test=3D&#39;abc&#39;;<br>=3D*&gt; rollb=
ack; -- commit works too<br>=3D&gt; select current_setting(&#39;my.test&#39=
;, true) =3D &#39;&#39;; -- true<br></div><div><br></div><div>Is this expec=
ted? I thought even if I misunderstand the docs, the effect isn&#39;t very =
nice because SQL like current_setting(&#39;my.some_<wbr>boolean_setting&#39=
;)::boolean will fail after a transaction with SET LOCAL sets it, a side-ef=
fect that can be particularly confusing and basically requires usage of nul=
lif(.., &#39;&#39;) or other explicit checks around every current_setting c=
all-site in practice.</div></div></blockquote><div><br></div><div>The obser=
ved behavior is also the documented behavior.</div><div><br></div><div>A se=
tting value can never actually be the NULL value.=C2=A0 NULL is only an ind=
icator that a setting doesn=E2=80=99t exist - i.e., when current_setting re=
turns 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 setti=
ng doesn=E2=80=99t exist.</div><div><br></div><div>You can only reset a set=
ting that does exist at which point it resets to its default/initial value.=
=C2=A0 For custom settings this is always text and the default/initial valu=
e for a text setting is the empty string.</div><div><br></div><div>There is=
 a pending documentation patch about NULL values that enhances the coverage=
 of this in the documentation.</div><div><br></div><div>If you design a sys=
tem 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.=
=C2=A0 But it=E2=80=99s better IMO to design the system to ensure the setti=
ng always exists up front and let its absence be an error that indicates a =
coding bug that needs to be fixed.</div><div><br></div><div>There is also a=
n open patch to add proper session variables and formally deprecate this ha=
ck-ish usage of the settings subsystem for this use case.</div><div><br></d=
iv><div>David J.</div><div><br></div>

--0000000000003504d90624d5b921--