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 1sWhHJ-00GnBP-QW for pgsql-hackers@arkaria.postgresql.org; Wed, 24 Jul 2024 19:03:57 +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 1sWhHI-007pdh-90 for pgsql-hackers@arkaria.postgresql.org; Wed, 24 Jul 2024 19:03:56 +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 1sWhHH-007pdZ-R6 for pgsql-hackers@lists.postgresql.org; Wed, 24 Jul 2024 19:03:55 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sWhHF-001FUJ-0A for pgsql-hackers@lists.postgresql.org; Wed, 24 Jul 2024 19:03:54 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-663dd13c0bbso1180617b3.1 for ; Wed, 24 Jul 2024 12:03:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721847832; x=1722452632; 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=5gwE5mVGfTx9pArpM1gbaspgSXjgu0j2eabDdoItN1E=; b=DrWOGu3FwDkTUSRrrNPcbmpTdjoHtFL6zQB5+pdox1Si80uwV768/LGyFn3d4hk2gA 8nRXKcKHsGLUHSK87fQbyeM9w8W9Dh3U/xtV25L8cRVQRjgLs9ESZmYJaEYMwXa+Ydld JOwJWJQqq7mvTRzCHz55/NAXDMf649ROoAbr5ZVQfD8acCZ/ZYb16HTIb4ixZ5QPkBgg 2m9xS07Sjta1/k6dpyuxVi2J2khP6cVKjtKAcaQYGPX3NybRQgeD9Qwu8GmhrPIplRg+ ukl4GgLKc6bZDW4KwZnEBSejwuVsrkBHJ1CmsGYdcMJKhVJUEriLOhzbiId2TPXpYRWc 8tMQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721847832; x=1722452632; 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=5gwE5mVGfTx9pArpM1gbaspgSXjgu0j2eabDdoItN1E=; b=IoXaOwDfdVcdJUuu58IcLcg54chCIpR9dnxe7NCE1eMWvcC7hHBJ7DDN0sE1IouyEX V5D+h8eteDK5YcTNk4SJcuY82CV92b4Y9JTqA0IaAMLF/alRTRKbRnTxRe4KJdqTIx26 ZDQOGyN6O6Xg1BpglOEN4GxJ9AyvkegoCbl5M7kjhOqe7IfbYEX618YGh8zIfFWBuu1z j0t5W8f+x+NZLASvMaTV5JeObGivELtgJT2Sd19MbZonAVte4IfwN2Od/38XcMumNGFH b2CBiR6zvwt3gzUm7Go2Qpq5npsBKfSRO5a8GtQFnUbqCIw15OQJUEfi4H4hABXPdetP benw== X-Forwarded-Encrypted: i=1; AJvYcCVBFGrONH/9uzaYxf6rNxuOydBG2RRiNnNb/Lp0EUfRKKwIxumrsRoptve30HBhsKQkDAdGKTqSGaMOJCjLN7BnD67VDwqLKP93QUvklzZydzGX X-Gm-Message-State: AOJu0YxSUboPGmQ80+LNTjlCEchtlsNm2O11E5uF/sLlmg1KExJDBXoG 2FnJQUmbmBczWYOHOVYAdsnebO55+/Dz1bZ6CTHHRVIEEVscqcf6kfcnvWPgTb520Fda/baan3w vfAP973webLh67ZxSDiUxd3VZ3co= X-Google-Smtp-Source: AGHT+IEiyrpjhkQpwKW+75wAju3F35VjMDY91B0q67tz61mWL4Nu2nmHeyolSz2eCwSdtoatq5oTD9jCO4BILxnz+54= X-Received: by 2002:a05:690c:f08:b0:65f:8973:31a2 with SMTP id 00721157ae682-675114a7c3fmr4619487b3.13.1721847832235; Wed, 24 Jul 2024 12:03:52 -0700 (PDT) MIME-Version: 1.0 References: <20200924035637.GF28585@paquier.xyz> <20201001033824.GC8130@paquier.xyz> <51a9a68e8a998d04df17417d45c1dbd4@xs4all.nl> <89817942c99da01cd5e7850fe418436b@xs4all.nl> <56ca532c37eb0b540961f74a7bd5db39@xs4all.nl> <8181bd3abc647bdae5a4f78e71e62478a98c75f4.camel@cybertec.at> <9e67d49deb18270eddb95e602c83f02b98459843.camel@cybertec.at> <4165c66e9057c34423a0f91d1558165738ba31e2.camel@cybertec.at> In-Reply-To: <4165c66e9057c34423a0f91d1558165738ba31e2.camel@cybertec.at> From: Pavel Stehule Date: Wed, 24 Jul 2024 21:03:15 +0200 Message-ID: Subject: Re: proposal: schema variables To: Laurenz Albe Cc: Erik Rijkers , Michael Paquier , Zhihong Yu , Amit Kapila , DUVAL REMI , PostgreSQL Hackers Content-Type: multipart/alternative; boundary="00000000000065d75b061e02f0c9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000065d75b061e02f0c9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =C3=BAt 23. 7. 2024 v 23:41 odes=C3=ADlatel Laurenz Albe napsal: > On Tue, 2024-07-23 at 16:34 +0200, Laurenz Albe wrote: > > CREATE VARIABLE command: > > > > This is buggy: > > > > CREATE VARIABLE str AS text NOT NULL DEFAULT NULL; > > > > Ugh. > > > > SELECT str; > > ERROR: null value is not allowed for NOT NULL session variable > "laurenz.str" > > DETAIL: The result of DEFAULT expression is NULL. > > > > Perhaps that is a leftover from the previous coding, but I think ther= e > need be > > no check upon SELECT. It should be enough to check during CREATE > VARIABLE and > > LET. > > I'm having second thoughts about that. > > I was thinking of a variable like of a table column, but there is a > fundamental > difference: there is a clear moment when a tuple is added (INSERT or > UPDATE), > which is the point where a column can be checked for NULL values. > > A variable can be SELECTed without having been LET before, in which case = it > has the default value. But there is no way to test the default value > before > the variable is SELECTed. So while DEFAULT NULL for a non-nullable > variable > seems weird, it is no worse than DEFAULT somefunc() for a function that > returns > NULL. > > So perhaps the behavior I complained about above is actually the right on= e. > In the view of that, it doesn't seem necessary to enforce a DEFAULT value > for > a NOT NULL variable: NOT NULL might just as well mean "you have to LET it > before > you can SELECT it". > exactly > > > IMMUTABLE variables: > > > > + > > + IMMUTABLE > > + > > + > > + The assigned value of the session variable can not be change= d. > > + Only if the session variable doesn't have a default value, a > single > > + initialization is allowed using the LET > command. Once > > + done, no further change is allowed until end of transaction > > + if the session variable was created with clause ON > TRANSACTION > > + END RESET, or until reset of all session variables > by > > + DISCARD VARIABLES, or until reset of all > session > > + objects by command DISCARD ALL. > > + > > + > > + > > > > I can see the usefulness of IMMUTABLE variables, but I am surprised > that > > they are reset by DISCARD. What is the use case you have in mind? > > The use case I can envision is an application that sets a value right > after > > authentication, for use with row-level security. But then it would b= e > harmful > > if the user could reset the variable with DISCARD. > > I'm beginning to be uncertain about that as well. You might want to use = a > connection pool, and you LET the variable when you take it out of the poo= l. > When the session is returned to the pool, variables get DISCARDed. > > Sure, a user can call DISCARD, but only if he or she is in an interactive > session. > > So perhaps it is good as it is. > I think this design should work. There are a lot of scenarios, where session variables can be used well, and sure, there will be scenarios where it doesn't work well, but now, I think it is a good balance between usability, complexity and code complexity. There are a lot of lines, but the code is almost very simple. Regards Pavel > > Yours, > Laurenz Albe > --00000000000065d75b061e02f0c9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
=C3=BAt 23. 7. 2024 v=C2=A023:41 odes= =C3=ADlatel Laurenz Albe <la= urenz.albe@cybertec.at> napsal:
On Tue, 2024-07-23 at 16:34 +0200, Laurenz Albe wrot= e:
> CREATE VARIABLE command:
>
>=C2=A0 =C2=A0This is buggy:
>
>=C2=A0 =C2=A0 =C2=A0CREATE VARIABLE str AS text NOT NULL DEFAULT NULL;<= br> >
>=C2=A0 =C2=A0Ugh.
>
>=C2=A0 =C2=A0 =C2=A0SELECT str;
>=C2=A0 =C2=A0 =C2=A0ERROR:=C2=A0 null value is not allowed for NOT NULL= session variable "laurenz.str"
>=C2=A0 =C2=A0 =C2=A0DETAIL:=C2=A0 The result of DEFAULT expression is N= ULL.
>
>=C2=A0 =C2=A0Perhaps that is a leftover from the previous coding, but I= think there need be
>=C2=A0 =C2=A0no check upon SELECT.=C2=A0 It should be enough to check d= uring CREATE VARIABLE and
>=C2=A0 =C2=A0LET.

