Message-ID: <f9f4d536993e48990bd7eb38e18798c4572b6700.camel@cybertec.at>
Subject: Re: proposal: schema variables
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Erik Rijkers <er@xs4all.nl>, Michael Paquier <michael@paquier.xyz>,
 Zhihong Yu <zyu@yugabyte.com>, Amit Kapila <amit.kapila16@gmail.com>, DUVAL
 REMI <REMI.DUVAL@cheops.fr>,  PostgreSQL Hackers
 <pgsql-hackers@lists.postgresql.org>
Date: Wed, 24 Jul 2024 19:02:11 +0200
In-Reply-To: 
 <CAFj8pRDXo-RRcy2VFDm_vzv3Eaaz6Ex=X19up=x8W4COyBNmaQ@mail.gmail.com>
References: 
	<CAFj8pRBoGBKopkiTa4ki3dMgy-cSTRZ-BQPKOq7=Tk0SSNsowA@mail.gmail.com>
	 <CAFj8pRBCiWn12H9FHymYs17fk68nRd9Xpn+SYf18TLdb2YSUrQ@mail.gmail.com>
	 <CAA4eK1JV-Ox0oRFdXnPqXSzM84wTR5QFkRzCpNVF_+0FNjS5Mg@mail.gmail.com>
	 <CAFj8pRByCCcgDkeXyafAnH6LgxtAyCVwg6yfJAhyTY6GLscfZg@mail.gmail.com>
	 <CAFj8pRBvUonC_ug3F=w1Q55Dp=DggojvAeL7Vmh14Q-WhFHxzw@mail.gmail.com>
	 <CAFj8pRDj72P-f=SUygtOXnTOBQ0RzmL_fN=wLfaCzcbPVpGgzw@mail.gmail.com>
	 <CAFj8pRDD2GQaJ_iDT4vSVe658+oHRXU2S2af7Y1-it9jaP8VFg@mail.gmail.com>
	 <20200924035637.GF28585@paquier.xyz>
	 <CAFj8pRAFktynx5wkanv5SRuzXkZgXu77XpVACiSE=v7i1xHFbw@mail.gmail.com>
	 <CAFj8pRA=bn_g5T2AZduy5gNOQoOnUJ+pMHmnRMHi6mR0n=TAsA@mail.gmail.com>
	 <20201001033824.GC8130@paquier.xyz>
	 <CAFj8pRC9de05HSb4tEHDUwJ98+4Wh30W-rJrNOPnTz6ARcv0Fw@mail.gmail.com>
	 <CAFj8pRBC5Wz1xHKKBmKsM0xYN0+PdSZ5oXPsk5SZt+VprdUW3A@mail.gmail.com>
	 <CAFj8pRAh4pzMoZrKCLt9h+Lr2L=vhgs2PjAF45uLbp_7sijM5w@mail.gmail.com>
	 <CAFj8pRA-kxQ1oErcuDeUKYsrgwB5XGLhquatwxOe3dCVy1gcyQ@mail.gmail.com>
	 <51a9a68e8a998d04df17417d45c1dbd4@xs4all.nl>
	 <CAFj8pRBbt2xhY9PyabOY0ZN+Aig6ee3oCon-DM9qi0Uw_3qfbw@mail.gmail.com>
	 <CAFj8pRDSa52J7kPmCYXgq1BBbu3YBXwpdSOVpjgU=hnE2k04Cw@mail.gmail.com>
	 <CAFj8pRD+QiWOoPrFk2NnPs3t5Eaf4X=aGRV-9ww11cnPP+fV4g@mail.gmail.com>
	 <bdeb7611c6dbf22226c302bcc01d178b@xs4all.nl>
	 <CAFj8pRCGTjqHvH9oeiSf4T6Bydhk9pm033DxxibgF+B7SHC6MQ@mail.gmail.com>
	 <CAFj8pRAzNDhFgbZnT0T0mJ7ygA1Qje1Hc0TiKwXM8++kGooPYg@mail.gmail.com>
	 <89817942c99da01cd5e7850fe418436b@xs4all.nl>
	 <CAFj8pRBarjJYfkN-0-i=JRZJ4PTOYC+K7XgAhfdDqWGqRiPkyw@mail.gmail.com>
	 <CAFj8pRDqdWdCULxd5asbKs5C4e9kT2TuKBkR5L-e1=hP5wF2uw@mail.gmail.com>
	 <56ca532c37eb0b540961f74a7bd5db39@xs4all.nl>
	 <CAFj8pRCPW56pFr0F0BcasdXjFeo3SFixNSpWKaBk0ibvznum-A@mail.gmail.com>
	 <CAFj8pRD1Feit93CgwmYm1Q=X+M+AZqffCEZPFQ7qEMNHZRN4fA@mail.gmail.com>
	 <CAFj8pRCc=B9-FRQg5eWDSkGwS2vpkq88hR6042cmPPizHuEGSA@mail.gmail.com>
	 <CAFj8pRBk8x7afUXKLBOU-Ctg6A7QJvTAGGVEi0b6Jc8YTe8nUg@mail.gmail.com>
	 <CAFj8pRCSwHQ4BJUbjF2YEausK1Z6+ejMyedpqAnWJbG+FEJDLw@mail.gmail.com>
	 <CAFj8pRAbY+N+UqjqgESL5x-bsGmV+aVyyUkxUSgaGDZToZjDqQ@mail.gmail.com>
	 <CAFj8pRBzKcqzj=23BHfv1QaXHt=2_SN=uhdR3rb_dAVQoit7ug@mail.gmail.com>
	 <CAFj8pRCi-n6SzkAB+OHG=TZvL13xxta_qgffBLDOY0HEBqDhvg@mail.gmail.com>
	 <8181bd3abc647bdae5a4f78e71e62478a98c75f4.camel@cybertec.at>
	 <CAFj8pRBaD0_bMrCREWnVLfcTMdc0v7ns7Rt=sEvd1EoFmLfarQ@mail.gmail.com>
	 <9e67d49deb18270eddb95e602c83f02b98459843.camel@cybertec.at>
	 <CAFj8pRDXo-RRcy2VFDm_vzv3Eaaz6Ex=X19up=x8W4COyBNmaQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3 (3.52.3-1.fc40) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/f9f4d536993e48990bd7eb38e18798c4572b6700.camel%40cybertec.at>
