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 1t4FDl-00H9IQ-St for pgsql-performance@arkaria.postgresql.org; Fri, 25 Oct 2024 07:58:58 +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 1t4FDj-00DuUb-Sa for pgsql-performance@arkaria.postgresql.org; Fri, 25 Oct 2024 07:58: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 1t4FDj-00DuUO-96 for pgsql-performance@lists.postgresql.org; Fri, 25 Oct 2024 07:58:55 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t4FDg-002l87-5y for pgsql-performance@lists.postgresql.org; Fri, 25 Oct 2024 07:58:54 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-e290d6d286eso1526054276.3 for ; Fri, 25 Oct 2024 00:58:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729843131; x=1730447931; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=bSjsNwDEgYz9J1SN6zyb8mzSMBT7xTgE7wSwNjKWDTA=; b=jdvOuxzenIGjpNB9v1XeyUszQIGfX5CMMyznNBOZM+s+VJ/VRvKS8bwQpiUC5muzNX 6aMVxpvoUyQNIxHmIiIAEWbEoGfwsk5iZeU8MVqzSo8epTF+80n/wKRRnJ9c0jKz2lNE wSsdO6b+PeA4Gwae6NwplLFaHmiPZOlqCc9DZBjlHtmCk3WNHVDZywVpJXLGqwHqMyHW yQuOwu2gRj80g5XsyWYeUqaizJ3oE12TjxWOjG7CS9Q4IG2zALLmUPQeqAISes8V8XqX qZyM//7rDLba7qPWuIbgANHKhRH3xmlo2niRkdUwdZTefLxAhc6/nDqtw9XlTwlXAq5x oE7Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729843131; x=1730447931; h=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=bSjsNwDEgYz9J1SN6zyb8mzSMBT7xTgE7wSwNjKWDTA=; b=DIcDnyZJvPKLkVpdaLkl0tMTvfs34z1hF5ikp2HmbnyhrJS9Bx2/wwXSVgNpgxLVCZ QJ8wgJV1BfAsaeMWKGYjpMA0CBGqsSZ1RgK/7maqtrsl4YapUMk7JvRddaPwgRFPTHGi GcaisAVhwLhK/hsuC3X2WoivX6W5rm4vncMokX3A3P/DDhs4zMAeKDug5+eY0sC5BWKx BWNH+s3EFgPDvKz29OBuKXUnP1J1F5Sf3ljrXyzXF3srOur4YDmEXAnOEMEg5xgbRQuZ pwSLSSWJauyTcWazsbyDMBP6zaqrVJ34t1SxifkqOu/VUYfaFOjZ+M05nn2ztcIGuXe9 Bqtg== X-Forwarded-Encrypted: i=1; AJvYcCUA5EArUCRMDaGdHtXBtDOdulLfOro1rhX8Fy1etmD19E2NrNnfYong+T7fTyxuyqmFpmZm/4lhc9U5tVne7Mjwpg==@lists.postgresql.org X-Gm-Message-State: AOJu0YwdBhbOB/oSfULJtTAZCH7I1VIqFxbnk3MTUcjohGiNBAPb7CHX 9sfbLmIUo38PPR19eFjO1ZcrKwA56CtSkc9T1eotF1zPnj33dUcZFSFKTq0HgicLqE+NbaeGlH3 nJkzpTo26rxX2ORewRZjsxtCI2fnAkpMH X-Google-Smtp-Source: AGHT+IGUIn5vt8nQ1uqhKjHjPrzGqsHbZ0VBCTh1u3HRN+A+VSbhZ7mUls+6DAb3xVYDx51GxxEVeWKq1bHMky/Pmi0= X-Received: by 2002:a25:c5c4:0:b0:e2e:42db:9f3f with SMTP id 3f1490d57ef6-e2e42dbb9cdmr6210239276.5.1729843131307; Fri, 25 Oct 2024 00:58:51 -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> In-Reply-To: <8181bd3abc647bdae5a4f78e71e62478a98c75f4.camel@cybertec.at> From: James Pang Date: Fri, 25 Oct 2024 15:58:39 +0800 Message-ID: Subject: Re: proposal: schema variables To: Laurenz Albe , pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000005be00b0625487d7b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005be00b0625487d7b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Yes, a lot new coming sessions running some "select" and sql parsing/planning there, including some partition tables in the query. but there were other sessions DML on these tables at the same time too Laurenz Albe =E6=96=BC 2024=E5=B9=B47=E6=9C=8819= =E6=97=A5=E9=80=B1=E4=BA=94 =E4=B8=8B=E5=8D=887:41=E5=AF=AB=E9=81=93=EF=BC= =9A > On Sat, 2021-04-10 at 08:58 +0200, Pavel Stehule wrote: > > I am sending a strongly updated patch for schema variables. > > > > I rewrote an execution of a LET statement. In the previous > implementation I hacked > > STMT_SELECT. Now, I introduced a new statement STMT_LET, and I > implemented a new > > executor node SetVariable. Now I think this implementation is much > cleaner. > > Implementation with own executor node reduces necessary work on PL side > - and allows > > the LET statement to be prepared - what is important from a security > view. > > > > I'll try to write a second implementation based on a cleaner > implementation like > > utility command too. I expect so this version will be more simple, but > utility > > commands cannot be prepared, and probably, there should be special > support for > > any PL. I hope a cleaner implementation can help to move this patch. > > > > We can choose one variant in the next step and this variant can be > finalized. > > > > Notes, comments? > > Thank you! > > I tried to give the patch a spin, but it doesn't apply any more, > and there are too many conflicts for me to fix manually. > > So I had a look at the documentation: > > > --- a/doc/src/sgml/advanced.sgml > > +++ b/doc/src/sgml/advanced.sgml > > > + > > + The value of a schema variable is local to the current session. > Retrieving > > + a variable's value returns either a NULL or a default value, unles= s > its value > > + is set to something else in the current session with a LET command= . > The content > > + of a variable is not transactional. This is the same as in regular > variables > > + in PL languages. > > + > > + > > + > > + Schema variables are retrieved by the SELECT SQ= L > command. > > + Their value is set with the LET SQL command. > > + While schema variables share properties with tables, their value > cannot be updated > > + with an UPDATE command. > > "PL languages" -> "procedural languages". Perhaps a link to the > "procedural Languages" > chapter would be a good idea. > I don't think we should say "regular" variables: are there irregular > variables? > > My feeling is that "SQL statement XY" is better than > "XY SQL command". > > I think the last sentence should go. The properties they share with > tables are > that they live in a schema and can be used with SELECT. > Also, it is not necessary to mention that they cannot be UPDATEd. They > cannot > be TRUNCATEd or CALLed either, so why mention UPDATE specifically? > > > --- a/doc/src/sgml/catalogs.sgml > > +++ b/doc/src/sgml/catalogs.sgml > > > + > > + varisnotnull > > + boolean > > + > > + > > + True if the schema variable doesn't allow null value. The > default value is false. > > + > > + > > I think the attribute should be called "varnotnull", similar to > "attnotnull". > This attribute determines whether the variable is NOT NULL or not, not > about > its current setting. > > There is a plural missing: "doesn't allow null valueS". > > > + > > + vareoxaction > > + char > > + > > + > > + n =3D no action, d =3D dr= op > the variable, > > + r =3D reset the variable to its default valu= e. > > + > > + > > Perhaps the name "varxactendaction" would be better. > > A descriptive sentence is missing. > > > --- /dev/null > > +++ b/doc/src/sgml/ref/create_variable.sgml > > > + > > + The value of a schema variable is local to the current session. > Retrieving > > + a variable's value returns either a NULL or a default value, unless > its value > > + is set to something else in the current session with a LET command. > The content > > + of a variable is not transactional. This is the same as in regular > variables in PL languages. > > + > > "regular variables in PL languages" -> "variables in procedural languages= " > > > + > > + Schema variables are retrieved by the SELECT SQL > command. > > + Their value is set with the LET SQL command. > > + While schema variables share properties with tables, their value > cannot be updated > > + with an UPDATE command. > > + > > That's just a literal copy from the tutorial section. I have the same > comments > as there. > > > + > > + NOT NULL > > + > > + > > + The NOT NULL clause forbids to set the > variable to > > + a null value. A variable created as NOT NULL and without an > explicitly > > + declared default value cannot be read until it is initialized by > a LET > > + command. This obliges the user to explicitly initialize the > variable > > + content before reading it. > > + > > + > > + > > What is the reason for that behavior? I'd have expected that a NOT NULL > variable needs a default value. > > > --- /dev/null > > +++ b/doc/src/sgml/ref/let.sgml > > > + > > + sql_expression > > + > > + > > + An SQL expression. The result is cast into the schema variable's > type. > > + > > + > > + > > Always, even if there is no assignment or implicit cast? > > I see no such wording fir INSERT or UPDATE, so if only assignment casts > are used, > the second sentence should be removed. > > > --- a/doc/src/sgml/ref/pg_restore.sgml > > +++ b/doc/src/sgml/ref/pg_restore.sgml > > > + > > + > > + > > + > > + > > + Restore a named schema variable only. Multiple schema > variables may be specified with > > + multiple switches. > > + > > + > > + > > Do we need that? We have no such option for functions and other > non-relations. > > And if we really want such an option for "pg_restore", why not for > "pg_dump"? > > Yours, > Laurenz Albe > > > --0000000000005be00b0625487d7b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes, a lot new coming sessions running some "select&q= uot; and sql parsing/planning there, including some partition tables in the= query. but there were other sessions DML on these tables at the same time = too

