MIME-Version: 1.0
References: <afb4e296d7ce57986f23bcfdee39b259b8f85f56.camel@cybertec.at>
 <CAFj8pRAjU-X6rEE9=1++PdtXOPc2uo=yu-tcFXByi-kN3B_7Vw@mail.gmail.com>
 <CAFj8pRC+hPCc2X88xC=pTJoqmVPApDsageZOMyqaxi5788WxHA@mail.gmail.com>
 <CAFj8pRDJ9cq00VYSHxs6LsoHNWjhYXyWWBtV6UgeWwhs0AHa9A@mail.gmail.com>
 <CAFj8pRBPXTcw_3fpKtgVthV2+9rZGhxitZ40DnAwCrK601TZZg@mail.gmail.com>
 <ndtfl4tsnpkb7m7hwvnmlpsascpgd3a7xvjmjhtxffsbrgygtm@4du6zsmnnwq5>
 <CAFj8pRAu4XvNCGu1751t=2YEqLqTjDA3FavMExm2S0KYQq=DdQ@mail.gmail.com>
 <CAFj8pRAsEoeZv0HEnA8CKgFKDSQ-wYw18Os1vdksWCV7ez2bVw@mail.gmail.com>
 <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65>
 <CAFj8pRBoWPDTOwn5FmMzc+1qiopw+N04U26nviOdF61fs8A2wQ@mail.gmail.com>
 <stckyvkl4yyzvgjsaawojs3xikke7mmds5bhv7l7qerclywywk@h4v4n43xm6u2>
 <CAFj8pRB_E1GM_YGT-ti4bXka6mhLdAAFeTe+BHgHFYC+qb-76g@mail.gmail.com>
In-Reply-To: 
 <CAFj8pRB_E1GM_YGT-ti4bXka6mhLdAAFeTe+BHgHFYC+qb-76g@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Wed, 21 May 2025 09:12:54 +0200
Message-ID: 
 <CAFj8pRCE=zkECNS9E-eLv9tbyUqCR-txx7eZp+GHF1_LKFUAOg@mail.gmail.com>
Subject: Re: proposal: schema variables
To: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, Erik Rijkers <er@xs4all.nl>,
	Michael Paquier <michael@paquier.xyz>, Amit Kapila <amit.kapila16@gmail.com>,
	DUVAL REMI <REMI.DUVAL@cheops.fr>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Bruce Momjian <bruce@momjian.us>,
	jian he <jian.universality@gmail.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>
Content-Type: multipart/alternative; boundary="0000000000007c6a550635a01a79"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRCE%3DzkECNS9E-eLv9tbyUqCR-txx7eZp%2BGHF1_LKFUAOg%40mail.gmail.com>
Precedence: bulk

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

Hi

=C3=BAt 19. 11. 2024 v 20:14 odes=C3=ADlatel Pavel Stehule <pavel.stehule@g=
mail.com>
napsal:

> Hi
>
> I wrote POC of VARIABLE(varname) syntax support
>
> here is a copy from regress test
>
> SET session_variables_ambiguity_warning TO on;
> SET session_variables_use_fence_warning_guard TO on;
> SET search_path TO 'public';
> CREATE VARIABLE a AS int;
> LET a =3D 10;
> CREATE TABLE test_table(a int, b int);
> INSERT INTO test_table VALUES(20, 20);
> -- no warning
> SELECT a;
>  a..
> ----
>  10
> (1 row)
>
> -- warning variable is shadowed
> SELECT a, b FROM test_table;
> WARNING:  session variable "a" is shadowed
> LINE 1: SELECT a, b FROM test_table;
>                ^
> DETAIL:  Session variables can be shadowed by columns, routine's variable=
s
> and routine's arguments with the same name.
>  a  | b..
> ----+----
>  20 | 20
> (1 row)
>
> -- no warning
> SELECT variable(a) FROM test_table;
>  a..
> ----
>  10
> (1 row)
>
> ALTER TABLE test_table DROP COLUMN a;
> -- warning - variable fence is not used
> SELECT a, b FROM test_table;
> WARNING:  session variable "a" is not used inside variable fence
> LINE 1: SELECT a, b FROM test_table;
>                ^
> DETAIL:  The collision of session variable' names and column names is
> possible.
>  a  | b..
> ----+----
>  10 | 20
> (1 row)
>
> -- no warning
> SELECT variable(a), b FROM test_table;
>  a  | b..
> ----+----
>  10 | 20
> (1 row)
>
> DROP VARIABLE a;
> DROP TABLE test_table;
> SET session_variables_ambiguity_warning TO DEFAULT;
> SET session_variables_use_fence_warning_guard TO DEFAULT;
> SET search_path TO DEFAULT;
>
>
Last discussion is related to reducing the size of the session variable
patch set.

