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 1tCC1d-00DXns-Oo for pgsql-hackers@arkaria.postgresql.org; Sat, 16 Nov 2024 06:11:17 +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 1tCC1Z-00DINI-BW for pgsql-hackers@arkaria.postgresql.org; Sat, 16 Nov 2024 06:11:13 +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 1tCC1Y-00DIN9-TM for pgsql-hackers@lists.postgresql.org; Sat, 16 Nov 2024 06:11:13 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tCC1W-0029PO-6S for pgsql-hackers@lists.postgresql.org; Sat, 16 Nov 2024 06:11:12 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-6ea07d119b7so21463697b3.0 for ; Fri, 15 Nov 2024 22:11:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731737469; x=1732342269; 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=yOb0Guw2IUuZWew0l4OR1L1eAzixXj3E0McH3el1YWE=; b=CAz+1piAqALChkaiCBrVv/IRZCgWWXl4swgWIaGgGCXR0Asy48SqNbaS7d1B6zHRWc CClo5T82D8wG/PNm/5w1fbao45jAlOj0LKuuIn1E9tcVNuUWTUqJtpio3i2YzJZcrR8O 9XhrrjcdkqL2tiCUUx2iImsDrwO4KmwvEDlRj+4wpyXZZ2UfkJrkIzXadPWOL03Zfjvp cM9WM1LpZNc3ph6BSmKsfS2pvC5aydnd++q/N5Z+pomi75GQLVO494QqV5MvxS6QJIeH HTtn7GXtq8PZ34RHYOc2dqX+AvnDCwZkmufaQTJ+JQEXSEOS0kdC/i8ySzCeUjnbvCKh 9tAQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731737469; x=1732342269; 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=yOb0Guw2IUuZWew0l4OR1L1eAzixXj3E0McH3el1YWE=; b=Ox+0j9OnT0EBAenfkxinFhTrlFOCPD4yBsWYv4SpIo/aUQJr/b11SPD096gHATom9B MqIwdcb8Uaie+gl8Pn0IYKZmq7dwjAI3coJ4q+pddWq/fOZqwrBJPTB6RDqwPGnIawDY IjJAEp2U+ldiZESCcLs32mt/Z7TEXoI5RmCjMecRMDq2TeCxnxSeyyoK09yCZYmfFDU7 n7nXWTtIR4c5lHH5PT8wP7LpKx9R+XYthqIRvXg97MU2eY7Wj+0g2MeJCXgQRS8w0dm/ 3SQmfemhFO1AhSH8CJKIQo6w6a/ewRIOQalreFmFjtxv0v0pceEToweZDMwL9LFS8qPz 4AmQ== X-Forwarded-Encrypted: i=1; AJvYcCUBSRLcvq2tUftVuxbeCjpqcGxYE2sRWK0x0T8aW+FB5c5KJ0FBKGUNCPS82mkMfArdWlgBnU/FLP68gEt0@lists.postgresql.org X-Gm-Message-State: AOJu0YxRSdyBt2qRuQ6XWmLHI5pXhXogCbP6nfrI2yaeyB5ddFiRjuu1 3a7ujQ9AV83hjBE6zDKpSyqbjg3Cye/w0R1qxAXPAK14hEX7Lqu7lFJsW4LPmLDRBPNyC53gyC0 x/cvPREeiEidrVgbLuFHSQI8wnWQ= X-Google-Smtp-Source: AGHT+IEa5zeLHnfrVUwx+l2jOvgNz6RAipVgF4TBhfspjQ2UcdO9B53/8MxOHhptUVI0bgrY+9EeEeDrg/PnC1qQu3I= X-Received: by 2002:a05:690c:6893:b0:6ee:3f37:919c with SMTP id 00721157ae682-6ee55bfa9b2mr66239517b3.14.1731737469370; Fri, 15 Nov 2024 22:11:09 -0800 (PST) MIME-Version: 1.0 References: <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65> In-Reply-To: <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65> From: Pavel Stehule Date: Sat, 16 Nov 2024 07:10:31 +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="000000000000b4c7830627018cb8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b4c7830627018cb8 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; > > 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. > > 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? > What do you think about the following design? I can implement a warning "variable_usage_guard" when the variable is accessed without using VARIABLE() syntax. We can discuss later if this warning can be enabled by default or not. There I am open to any variant. So for variable public.a and table public.foo(a, b) I can write LET a =3D 10; -- there is not possible collision LET a =3D a + 1; -- there is not possible collision, no warning SELECT a, b FROM foo; -- there is a collision - and warning "variable a is shadowed" SELECT VARIABLE(a), b FROM foo; -- no collision, no warning After ALTER TABLE foo DROP COLUMN a; SELECT a, b FROM foo; -- possible warning "the usage in variable without safe syntax", SELECT VARIABLE(a), b FROM foo; -- no warning I think this design can be good for all. variable_usage_guard can be enabled by default. If somebody uses conventions for collision protection, then he can safely disable it. Comments, notes? Regards Pavel > 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. > --000000000000b4c7830627018cb8 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 <9erthal= ion6@gmail.com> napsal:
> On Sun, Nov 10, 2024 at 06:51:40PM GMT, Pavel Stehule w= rote:
> 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;

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.

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?



I can write


SELECT VARIABLE(a), b FROM foo; -- no collision, no warning





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.
--000000000000b4c7830627018cb8--