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 1tBoEF-006FyR-N1 for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Nov 2024 04:46:43 +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 1tBoED-007taY-3p for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Nov 2024 04:46:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBoEC-007taQ-Ni for pgsql-hackers@lists.postgresql.org; Fri, 15 Nov 2024 04:46:41 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tBoE9-0020pJ-9h for pgsql-hackers@lists.postgresql.org; Fri, 15 Nov 2024 04:46:40 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-6ea339a41f1so2614737b3.2 for ; Thu, 14 Nov 2024 20:46:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731645996; x=1732250796; 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=8dWad0TPDfbz/MxpsuSv7uxgpLLzsjYGHnSgQy0nUU0=; b=lSSpTRLTDBJGN5m88h+1coFZ15Mvuk7vy75FubdksUi5xsAaDSwRVUYftNYSaPWhPZ 8yJD3kzfimtUIKd/yB7O/sF0AaEU7/fIdJGfp/0Qki6OCtR+821ehqONVTita7c8ChCx 7VYhlD1y7NKpAMWSdYuo1uxGhK8vOAwimDxZqt+d4Ppg5fq4iu/8ehFoiMCBSkTDnbS3 a++/PWc3vhLMHNOurLKtDdCbTbMVSW5H5tWf98gTd0Jqxa/gnpriuTJT73rFD8DF7rpv 6XgGEyjg0VorNyUSRLSGvNnzcZXXexj4nDgFoQthTKAZd26bV+oG1RZx7xi1RbTs7E1Q 7tEQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731645996; x=1732250796; 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=8dWad0TPDfbz/MxpsuSv7uxgpLLzsjYGHnSgQy0nUU0=; b=iXtxEsZrGmIeNo17f2vLrKifMVLGx9yss+IvnafBqcVhZHgp6nAUrjLnVA001IECsk Dc7VQXwKFNp1RqOuU+7HyINMP2qKM96jbM7qJ2rVfWS0qlp2iXuOCWBSeei/JF8pTawU JTo1dBYMDxWjZ31qdb/7rY7J7ZO5PsXiprWFzWx9xrrswNDzi8JOJRhroW+QQRMasmQ4 KJf/V7zl36roqnZ2tF9QdSzM9OqUfZrBqUFisNUxNlcnfs2zyksZYhgineNKZA86SKG2 0UZUByOLH5AgqGYS5ZFbKgveHQJlvThGLhmPRg+DNW4bi9lqXOHhaOTKkQPyipmf29md cxTQ== X-Forwarded-Encrypted: i=1; AJvYcCUfoT1Cs/TwBjgYULu7a8ZfqUhyJVxTFHF9XPr4ppvlH1AgWN7lVg/Qtpv8rgKZfqNWQuRvq+gawfF1Prg4@lists.postgresql.org X-Gm-Message-State: AOJu0YxsJrfo0CRb/d31d744w8w2HJlU0UX6f5RQrSWPSsQPrnCMh6Sb uSlXUg3hksqPeV21Wt5eQ/Kh2E5hwlhvBRe7hfVrblW3DHzo+TrRNnyZrv667N2mHZ4YXOGtRIK m9twdwNY7Ok6jrqSxWLV3DAaKNQU= X-Google-Smtp-Source: AGHT+IHfr+dG0Y/ohSvVHCXpQPayFWGlOK5eaQf17W+PijQUeg6PThRqCSa0FkI/5v86YWcogJnAoPSlQGqqbJumOE4= X-Received: by 2002:a05:690c:6e10:b0:6e7:d974:8cee with SMTP id 00721157ae682-6ee55a3bb6dmr17330777b3.7.1731645996555; Thu, 14 Nov 2024 20:46:36 -0800 (PST) MIME-Version: 1.0 References: <3chredgnjcmccym2kczawfih226b4ac6co7p6z4jeofevrcosi@mrsxkx2x2c65> In-Reply-To: From: Pavel Stehule Date: Fri, 15 Nov 2024 05:45:58 +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="00000000000080638f0626ec40ef" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000080638f0626ec40ef Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =C4=8Dt 14. 11. 2024 v 8:41 odes=C3=ADlatel Pavel Stehule napsal: > > > st 13. 11. 2024 v 17:35 odes=C3=ADlatel Dmitry Dolgov <9erthalion6@gmail.= com> > 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 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 >> 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 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 >> > 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 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. >> > > In this scenario you will get a warning related to variable shadowing > (before you drop a column). > > I think this issue can be partially similar to creating two equally named > tables in different schemas (both schemas are in search path). When you > drop one table, the query will work, but the result is different. It is t= he > same issue. The SQL has no concept of shadowing and on the base line it i= s > not necessary. But when you integrate SQL with some procedural code then > you should solve this issue (or accept). This issue is real, and it is in > every procedural enhancement of SQL that I know with the same syntax. On > the other hand I doubt this is a real issue. The changes of system > catalogue are tested before production - so probably you will read a > warning about a shadowed variable, and probably you will get different > results, because variable b has the same value for all rows, and probably > will have different value than column b. I can imagine the necessity of > disabling this warning on production systems. Shadowing by self is not an > issue, probably, but it is a signal of code quality problems. > > But this scenario is real, and then it is a question if the warning about > shadowed variables should be only optional and if it can be disabled. May= be > not. Generally the shadowing is a strange concept - it is safeguard again= st > serious issues, but it should not be used generally and everywhere the > developer should rename the conflict identifiers. > There can be another example against usage of the FROM clause for variables. Because it solves just one special case, but others are not covered. Theoretically, variables can have the same names as tables. The table overshadows the variable, so it can work. But when somebody drops the variable, then the query still can work. So requirement of usage variable in FROM clause protects us just against drop column, but not against dropping table. In Postgres the dropping table is possibly risky due search_path (that introduces shadowing concept) without introduction variables. There is a possibility of this issue, but how common is this issue? Regards Pavel > > Regards > > Pavel > > >> 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. >> > --00000000000080638f0626ec40ef Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
=C4=8Dt 14. 11. 2024 v=C2=A08:41 odes= =C3=ADlatel Pavel Stehule <pa= vel.stehule@gmail.com> napsal:


st 13. 11. 2024= v=C2=A017:35 odes=C3=ADlatel Dmitry Dolgov <9erthalion6@gmail.com> 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 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.

In this scenario you will get=C2=A0 a warning related= to variable shadowing (before you drop a column).

I think this issue can be partially similar to creating two equally named = tables in different schemas (both schemas are in search path). When you dro= p one table, the query will work, but the result is different. It is the sa= me issue. The SQL has no concept of shadowing and on the base line it is no= t necessary. But when you integrate SQL with some procedural code then you = should solve this issue (or accept). This issue is real, and it is in every= procedural enhancement of SQL that I know with the same syntax.=C2=A0 On t= he other hand I doubt this is a real issue. The changes of system catalogue= are tested before production - so probably you will read a warning about a= shadowed variable, and probably you will get different results, because va= riable b has the same value for all rows, and probably will have different = value than column b. I can imagine the necessity of disabling this warning = on production systems. Shadowing by self is not an issue, probably, but it = is a signal of code quality problems.

But this= scenario is real, and then it is a question if the warning about shadowed = variables should be only optional and if it can be disabled. Maybe not. Gen= erally the shadowing is a strange concept - it is safeguard against serious= issues, but it should not be used generally and everywhere the developer s= hould rename the conflict identifiers.




Pavel

=C2=A0
Regards

Pavel


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.
--00000000000080638f0626ec40ef--