Laurenz Albe <laurenz.alb= e@cybertec.at> =E6=96=BC 2024=E5=B9=B47=E6=9C=8819=E6=97=A5=E9=80=B1= =E4=BA=94 =E4=B8=8B=E5=8D=887:41=E5=AF=AB=E9=81=93=EF=BC=9A
On Sat, 2021-04-10 at 08:58 +02= 00, Pavel Stehule wrote:
> I am sending a strongly updated patch for schema variables.
>
> I rewrote an execution of a LET statement. In the previous implementat= ion I hacked
> STMT_SELECT. Now, I introduced a new statement STMT_LET, and I impleme= nted a new
> executor node SetVariable. Now I think this implementation is much cle= aner.
> Implementation with own executor node reduces necessary work on PL sid= e - and allows
> the LET statement to be prepared - what is important from a security v= iew.
>
> I'll try to write a second implementation based on a cleaner imple= mentation like
> utility command too. I expect so this version will be more simple, but= utility
> commands cannot be prepared, and probably, there should be special sup= port for
> any PL. I hope a cleaner implementation can help to move this patch. >
> We can choose one variant in the next step and this variant can be fin= alized.
>
> Notes, comments?

Thank you!

I tried to give the patch a spin, but it doesn't apply any more,
and there are too many conflicts for me to fix manually.