I have an idea to use variable's fencing more aggressively from the start,
and then we can reduce it in future. This should not break issues with
compatibility and doesn't need some like version flags.

The real problem of proposed session variables is possible collisions
between session variables identifiers and table or columns identifiers. I
designed some tools to minimize the risk of unwanted collisions, but these
tools increase the size of code and don't reduce the complexity of the
patch and tests. The proposed change probably doesn't reduce a lot of code,
but can reduce some tests, and mainly possible risk of some unwanted impact
- at the end it can be less work for reviewers and less stress for
committers - and the implementation can be divided to allone workable
following steps.

Step 1
=3D=3D=3D=3D=3D

So the main change is the hard requirement for usage variable's fence
everywhere where collisions are possible - and then in the first step, the
collisions will not be possible, and then we don't need it to solve, and we
don't need to test it.

CREATE VARIABLE public.foo AS int;
LET foo =3D 10;
SELECT VARIABLE(foo);

DO $$
BEGIN
  RAISE NOTICE '% %', VARIABLE(foo), VARIABLE(public.foo);
END;
$$;

Step 2
=3D=3D=3D=3D=3D
Necessity of usage variable fencing in PL/pgSQL can be a problem for
migration from PL/SQL. But this can be solved separately by using SPI
params hooks - similar to how PL/pgSQL works with PL/pgSQL variables. In
this step we can push optimization for fast execution of the LET statement
or optimization of usage variables in queries.

After this step will be possible:

DO $$
BEGIN
  RAISE NOTICE '% %', foo, VARIABLE(public.foo);
END;
$$;

SELECT VARIABLE(foo);

No other visible change in this step. WIth this step the people who do
migration form Oracle and PL/pgSQL developers will be very happy. They
don't need more. There can be collisions, but the collisions can be limited
just to PL/pgSQL scope, and we can use already implemented mechanisms.

Step 3
=3D=3D=3D=3D=3D
We can talk in future about less requirement of usage variable fencing in
queries. This needs to introduce some form of detection collisions and how
they should be solved (outside PL/pgSQL).
We can talk about other features like temporal, default values,
transactional, etc ...

This proposal doesn't reduce lines of code, but significantly reduces
possible impacts of introducing session variables to other parts of SQL.
Moreover, it allows us to separate some
 work and related discussion into separate blocks - any block can be
implemented in different major pg releases.

I think a lot of users will be very happy just with step 1 and step 2, and
anything else can be discussed in future.

Is this plan acceptable?

Regards

Pavel


> Regards
>
> Pavel
>

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

<div dir=3D"ltr"><div>Hi</div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">=C3=BAt 19. 11. 2024 v=C2=A02=
0:14 odes=C3=ADlatel Pavel Stehule &lt;<a href=3D"mailto:pavel.stehule@gmai=
l.com">pavel.stehule@gmail.com</a>&gt; napsal:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>Hi</div><div><br></d=
iv><div>I wrote POC of VARIABLE(varname) syntax support</div><div><br></div=
><div>here is a copy from regress test</div><div><br></div><div>SET session=
_variables_ambiguity_warning TO on;<br>SET session_variables_use_fence_warn=
ing_guard TO on;<br>SET search_path TO &#39;public&#39;;<br>CREATE VARIABLE=
 a AS int;<br>LET a =3D 10;<br>CREATE TABLE test_table(a int, b int);<br>IN=
SERT INTO test_table VALUES(20, 20);<br>-- no warning<br>SELECT a;<br>=C2=
=A0a..<br>----<br>=C2=A010<br>(1 row)<br><br>-- warning variable is shadowe=
d<br>SELECT a, b FROM test_table;<br>WARNING: =C2=A0session variable &quot;=
a&quot; is shadowed<br>LINE 1: SELECT a, b FROM test_table;<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^<br>DETAIL: =C2=A0Session var=
iables can be shadowed by columns, routine&#39;s variables and routine&#39;=
s arguments with the same name.<br>=C2=A0a =C2=A0| b..<br>----+----<br>=C2=
=A020 | 20<br>(1 row)<br><br>-- no warning<br>SELECT variable(a) FROM test_=
table;<br>=C2=A0a..<br>----<br>=C2=A010<br>(1 row)<br><br>ALTER TABLE test_=
table DROP COLUMN a;<br>-- warning - variable fence is not used<br>SELECT a=
, b FROM test_table;<br>WARNING: =C2=A0session variable &quot;a&quot; is no=
t used inside variable fence<br>LINE 1: SELECT a, b FROM test_table;<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^<br>DETAIL: =C2=A0The =
collision of session variable&#39; names and column names is possible.<br>=
=C2=A0a =C2=A0| b..<br>----+----<br>=C2=A010 | 20<br>(1 row)<br><br>-- no w=
arning<br>SELECT variable(a), b FROM test_table;<br>=C2=A0a =C2=A0| b..<br>=
----+----<br>=C2=A010 | 20<br>(1 row)<br><br>DROP VARIABLE a;<br>DROP TABLE=
 test_table;<br>SET session_variables_ambiguity_warning TO DEFAULT;<br>SET =
