Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBHxd-002uBk-2e for pgsql-hackers@arkaria.postgresql.org; Wed, 13 Nov 2024 18:19:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tBHxa-00G12K-3L for pgsql-hackers@arkaria.postgresql.org; Wed, 13 Nov 2024 18:19:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBHxZ-00G12C-GS for pgsql-hackers@lists.postgresql.org; Wed, 13 Nov 2024 18:19:22 +0000 Received: from mail-yb1-xb2f.google.com ([2607:f8b0:4864:20::b2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tBHxW-001hjn-NC for pgsql-hackers@lists.postgresql.org; Wed, 13 Nov 2024 18:19:20 +0000 Received: by mail-yb1-xb2f.google.com with SMTP id 3f1490d57ef6-e380e4825a9so92441276.3 for ; Wed, 13 Nov 2024 10:19:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731521958; x=1732126758; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=J2WK38BXc0rooKj4Bl/n5GWOvMBrCs1Zs0wfyBMzFy0=; b=eEkTAuCvZYv9zwK9/qOZtL1LiXCXj4Di0an/MFd61W1r2wicOUG7Ob1uSSzEMDtpxS ge9py1eACblGhaKpumCmNAIrFS21cHFe/bh5evMNdvo0JX4vSdfsODF9nhPRUa0hNXJw QpPNvOivPjWEe5kBS8SWHN5/km8Jx7KOtSaQ7pyjQyL5/m2MGvDYjizD0gxcIw1nvJb5 aJs8fYtuF6dExSPtqu9cSwUakgBbuhYdP60mFaHX2D4E1i1Iz/QfIAsRDN/hZ0RsClA8 Hs0A22EGLRnOVtBAPWkOl89OOrSerPmt9S3eU71zufTpzsQ52+0tz5+sxL4ZIUSR9V8I JV9g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731521958; x=1732126758; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=J2WK38BXc0rooKj4Bl/n5GWOvMBrCs1Zs0wfyBMzFy0=; b=k/+8dz65XTbcx5WezyVNjC23LFso9Af9ZMHelYxWLD6/f4eHG5/hGektlcENeYYZHd 86hAlTu4jXaB5AgYVrh479CVTko4TBhQU7z1ZyaTBl+WG7ReP0xKQjH8eHpH8JD3obgo Nx3H5NeH5kfmVREhv1ldNmx/094/kSTYuNfOrVyO4W94AiC9oTWt5/ccr6EXzgqhUhRj Jf9YelL5/TSBrvuMnbz4w/nLTgUMUrBde0gCG7pnJJ7Zhzy7zJ33S6XDHFhtlPtI+DsU c4KmJ/V8V0zJ3nApHjleaMI9V3BjaWDIiVt3SuahLIVU6aXO4wjrSKQyX/ve+UaWWWkW X5ZA== X-Forwarded-Encrypted: i=1; AJvYcCWMtEEgZc90OX/TTvZb4hjy2cc0elNlMvrgQ3YG7bk01xrwAoTv6u3ywhveckUPOwxOHRjmofqwerNGKXpP@lists.postgresql.org X-Gm-Message-State: AOJu0Yxq9dm+rGtPv6HzJmnKMGX/BpPetOCazBYNWCjgxpp6vGs/ibq/ f6rsSwNsGzc+b3xOtn8BL+q2arApjyGb8Pq9CHAdums3NjUmDkeMCrFXiYKiA/jE9UAnwhVE9gK lBA3V5JV4rAci3wZaVM3xL3zdNc5saoho X-Google-Smtp-Source: AGHT+IGL0YYKajjwsWSA9pEX4ZqeNIKhNRuIjxIYOb7L6aB5lH7rSWvEKiphI+ddXjC8TbUaRX0pts/HigCj5Q7FE3A= X-Received: by 2002:a05:690c:650f:b0:6ea:8a73:c0b with SMTP id 00721157ae682-6eaddd86cfcmr213604147b3.7.1731521957780; Wed, 13 Nov 2024 10:19:17 -0800 (PST) MIME-Version: 1.0 References: <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65> In-Reply-To: <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65> From: Pavel Stehule Date: Wed, 13 Nov 2024 19:18:40 +0100 Message-ID: Subject: Re: proposal: schema variables To: Dmitry Dolgov <9erthalion6@gmail.com> Cc: Laurenz Albe , Erik Rijkers , Michael Paquier , Amit Kapila , DUVAL REMI , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="00000000000036e4e70626cf5f66" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000036e4e70626cf5f66 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable st 13. 11. 2024 v 17:35 odes=C3=ADlatel Dmitry Dolgov <9erthalion6@gmail.co= m> napsal: > > On Sun, Nov 10, 2024 at 06:51:40PM GMT, Pavel Stehule wrote: > > ne 10. 11. 2024 v 17:19 odes=C3=ADlatel Pavel Stehule < > pavel.stehule@gmail.com> > > napsal: > > I thought a lot of time about better solutions for identifier collision= s > > and I really don't think so there is some consistent user friendly > syntax. > > Personally I think there is an easy already implemented solution - > > convention - just use a dedicated schema for variables and this schema > > should not be in the search path. Or use secondary convention - like > using > > prefix "__" for session variables. Common convention is using "_" for > > PLpgSQL variables. I searched how this issue is solved in other > databases, > > or in standard, and I found nothing special. The Oracle and SQL/PSM has= a > > concept of visibility - the variables are not visible outside packages = or > > modules, but Postgres has nothing similar. It can be emulated by a > > dedicated schema without inserting a search path, but it is less strong= . > > > > I think we can introduce an alternative syntax, that will not be user > > friendly or readable friendly, but it can be without collisions - or ca= n > > decrease possible risks. > > > > It is nothing new - SQL does it with old, "new" syntax of inner joins, = or > > in Postgres we can > > > > where salary < 40000 > > > > or > > > > where pg_catalog.int4lt(salary, 40000); > > > > > > or some like we use for operators OPERATOR(*schema*.*operatorname*) > > > > So introducing VARIABLE(schema.variablename) syntax as an alternative > > syntax for accessing variables I really like. I strongly prefer to use > this > > as only alternative (secondary) syntax, because I don't think it is > > friendly syntax or writing friendly, but it is safe, and I can imagine > > tools that can replace generic syntax to this special, or that detects > > generic syntax and shows some warning. Then users can choose what they > > prefer. Two syntaxes - generic and special can be good enough for all - > and > > this can be perfectly consistent with current Postgres. > > As far as I recall, last time this topic was discussed in hackers, two > options were proposed: the one with VARIABLE(name), what you mention > here; and another one with adding variables to the FROM clause. The > VARIABLE(...) syntax didn't get much negative feedback, so I guess why > not -- if you find it fitting, it would be interesting to see the > implementation. > > I'm afraid it should not be just an alternative syntax, but the only one > allowed, because otherwise I don't see how scenarious like "drop a > column with the same name" could be avoided. As in the previous thread: > > -- we've got a variable b at the same time > SELECT a, b FROM table1; > I am sorry, but I am in very strong opposition against this idea. Nobody did reply to my questions, that can change my opinion. 1. This introduces possible inconsistency between LET syntax and SELECT syntax. What will be the syntax of LET? LET var =3D var FROM var PLpgSQL does something, and it is really strange, and source of some unwanted bugs. See https://commitfest.postgresql.org/50/5044/ With current design I can support LET var =3D expr with wars or LET var =3D (QUERY with vars) It is perfectly consistent. The expressions will be expressions. 2. I don't know of any product in the world that introduced the same requirement. So this syntax will be proprietary (SQL/PSM it really doesn't require) and shock for any users that know other databases. Proprietary syntax in this area far from syntaxes of other products is hell. Try to explain to users the working with OUT variables of Postgres's procedures and functions. And there is some deeper logic. 3. There is a simple solution - convention. Use your own schema like vars, and use session variables in this schema, When this schema will not be on the search path, then there is not a collision. Variables living in schema. Nobody without CREATE right can create it. So it is safe. Or use prefix in __ for variables in the search path. 4. this requirement introduces syntax inconsistency between plpgsql variables and session variables - which breaks one goal of the patch - introducing some global variables for plpgsql (and for all PL). 5. Using more variables and FROM clauses decrease readability of FROM claus= e SELECT v1, v2, a, b, c FROM t1, v1, v2, t2, ... 6. Usually composite variables don't want to unpack. When you should use FROM clause, then composite variables will be unpacked. Then all fields can be possibly in collision with all other column name Example CREATE TYPE t1 AS (id int, name varchar) CREATE TABLE tab(id int, name varchar) CREATE VARIABLE var1 AS t1; SELECT id, name, foo(var1) FROM tab, var1; Now I have a collision in columns id, name, and everywhere I need to use aliases. Without necessity to use var in FROM clause, I can just write SELECT id, name, foo(var) FROM tab and there is not any risk of collision > Then dropping the column b, but everything still works beause the > variable b got silently picked up. But if it would be required to say > VARIABLE(b), then all fine. > but same risk you have any time in plpgsql - all time. I don't remember any bug report related to this issue. > > And to make sure we're on the same page, could you post couple of > examples from curretly existing tests in the patch, how are they going > to look like with this proposal? > > About adding variables to the FROM clause. Looks like this option was > quite popular, and you've mentioned some technical challenges > implementing that. If you'd like to go with another approach, it would > be great to elaborate on that -- maybe even with a PoC, to make a > convincing point here. > There is not any problem with implementation. I see the main problem with usability, and I really don't want to implement some like LET var =3D var FROM var; I am sorry It fixes one issue, but it increases possible collisions - so the variables will be unusable. Regards Pavel --00000000000036e4e70626cf5f66 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


st 13. 11. 2024 v=C2= =A017:35 odes=C3=ADlatel Dmitry Dolgov <9erthalion6@gmail.com> napsal:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">> On Sun, Nov 10, 2024 = at 06:51:40PM GMT, Pavel Stehule wrote:
> ne 10. 11. 2024 v 17:19 odes=C3=ADlatel Pavel Stehule <pavel.stehule@gmail.com>
> napsal:
> I thought a lot of time about better solutions for identifier collisio= ns
> and I really don't think so there is some consistent user friendly= syntax.
> Personally I think there is an easy already implemented solution -
> convention - just use a dedicated schema for variables and this schema=
> should not be in the search path. Or use secondary convention - like u= sing
> prefix "__" for session variables. Common convention is usin= g "_" for
> PLpgSQL variables. I searched how this issue is solved in other databa= ses,
> or in standard, and I found nothing special. The Oracle and SQL/PSM ha= s a
> concept of visibility - the variables are not visible outside packages= or
> modules, but Postgres has nothing similar. It can be emulated by a
> dedicated schema without inserting a search path, but it is less stron= g.
>
> I think we can introduce an alternative syntax, that will not be user<= br> > friendly or readable friendly, but it can be without collisions - or c= an
> decrease possible risks.
>
> It is nothing new - SQL does it with old, "new" syntax of in= ner joins, or
> in Postgres we can
>
> where salary < 40000
>
> or
>
> where pg_catalog.int4lt(salary, 40000);
>
>
> or some like we use for operators OPERATOR(*schema*.*operatorname*) >
> So introducing VARIABLE(schema.variablename) syntax as an alternative<= br> > syntax for accessing variables I really like. I strongly prefer to use= this
> as only alternative (secondary) syntax, because I don't think it i= s
> friendly syntax or writing friendly, but it is safe, and I can imagine=
> tools that can replace generic syntax to this special, or that detects=
> generic syntax and shows some warning. Then users can choose what they=
> prefer. Two syntaxes - generic and special can be good enough for all = - and
> this can be perfectly consistent with current Postgres.

As far as I recall, last time this topic was discussed in hackers, two
options were proposed: the one with VARIABLE(name), what you mention
here; and another one with adding variables to the FROM clause. The
VARIABLE(...) syntax didn't get much negative feedback, so I guess why<= br> not -- if you find it fitting, it would be interesting to see the
implementation.

I'm afraid it should not be just an alternative syntax, but the only on= e
allowed, because otherwise I don't see how scenarious like "drop a=
column with the same name" could be avoided. As in the previous thread= :

=C2=A0 =C2=A0 -- we've got a variable b at the same time
=C2=A0 =C2=A0 SELECT a, b FROM table1;

= I am sorry, but I am in very strong opposition=C2=A0 against this idea. Nob= ody did reply to my questions, that can change my opinion.

1. This introduces possible inconsistency between LET syntax and SELECT = syntax.



With current desi= gn I can support

LET var =3D expr with wars

or

LET var =3D (QUERY= with vars)

It is perfectly consistent. The expres= sions will be expressions.

2. I don't know= of any product in the world that introduced the same requirement. So this = syntax will be proprietary (SQL/PSM it really doesn't require) and shoc= k for any users that know other databases. Proprietary syntax in this area = far from syntaxes of other products is hell. Try to explain to users the wo= rking with OUT variables of Postgres's procedures and functions. And th= ere is some deeper logic.

3. There is a simpl= e solution - convention. Use your own schema like vars, and use session var= iables in this schema, When this schema will not be on the search path, the= n there is not a collision.
Variables living in schema. Nobody wi= thout CREATE right can create it. So it is safe. Or use prefix in __ for va= riables in the search path.

4. this requiremen= t introduces syntax inconsistency between plpgsql variables and session var= iables - which breaks one goal of the patch - introducing some global varia= bles for plpgsql (and for all PL).

5. Using more v= ariables and FROM clauses decrease readability of FROM clause

SELECT v1, v2, a, b, c FROM t1, v1, v2, t2, ...

6. Usually composite variables don't want to unpack. = When you should use FROM clause, then composite variables will be unpacked.= Then all fields can be possibly in collision with all other column name

Example

CREATE TYPE t1 AS (= id int, name varchar)
CREATE TABLE tab(id int, name varchar)
CREATE VARIABLE var1 AS t1;

SELECT id, n= ame, foo(var1) FROM tab, var1;

Now I have a collis= ion in columns id, name, and everywhere I need to use aliases. Without nece= ssity to use var in FROM clause, I can just write

= SELECT id, name, foo(var) FROM tab

and there is no= t any risk of collision




Then dropping the column b, but everything still works beause the
variable b got silently picked up. But if it would be required to say
VARIABLE(b), then all fine.

but same ri= sk you have any time in plpgsql - all time. I don't remember any bug re= port related to this issue.=C2=A0
=C2=A0

And to make sure we're on the same page, could you post couple of
examples from curretly existing tests in the patch, how are they going
to look like with this proposal?

About adding variables to the FROM clause. Looks like this option was
quite popular, and you've mentioned some technical challenges
implementing that. If you'd like to go with another approach, it would<= br> be great to elaborate on that -- maybe even with a PoC, to make a
convincing point here.

There is not any= problem with implementation. I see the main problem with usability, and I = really don't want to implement some like LET var =3D var FROM var; I am= sorry
It fixes one issue, but it increases possible collisions -= so the variables will be unusable.

Regards

Pavel

--00000000000036e4e70626cf5f66--