So I had a look at the documentation:

> --- a/doc/src/sgml/advanced.sgml
> +++ b/doc/src/sgml/advanced.sgml

> +=C2=A0 =C2=A0<para>
> +=C2=A0 =C2=A0 The value of a schema variable is local to the current = session. Retrieving
> +=C2=A0 =C2=A0 a variable's value returns either a NULL or a defau= lt value, unless its value
> +=C2=A0 =C2=A0 is set to something else in the current session with a = LET command. The content
> +=C2=A0 =C2=A0 of a variable is not transactional. This is the same as= in regular variables
> +=C2=A0 =C2=A0 in PL languages.
> +=C2=A0 =C2=A0</para>
> +
> +=C2=A0 =C2=A0<para>
> +=C2=A0 =C2=A0 Schema variables are retrieved by the <command>SE= LECT</command> SQL command.
> +=C2=A0 =C2=A0 Their value is set with the <command>LET</comm= and> SQL command.
> +=C2=A0 =C2=A0 While schema variables share properties with tables, th= eir value cannot be updated
> +=C2=A0 =C2=A0 with an <command>UPDATE</command> command.<= br>
"PL languages" -> "procedural languages".=C2=A0 Perh= aps a link to the "procedural Languages"
chapter would be a good idea.
I don't think we should say "regular" variables: are there ir= regular variables?

My feeling is that "SQL statement <command>XY</command>&qu= ot; is better than
"<command>XY</command> SQL command".

I think the last sentence should go.=C2=A0 The properties they share with t= ables are
that they live in a schema and can be used with SELECT.
Also, it is not necessary to mention that they cannot be UPDATEd.=C2=A0 The= y cannot
be TRUNCATEd or CALLed either, so why mention UPDATE specifically?

> --- a/doc/src/sgml/catalogs.sgml
> +++ b/doc/src/sgml/catalogs.sgml

> +=C2=A0 =C2=A0 =C2=A0<row>
> +=C2=A0 =C2=A0 =C2=A0 <entry><structfield>varisnotnull<= /structfield></entry>
> +=C2=A0 =C2=A0 =C2=A0 <entry><type>boolean</type><= ;/entry>
> +=C2=A0 =C2=A0 =C2=A0 <entry></entry>
> +=C2=A0 =C2=A0 =C2=A0 <entry>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0True if the schema variable doesn't al= low null value. The default value is false.
> +=C2=A0 =C2=A0 =C2=A0 </entry>
> +=C2=A0 =C2=A0 =C2=A0</row>

I think the attribute should be called "varnotnull", similar to &= quot;attnotnull".
This attribute determines whether the variable is NOT NULL or not, not abou= t
its current setting.

There is a plural missing: "doesn't allow null valueS".

> +=C2=A0 =C2=A0 =C2=A0<row>
> +=C2=A0 =C2=A0 =C2=A0 <entry><structfield>vareoxaction<= /structfield></entry>
> +=C2=A0 =C2=A0 =C2=A0 <entry><type>char</type></e= ntry>
> +=C2=A0 =C2=A0 =C2=A0 <entry></entry>
> +=C2=A0 =C2=A0 =C2=A0 <entry>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0<literal>n</literal> =3D no ac= tion, <literal>d</literal> =3D drop the variable,
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0<literal>r</literal> =3D reset= the variable to its default value.
> +=C2=A0 =C2=A0 =C2=A0 </entry>
> +=C2=A0 =C2=A0 =C2=A0</row>