session_variables_use_fence_warning_guard TO DEFAULT;<br>SET search_path TO=
 DEFAULT;</div><div><br></div></div></blockquote><div><br></div><div>Last d=
iscussion is related to reducing the size of the session variable patch set=
.</div><div><br></div><div>I have an idea to use variable&#39;s fencing mor=
e aggressively from the start, and then we can reduce it in future. This sh=
ould not break issues with compatibility and doesn&#39;t need some like ver=
sion flags.</div><div><br></div><div>The real problem of proposed session v=
ariables is possible collisions between session variables identifiers and t=
able or columns identifiers. I designed some tools to minimize the risk of =
unwanted collisions, but these tools increase the size of code and don&#39;=
t reduce the complexity of the patch and tests. The proposed change probabl=
y doesn&#39;t reduce a lot of code, but can reduce some tests, and mainly p=
ossible risk of some unwanted impact - at the end it can be less work for r=
eviewers and less stress for committers - and the implementation can be div=
ided to allone workable following steps.</div><div><br></div><div>Step 1</d=
iv><div>=3D=3D=3D=3D=3D</div><div><br></div><div>So the main change is the =
hard requirement for usage variable&#39;s fence everywhere where collisions=
 are possible - and then in the first step, the collisions will not be poss=
ible, and then we don&#39;t need it to solve, and we don&#39;t need to test=
 it.</div><div><br></div><div>CREATE VARIABLE public.foo AS int;</div><div>=
LET foo =3D 10;</div><div>SELECT VARIABLE(foo);</div><div><br></div><div>DO=
 $$</div><div>BEGIN</div><div>=C2=A0 RAISE NOTICE &#39;% %&#39;, VARIABLE(f=
oo), VARIABLE(public.foo);</div><div>END;</div><div>$$;</div><div><br></div=
><div>Step 2</div><div>=3D=3D=3D=3D=3D</div><div>Necessity of usage variabl=
e fencing in PL/pgSQL can be a problem for migration from PL/SQL. But this =
can be solved separately by using SPI params hooks - similar to how PL/pgSQ=
L works with PL/pgSQL variables. In this step we can push optimization for =
fast execution of the LET statement or optimization of usage variables in q=
ueries.</div><div><br></div><div>After this step will be possible:</div><di=
v><br></div><div>DO $$</div><div>BEGIN</div><div>=C2=A0 RAISE NOTICE &#39;%=
 %&#39;, foo, VARIABLE(public.foo);</div><div>END;</div><div>$$;</div><div>=
<br></div><div>SELECT VARIABLE(foo);</div><div><br></div><div>No other visi=
ble change in this step. WIth this step the people who do migration form Or=
acle and PL/pgSQL developers will be very happy. They don&#39;t need more. =
There can be collisions, but the collisions can be limited just to PL/pgSQL=
 scope, and we can use already implemented mechanisms. <br></div><div><br><=
/div><div>Step 3</div><div>=3D=3D=3D=3D=3D</div><div>We can talk in future =
about less requirement of usage variable fencing in queries. This needs to =
introduce some form of detection collisions and how they should be solved (=
outside PL/pgSQL).</div><div>We can talk about other features like temporal=
, default values, transactional, etc ...</div><div><br></div><div>This prop=
osal doesn&#39;t reduce lines of code, but significantly reduces possible i=
mpacts of introducing session variables to other parts of SQL. Moreover, it=
 allows us to separate some</div>=C2=A0work and related discussion into sep=
arate blocks - any block can be implemented in different major pg releases.=
</div><div class=3D"gmail_quote gmail_quote_container"><br></div><div class=
=3D"gmail_quote gmail_quote_container">I think a lot of users will be very =
happy just with step 1 and step 2, and anything else can be discussed in fu=
ture.</div><div class=3D"gmail_quote gmail_quote_container"><br></div><div =
class=3D"gmail_quote gmail_quote_container">Is this plan acceptable?<div><b=
r></div><div>Regards</div><div><br></div><div>Pavel</div><div><br></div><di=
v><br></div><div><br></div><div>=C2=A0</div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex"><div dir=3D"ltr"><div>Regards</div><div><br></div><div>P=
avel<br></div></div>
</blockquote></div></div>

--0000000000007c6a550635a01a79--