Date: Fri, 14 Mar 2025 11:49:06 +0000
From: Viktor Holmberg <v@viktorh.net>
To: pgsql-bugs@lists.postgresql.org
Message-ID: <790bc1f9-74dc-4b50-94d2-8147315b1556@Spark>
In-Reply-To: <ae6ed432-4491-4030-be20-3ea69d03a098@Spark>
References: <ae6ed432-4491-4030-be20-3ea69d03a098@Spark>
Subject: ISN extension - wrong volatility level for isn_weak() function
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="67d417ba_2ca88611_1881"
Archived-At: 
 <https://www.postgresql.org/message-id/790bc1f9-74dc-4b50-94d2-8147315b1556%40Spark>
Precedence: bulk

--67d417ba_2ca88611_1881
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello. Apologies if this is not the right place to bug report extensions,=
 but I couldn=E2=80=99t find a better place. An at the very least I=E2=80=
=99d like to save someone a day of debugging this issue.

The isn=5Fweak function in the isn extension reports the wrong value if y=
ou look at it inside a transaction:

BEGIN;

-- Prepare a statement for isn=5Fweak():
PREPARE s=5F1 AS
=C2=A0SELECT isn=5Fweak() AS prepared=5Fcheck;

-- =46irst check - locks in the isn=5Fweak value
EXECUTE s=5F1; -- =3D> =46ALSE

-- Set isn=5Fweak(true):
SELECT isn=5Fweak(true) AS setting=5Ftrue;

-- Check using normal query:
SELECT isn=5Fweak() AS direct=5Fcheck; -- =3D> TRUE

EXECUTE s=5F1; -- =3D> =46ALSE (=21=21=21=21)

This is because by default, that function is marked as immutable:

d6kpcv43m9du6> =5Cdf+ isn=5Fweak
List of functions
=E2=94=80=5B RECORD 1 =5D=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80
Schema =E2=94=82 public
Name =E2=94=82 isn=5Fweak
Result data type =E2=94=82 boolean
Argument data types =E2=94=82
Type =E2=94=82 func
Volatility =E2=94=82 immutable <=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94 bad
Parallel =E2=94=82 restricted
Owner =E2=94=82 postgres
Security =E2=94=82 invoker
Access privileges =E2=94=82
Language =E2=94=82 c
Internal name =E2=94=82 weak=5Finput=5Fstatus

I can manually fix this by changing it to STABLE:

ALTER =46UNCTION isn=5Fweak() STABLE;

Am I missing something or isn=E2=80=99t this quite weird=3F Would it at l=
east be possible to change the documentation to explain this=3F

/Viktor Holmberg

--67d417ba_2ca88611_1881
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Hello. Apologies if this is not the right place to =
bug report extensions, but I couldn=E2=80=99t find a better place. An at =
the very least I=E2=80=99d like to save someone a day of debugging this i=
ssue.<br />
<br />
The isn=5Fweak function in the isn extension reports the wrong value if y=
ou look at it inside a transaction:<br />
<br />
BEGIN;<br />
<br />
-- Prepare a statement for isn=5Fweak():<br />
PREPARE s=5F1 AS<br />
&=23160;SELECT isn=5Fweak() AS prepared=5Fcheck;<br />
<br />
-- =46irst check - locks in the isn=5Fweak value<br />
EXECUTE s=5F1; -- =3D&gt; =46ALSE<br />
<br />
-- Set isn=5Fweak(true):<br />
SELECT isn=5Fweak(true) AS setting=5Ftrue;<br />
<br />
-- Check using normal query:<br />
SELECT isn=5Fweak() AS direct=5Fcheck; -- =3D&gt; TRUE<br />
<br />
EXECUTE s=5F1; -- =3D&gt; =46ALSE (=21=21=21=21)<br />
<br />
This is because by default, that function is marked as immutable:<br />
<br />
d6kpcv43m9du6&gt; =5Cdf+ isn=5Fweak<br />
List of functions<br />
=E2=94=80=5B RECORD 1 =5D=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80<br />
Schema =E2=94=82 public<br />
Name =E2=94=82 isn=5Fweak<br />
Result data type =E2=94=82 boolean<br />
Argument data types =E2=94=82<br />
Type =E2=94=82 func<br />
Volatility =E2=94=82 immutable &lt;=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94 bad<br />
Parallel =E2=94=82 restricted<br />
Owner =E2=94=82 postgres<br />
Security =E2=94=82 invoker<br />
Access privileges =E2=94=82<br />
Language =E2=94=82 c<br />
Internal name =E2=94=82 weak=5Finput=5Fstatus<br />
<br />
I can manually fix this by changing it to STABLE:<br />
<br />
ALTER =46UNCTION isn=5Fweak() STABLE;<br />
<br />
Am I missing something or isn=E2=80=99t this quite weird=3F Would it at l=
east be possible to change the documentation to explain this=3F</div>
</div>
<div name=3D=22messageSignatureSection=22><br />
<div class=3D=22match=46ont=22>/Viktor Holmberg</div>
</div>
</body>
</html>

--67d417ba_2ca88611_1881--