MIME-Version: 1.0
From: Marcelo Zabani <mzabani@gmail.com>
Date: Sat, 19 Oct 2024 11:07:06 -0300
Message-ID: <CACgY3QbXpCBpM9ShSo2-VM9x=5GiXJeY-4hqhCXa8bkEGuXGvw@mail.gmail.com>
Subject: RESET, NULL and empty-string valued settings and transaction isolation
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f163000624d4efd0"
Archived-At: <https://www.postgresql.org/message-id/CACgY3QbXpCBpM9ShSo2-VM9x%3D5GiXJeY-4hqhCXa8bkEGuXGvw%40mail.gmail.com>
Precedence: bulk

--000000000000f163000624d4efd0
Content-Type: text/plain; charset="UTF-8"

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.

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

<div dir=3D"ltr"><div>Hi, according to the docs, RESET sets the value of a =
setting to</div><div>&quot;The default value is defined as the value that t=
he parameter would have had, if no <code class=3D"gmail-command">SET</code>=
 had ever been issued for it in the current session&quot;</div><div><br></d=
iv><div>Which confuses me given that the value starts being NULL in the ses=
sion 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_setting(&#39;my.test&#39;, true) is null; -- false<br>=3D&=
gt; select current_setting(&#39;my.test&#39;, true)=3D&#39;&#39;; -- true</=
div><div><br></div><div>A similar effect happens with transactions and SET =
LOCAL:</div><div>=3D&gt; begin;<br>=3D*&gt; set local my.test=3D&#39;abc=
9;;<br>=3D*&gt; rollback; -- commit works too<br>=3D&gt; select current_set=
ting(&#39;my.test&#39;, true) =3D &#39;&#39;; -- true<br></div><div><br></d=
iv><div>Is this expected? I thought even if I misunderstand the docs, the e=
ffect isn&#39;t very nice because SQL like current_setting(&#39;my.some_boo=
lean_setting&#39;)::boolean will fail after a transaction with SET LOCAL se=
ts it, a side-effect that can be particularly confusing and basically requi=
res usage of nullif(.., &#39;&#39;) or other explicit checks around every c=
urrent_setting call-site in practice.</div><div><br></div><div>Thanks in ad=
vance,</div><div>Marcelo.<br></div></div>

--000000000000f163000624d4efd0--