Perhaps the name "varxactendaction" would be better.

A descriptive sentence is missing.

> --- /dev/null
> +++ b/doc/src/sgml/ref/create_variable.sgml

> +=C2=A0 <para>
> +=C2=A0 =C2=A0The value of a schema variable is local to the current s= ession. Retrieving
> +=C2=A0 =C2=A0a variable's value returns either a NULL or a defaul= t value, unless its value
> +=C2=A0 =C2=A0is set to something else in the current session with a L= ET command. The content
> +=C2=A0 =C2=A0of a variable is not transactional. This is the same as = in regular variables in PL languages.
> +=C2=A0 </para>

"regular variables in PL languages" -> "variables in proc= edural languages"

> +=C2=A0 <para>
> +=C2=A0 =C2=A0Schema variables are retrieved by the <command>SEL= ECT</command> SQL command.
> +=C2=A0 =C2=A0Their value is set with the <command>LET</comma= nd> SQL command.
> +=C2=A0 =C2=A0While schema variables share properties with tables, the= ir value cannot be updated
> +=C2=A0 =C2=A0with an <command>UPDATE</command> command. > +=C2=A0 </para>

That's just a literal copy from the tutorial section.=C2=A0 I have the = same comments
as there.

> +=C2=A0 =C2=A0<varlistentry>
> +=C2=A0 =C2=A0 <term><literal>NOT NULL</literal><= /term>
> +=C2=A0 =C2=A0 <listitem>
> +=C2=A0 =C2=A0 =C2=A0<para>
> +=C2=A0 =C2=A0 =C2=A0 The <literal>NOT NULL</literal> clau= se forbids to set the variable to
> +=C2=A0 =C2=A0 =C2=A0 a null value. A variable created as NOT NULL and= without an explicitly
> +=C2=A0 =C2=A0 =C2=A0 declared default value cannot be read until it i= s initialized by a LET
> +=C2=A0 =C2=A0 =C2=A0 command. This obliges the user to explicitly ini= tialize the variable
> +=C2=A0 =C2=A0 =C2=A0 content before reading it.
> +=C2=A0 =C2=A0 =C2=A0</para>
> +=C2=A0 =C2=A0 </listitem>
> +=C2=A0 =C2=A0</varlistentry>

What is the reason for that behavior?=C2=A0 I'd have expected that a NO= T NULL
variable needs a default value.

> --- /dev/null
> +++ b/doc/src/sgml/ref/let.sgml

> +=C2=A0 =C2=A0<varlistentry>
> +=C2=A0 =C2=A0 <term><literal>sql_expression</literal&g= t;</term>
> +=C2=A0 =C2=A0 <listitem>
> +=C2=A0 =C2=A0 =C2=A0<para>
> +=C2=A0 =C2=A0 =C2=A0 An SQL expression. The result is cast into the s= chema variable's type.
> +=C2=A0 =C2=A0 =C2=A0</para>
> +=C2=A0 =C2=A0 </listitem>
> +=C2=A0 =C2=A0</varlistentry>

Always, even if there is no assignment or implicit cast?

I see no such wording fir INSERT or UPDATE, so if only assignment casts are= used,
the second sentence should be removed.

> --- a/doc/src/sgml/ref/pg_restore.sgml
> +++ b/doc/src/sgml/ref/pg_restore.sgml

> +=C2=A0 =C2=A0 =C2=A0<varlistentry>
> +=C2=A0 =C2=A0 =C2=A0 <term><option>-A <replaceable cla= ss=3D"parameter">schema_variable</replaceable></optio= n></term>
> +=C2=A0 =C2=A0 =C2=A0 <term><option>--variable=3D<repla= ceable class=3D"parameter">schema_variable</replaceable>= </option></term>
> +=C2=A0 =C2=A0 =C2=A0 <listitem>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0<para>
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 Restore a named schema variable only.=C2= =A0 Multiple schema variables may be specified with
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0 multiple <option>-A</option> = switches.
> +=C2=A0 =C2=A0 =C2=A0 =C2=A0</para>
> +=C2=A0 =C2=A0 =C2=A0 </listitem>
> +=C2=A0 =C2=A0 =C2=A0</varlistentry>

Do we need that?=C2=A0 We have no such option for functions and other non-r= elations.

And if we really want such an option for "pg_restore", why not fo= r "pg_dump"?

Yours,
Laurenz Albe


--0000000000005be00b0625487d7b--