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 1t4FEZ-00H9Ld-99 for pgsql-performance@arkaria.postgresql.org; Fri, 25 Oct 2024 07:59:47 +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 1t4FEX-00Dx8X-Ju for pgsql-performance@arkaria.postgresql.org; Fri, 25 Oct 2024 07:59:45 +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 1t4FEX-00Dx8P-19 for pgsql-performance@lists.postgresql.org; Fri, 25 Oct 2024 07:59:45 +0000 Received: from mail-yb1-xb33.google.com ([2607:f8b0:4864:20::b33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t4FEQ-002l8S-Lw for pgsql-performance@lists.postgresql.org; Fri, 25 Oct 2024 07:59:44 +0000 Received: by mail-yb1-xb33.google.com with SMTP id 3f1490d57ef6-e2e444e355fso2043675276.1 for ; Fri, 25 Oct 2024 00:59:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729843178; x=1730447978; 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=060ztHb/A9ER+fBtf+GEkF4LMDTOUzwxOUEr5sgtTxA=; b=LfI8k49I2KeFA3PEKVmop5DIDHE/r1IFQvcBwsbAInq8/l2qNI3rGd+bVM3kz+srBe WeqJxJI5dyOjHiIx0/6mxtwrk7EyiQxqnNEvZy6CcQ6hu0GUmaWFl9GHySBa0si5A4RA zl7f+1aX1Hdfevoq72OsOyMzi7OjzEWCkFqJuTR4DmByd5PdFrpUXiHtyv7knZBYesRH 6rBWDczDd9EHmmOGBNRbYRhiMSo8z9+yVbHrcTIy9+tT3pA3dbnsTMiEQGmAyLcXNHHl BEewaz2S0unJRWHHSVyuz9pNWd5/d9hcTHv6VX8AbJp6t3KawYVH7Xg3nDH4x8df9yJD JRUQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729843178; x=1730447978; 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=060ztHb/A9ER+fBtf+GEkF4LMDTOUzwxOUEr5sgtTxA=; b=MfO2/lrv6esGayArxeibFSYdBx0NtUgbmix2jG8SKMmme7FKh9qrjW4dme5iXB2q1a PvYunaCLZKtyVtJdt2uoEWuw/h6PatJ67Us29j5g6W1UrLjhwFJF5x+6MkcJbkOhr4EB GGqX6PX4JZqLBpOKfbJJJAZUF6zpSclcBOr30pddXlld4j+pcxfJaXV8CVNKxBJOGFk/ FJNAVQGPnQqDL4W/kGz0+ESHvQG807iQnWNRrSU9vd5mY8eUbj+1PE/09PMjz7I9XALV 24mLprz0alhNYuN5v1evoP8g98156dA4IyPRun3yd4VF7DOn4q+uHMrMx4ZcVTqTYo2y lQVw== X-Forwarded-Encrypted: i=1; AJvYcCUlw0x1OBTCtYw6Hz/txGFNzyFHcWSvnS24NM2hKAPSG5PL2jfQ95JEL4dmlSR3xmA62EhrL7vULUWVGoL+WiBW1Q==@lists.postgresql.org X-Gm-Message-State: AOJu0Yxp0nXwY+d9xGnqJKYl1mYMLcvCaSFa/A9At4z3JzzBJwEXl2E6 PFGCDGTuzYE9xZvFOVN7ZQ6puc+A+Djz2B79MTDcOxEF0WoMPBsQM9A36Q74uUOESfkOLBfvZLx +MXceVrm7qmR2aG7ojbai7NzwZy4= X-Google-Smtp-Source: AGHT+IEtoPqMpNDo4ZrIg0AKaDjowzB8kamdlP2Q8DXuRlPXJPiBfmlpmuTFW1RmV00HWwut7SEWNKwGQ3x8xg9mXbg= X-Received: by 2002:a05:6902:2805:b0:e2e:45ad:9abb with SMTP id 3f1490d57ef6-e2ea8a1f28bmr4614745276.3.1729843177879; Fri, 25 Oct 2024 00:59:37 -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: From: James Pang Date: Fri, 25 Oct 2024 15:59:26 +0800 Message-ID: Subject: Re: proposal: schema variables To: Laurenz Albe , pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000002284880625488096" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002284880625488096 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable sorry, I sent to wrong email. please ignore. James Pang =E6=96=BC 2024=E5=B9=B410=E6=9C=8825=E6= =97=A5=E9=80=B1=E4=BA=94 =E4=B8=8B=E5=8D=883:58=E5=AF=AB=E9=81=93=EF=BC=9A > 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=88= 19=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 sid= e >> - 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, >> 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 regula= r >> variables >> > + in PL 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. >> >> "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 d= rop >> the variable, >> > + r =3D reset the variable to its default val= ue. >> > + >> > + >> >> 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, 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. >> > + >> >> "regular variables in PL languages" -> "variables in procedural language= s" >> >> > + >> > + 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. >> > + >> >> 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 b= y >> 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 >> >> >> --0000000000002284880625488096 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
sorry, I sent to wrong email. please ignore.=C2=A0
James Pa= ng <jamespang886@gmail.com= > =E6=96=BC 2024=E5=B9=B410=E6=9C=8825=E6=97=A5=E9=80=B1=E4=BA=94 =E4=B8= =8B=E5=8D=883:58=E5=AF=AB=E9=81=93=EF=BC=9A
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 th= ese tables at the same time too

Laurenz Albe <laurenz.albe@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=88= 7: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 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


--0000000000002284880625488096--