MIME-Version: 1.0
References: 
 <CAFj8pRC_2tHski1K4hUMZm0+T4y7na4d8hwgMMF7Y0MXhfUhbg@mail.gmail.com>
 <CAB-JLwaOdMQN838Fy2r2qZLTExfJp0zcjQjmOFVRXhW_-uDNVw@mail.gmail.com>
 <CAFj8pRArtw+Qqzk+Zf2NxjgO=paCBdc54eEma+MsVwtx3j7Atg@mail.gmail.com>
 <CAFj8pRAK_8ym1J=SAdin-vtL0PAachXgjWgU3QrhzinRdBX2sw@mail.gmail.com>
 <CAFj8pRAx-Gb9Ux825cxGNm38qQUOrij1GY6erPRaQ=cpqfiX7g@mail.gmail.com>
 <CAFj8pRCEjG005L=1vGbir-odYdcYxByOAeFVJ_K9eRcRF=MbNQ@mail.gmail.com>
 <aCyYHXhAjXOUPvLO@momjian.us>
 <CAB-JLwY2-Hm_SYbj2td_VKuU8awCwgDr-sOtjHRE2C-Sgg_pFg@mail.gmail.com>
 <aCywNzDx2J3jrY1-@momjian.us>
 <CAFj8pRABfnPMJCzqLKp11q-FhpdteTcAG8Bqm6SOoe-RA2uxcA@mail.gmail.com>
 <aC0clv2F-HR1vVvP@paquier.xyz>
In-Reply-To: <aC0clv2F-HR1vVvP@paquier.xyz>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Wed, 21 May 2025 07:49:34 +0200
Message-ID: 
 <CAFj8pRChvU6-ANJD_G=3+zfou-ADu05oxfCnbXFgtQhyWMggwg@mail.gmail.com>
Subject: Re: Re: proposal: schema variables
To: Michael Paquier <michael@paquier.xyz>,
 Peter Eisentraut <peter@eisentraut.org>
Cc: Bruce Momjian <bruce@momjian.us>, Marcos Pegoraro <marcos@f10.com.br>,
	jian he <jian.universality@gmail.com>, Dmitry Dolgov <9erthalion6@gmail.com>,
	Laurenz Albe <laurenz.albe@cybertec.at>, Erik Rijkers <er@xs4all.nl>,
	Amit Kapila <amit.kapila16@gmail.com>, DUVAL REMI <REMI.DUVAL@cheops.fr>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004fc11906359ef0fc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRChvU6-ANJD_G%3D3%2Bzfou-ADu05oxfCnbXFgtQhyWMggwg%40mail.gmail.com>
Precedence: bulk

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

st 21. 5. 2025 v 2:21 odes=C3=ADlatel Michael Paquier <michael@paquier.xyz>
napsal:

> On Tue, May 20, 2025 at 10:28:31PM +0200, Pavel Stehule wrote:
> > This topic is difficult, because there is no common solution. SQL/PSM i=
s
> > almost dead. T-SQL (and MySQL) design is weak and cannot be used for
> > security.
> > Oracle's design is joined with just one environment. And although almos=
t
> > all widely used databases have supported session variables for decades,
> no
> > one design
> > is perfect. Proposed design is not perfect too (it introduces possible
> > ambiguity) , but I think it can support most wanted use cases (can be
> > enhanced in future),
> > and it is consistent with Postgres. There are more ways to reduce risk =
of
> > unwanted ambiguity to zero. But it increases the size of the patch.
>
> One thing that I keep hearing about this feature is that this would be
> really helpful for migration from Oracle to PostgreSQL, helping a lot
> with rewrites of pl/pgsql functions.
>
> There is one page on the wiki about private variables, dating back to
> 2016:
> https://wiki.postgresql.org/wiki/CREATE_PRIVATE_VARIABLE
>
>
I wrote mail

https://www.postgresql.org/message-id/CAFj8pRB8kdWQCdN2X1_63c58+07Oy4Z+ruDK=
_xPTUP+Pe8R2Pw@mail.gmail.com

and there is another wiki page
https://wiki.postgresql.org/wiki/Variable_Design


> Perhaps it would help to summarize a bit the pros and cons of existing
> implementations to drive which implementation would be the most suited
> on a wiki page?  The way standards are defined is by overwriting
> existing standards, and we don't have one in the SQL specification.
> It's hard to say if there will be one at some point, but if the main
> SQL products around the world have one, it pretty much is a point in
> favor of having a standard.
>

Although it is  maybe a peccant idea - I can imagine two different
implementations of server side session variables with different syntaxes
(and different advantages and disadvantages, and different use cases). The
implementations are not going against, but we should to accept
fact, so one feature is implemented twice. We should choose just one, that
will be implemented first. Proposed helps with migration from
PL/SQL.


>
> Another possible angle that could be taken is to invest in a proposal
> for the SQL committee to consider, forcing an actual standard that
> PostgreSQL could rely on rather than having one behavior implemented
> to have it standard-incompatible a few years after.  And luckily, we
> have Vik Fearing and Peter Eisentraut in this community who invest
> time and resources in this area.
>

Theoretically the proposed design is a subset of implementation from DB2 -
I designed it without knowledge of this DB2 feature. But without
introduction of concept of modules (that is partially redundant to
schemas), this design is very natural and I am very sure, so there is not
another way, how to design it. We can ask Peter or Vik about real
possibilities in this area. I have not any information from this area, just
I haven't seen the changes in SQL/PSM for decades, so I didn't think  about
it.