Precedence: bulk

On Wed, 2024-07-24 at 17:19 +0200, Pavel Stehule wrote:
> > =C2=A0 This is buggy:
> >=20
> > =C2=A0 =C2=A0 CREATE VARIABLE str AS text NOT NULL DEFAULT NULL;
> >=20
> > =C2=A0 Ugh.
> >=20
> > =C2=A0 =C2=A0 SELECT str;
> > =C2=A0 =C2=A0 ERROR:=C2=A0 null value is not allowed for NOT NULL sessi=
on variable "laurenz.str"
> > =C2=A0 =C2=A0 DETAIL:=C2=A0 The result of DEFAULT expression is NULL.
> >=20
> > =C2=A0 Perhaps that is a leftover from the previous coding, but I think=
 there need be
> > =C2=A0 no check upon SELECT.=C2=A0 It should be enough to check during =
CREATE VARIABLE and
> > =C2=A0 LET.
>=20
> I think it is correct. When you use SELECT str, then DEFAULT expression i=
s
> executed, and then the result is assigned to a variable, and there is NOT=
 NULL
> guard, which raises an exception. The variable is not initialized when yo=
u run
> DDL, but it is initialized when you first read or write from/to the varia=
ble.
> The DEFAULT expression is not evaluated in DDL time. In this case, I can =
detect
> the problem in DDL time because the result is transformed to NULL node, b=
ut
> generally there can be SQL non immutable function, and then I need to wai=
t until
> the DEFAULT expression will be evaluated - and it is time of first readin=
g.
> Unfortunately, there is not an available check if some expression is NULL=
,
> that I can use in DDL time, but I have it in plpgsql_check.

That makes sense to me.

In that case, I think we can drop the requirement that NOT NULL variables
need a default clause.

>=20
> > =C2=A0 I can see the usefulness of IMMUTABLE variables, but I am surpri=
sed that
> > =C2=A0 they are reset by DISCARD.=C2=A0 What is the use case you have i=
n mind?
> > =C2=A0 The use case I can envision is an application that sets a value =
right after
> > =C2=A0 authentication, for use with row-level security.=C2=A0 But then =
it would be harmful
> > =C2=A0 if the user could reset the variable with DISCARD.
>=20
> Primary I think about IMMUTABLE variables like about some form of cache.
> This cache is protected against unwanted repeated write - and can help wi=
th
> detection of this situation.

We can leave it as it is.  The IMMUTABLE feature need not go into the first
release, so that can be discussed some more later.

Thanks for the new patch set; I'll look at it soon.

Yours,
Laurenz Albe