I'm having second thoughts about that.

I was thinking of a variable like of a table column, but there is a fundame= ntal
difference: there is a clear moment when a tuple is added (INSERT or UPDATE= ),
which is the point where a column can be checked for NULL values.

A variable can be SELECTed without having been LET before, in which case it=
has the default value.=C2=A0 But there is no way to test the default value = before
the variable is SELECTed.=C2=A0 So while DEFAULT NULL for a non-nullable va= riable
seems weird, it is no worse than DEFAULT somefunc() for a function that ret= urns
NULL.

So perhaps the behavior I complained about above is actually the right one.=
In the view of that, it doesn't seem necessary to enforce a DEFAULT val= ue for
a NOT NULL variable: NOT NULL might just as well mean "you have to LET= it before
you can SELECT it".

exactly
<= div>=C2=A0

> IMMUTABLE variables:
>
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0<varlistentry id=3D"sql-creat= evariable-immutable">
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 <term><literal>IMMUTABLE= </literal></term>
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 <listitem>
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0<para>
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0 The assigned value of the ses= sion variable can not be changed.
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0 Only if the session variable = doesn't have a default value, a single
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0 initialization is allowed usi= ng the <command>LET</command> command. Once
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0 done, no further change is al= lowed until end of transaction
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0 if the session variable was c= reated with clause <literal>ON TRANSACTION
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0 END RESET</literal>, or= until reset of all session variables by
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0 <command>DISCARD VARIAB= LES</command>, or until reset of all session
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0 objects by command <comman= d>DISCARD ALL</command>.
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 =C2=A0</para>
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0 </listitem>
>=C2=A0 =C2=A0 =C2=A0+=C2=A0 =C2=A0</varlistentry>
>
>=C2=A0 =C2=A0I can see the usefulness of IMMUTABLE variables, but I am = surprised that
>=C2=A0 =C2=A0they are reset by DISCARD.=C2=A0 What is the use case you = have in mind?
>=C2=A0 =C2=A0The use case I can envision is an application that sets a = value right after
>=C2=A0 =C2=A0authentication, for use with row-level security.=C2=A0 But= then it would be harmful
>=C2=A0 =C2=A0if the user could reset the variable with DISCARD.

I'm beginning to be uncertain about that as well.=C2=A0 You might want = to use a
connection pool, and you LET the variable when you take it out of the pool.=
When the session is returned to the pool, variables get DISCARDed.

Sure, a user can call DISCARD, but only if he or she is in an interactive s= ession.

So perhaps it is good as it is.

I think= this design should work. There are a lot of scenarios, where session varia= bles can be used well, and sure, there will be scenarios where it doesn'= ;t work well, but now, I think it is a good balance between usability, comp= lexity and code complexity. There are a lot of lines, but the code is almos= t very simple.

Regards

Pavel
=C2=A0

Yours,
Laurenz Albe
--00000000000065d75b061e02f0c9--