> FWIW, I do agree with the opinion that if you want to propose rebased
> versions of this patch set on a periodic basis, you are free to do so.
> This is the core concept of an open community.  In terms of my
> committer time, I'm pretty much already booked in terms of features
> planned for the next release, so I guess that I won't be able to
> invest time on this thread.  Just saying.
>

thank you for an info


> I know that this patch set has been discussed at FOSDEM at some point,
> so others may be able to comment more about that.  That's just one
> opinion among many others.
> --
> Michael
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">st 21. 5. 2025 =
v=C2=A02:21 odes=C3=ADlatel Michael Paquier &lt;<a href=3D"mailto:michael@p=
aquier.xyz">michael@paquier.xyz</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">On Tue, May 20, 2025 at 10:28:31PM +0200, =
Pavel Stehule wrote:<br>
&gt; This topic is difficult, because there is no common solution. SQL/PSM =
is<br>
&gt; almost dead. T-SQL (and MySQL) design is weak and cannot be used for<b=
r>
&gt; security.<br>
&gt; Oracle&#39;s design is joined with just one environment. And although =
almost<br>
&gt; all widely used databases have supported session variables for decades=
, no<br>
&gt; one design<br>
&gt; is perfect. Proposed design is not perfect too (it introduces possible=
<br>
&gt; ambiguity) , but I think it can support most wanted use cases (can be<=
br>
&gt; enhanced in future),<br>
&gt; and it is consistent with Postgres. There are more ways to reduce risk=
 of<br>
&gt; unwanted ambiguity to zero. But it increases the size of the patch.<br=
>
<br>
One thing that I keep hearing about this feature is that this would be<br>
really helpful for migration from Oracle to PostgreSQL, helping a lot<br>
with rewrites of pl/pgsql functions.<br>
<br>
There is one page on the wiki about private variables, dating back to<br>
2016:<br>
<a href=3D"https://wiki.postgresql.org/wiki/CREATE_PRIVATE_VARIABLE" rel=3D=
"noreferrer" target=3D"_blank">https://wiki.postgresql.org/wiki/CREATE_PRIV=
ATE_VARIABLE</a><br>
<br></blockquote><div><br></div><div>I wrote mail=C2=A0</div><div><br></div=
><div><a href=3D"https://www.postgresql.org/message-id/CAFj8pRB8kdWQCdN2X1_=
63c58+07Oy4Z+ruDK_xPTUP+Pe8R2Pw@mail.gmail.com">https://www.postgresql.org/=
message-id/CAFj8pRB8kdWQCdN2X1_63c58+07Oy4Z+ruDK_xPTUP+Pe8R2Pw@mail.gmail.c=
om</a></div><div><br></div><div>and there is another wiki page <a href=3D"h=
ttps://wiki.postgresql.org/wiki/Variable_Design">https://wiki.postgresql.or=
g/wiki/Variable_Design</a></div><div><br></div><div>=C2=A0<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex">
Perhaps it would help to summarize a bit the pros and cons of existing<br>
implementations to drive which implementation would be the most suited<br>
on a wiki page?=C2=A0 The way standards are defined is by overwriting<br>
existing standards, and we don&#39;t have one in the SQL specification.<br>
It&#39;s hard to say if there will be one at some point, but if the main<br=
>
SQL products around the world have one, it pretty much is a point in<br>
favor of having a standard.<br></blockquote><div><br></div><div>Although it=
 is=C2=A0 maybe a peccant idea - I can imagine two different implementation=
s of server side session variables with different syntaxes</div><div>(and d=
ifferent advantages and disadvantages, and different use cases). The implem=
entations are not going against, but we should to accept</div><div>fact, so=
 one feature is implemented twice. We should choose just one, that will be =
implemented first. Proposed helps with migration from</div><div>PL/SQL. <br=
></div><div>=C2=A0<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x">
<br>
Another possible angle that could be taken is to invest in a proposal<br>
for the SQL committee to consider, forcing an actual standard that<br>
PostgreSQL could rely on rather than having one behavior implemented<br>
to have it standard-incompatible a few years after.=C2=A0 And luckily, we<b=
r>
have Vik Fearing and Peter Eisentraut in this community who invest<br>
time and resources in this area.<br></blockquote><div><br></div><div>Theore=
tically the proposed design is a subset of implementation from DB2 - I desi=
gned it without knowledge of this DB2 feature. But without</div><div>introd=
uction of concept of modules (that is partially redundant to schemas), this=
 design is very natural and I am very sure, so there is not</div><div>anoth=
er way, how to design it. We can ask Peter or Vik about real possibilities =
in this area. I have not any information from this area, just</div><div>I h=
aven&#39;t seen the changes in SQL/PSM for decades, so I didn&#39;t think=
=C2=A0 about it.</div><div><br></div><div><br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex">
<br>
FWIW, I do agree with the opinion that if you want to propose rebased<br>
versions of this patch set on a periodic basis, you are free to do so.<br>
This is the core concept of an open community.=C2=A0 In terms of my<br>
committer time, I&#39;m pretty much already booked in terms of features<br>
planned for the next release, so I guess that I won&#39;t be able to<br>
invest time on this thread.=C2=A0 Just saying.<br></blockquote><div><br></d=
iv><div>thank you for an info <br></div><div><br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">
<br>
I know that this patch set has been discussed at FOSDEM at some point,<br>
so others may be able to comment more about that.=C2=A0 That&#39;s just one=
<br>
opinion among many others.<br>
--<br>
Michael<br>
</blockquote></div></div>

--0000000000004fc11906359ef0fc--