Re: proposal: schema variables

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 <laurenz(dot)albe(at)cybertec(dot)at> 於 2024年7月19日週五 下午7:41寫道：

> 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
>
> > + <para>
> > + 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.
> > + </para>
> > +
> > + <para>
> > + Schema variables are retrieved by the <command>SELECT</command> SQL
> command.
> > + Their value is set with the <command>LET</command> SQL command.
> > + While schema variables share properties with tables, their value
> cannot be updated
> > + with an <command>UPDATE</command> 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 <command>XY</command>" is better than
> "<command>XY</command> 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
>
> > + <row>
> > + <entry><structfield>varisnotnull</structfield></entry>
> > + <entry><type>boolean</type></entry>
> > + <entry></entry>
> > + <entry>
> > + True if the schema variable doesn't allow null value. The
> default value is false.
> > + </entry>
> > + </row>
>
> 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".
>
> > + <row>
> > + <entry><structfield>vareoxaction</structfield></entry>
> > + <entry><type>char</type></entry>
> > + <entry></entry>
> > + <entry>
> > + <literal>n</literal> = no action, <literal>d</literal> = drop
> the variable,
> > + <literal>r</literal> = reset the variable to its default value.
> > + </entry>
> > + </row>
>
> Perhaps the name "varxactendaction" would be better.
>
> A descriptive sentence is missing.
>
> > --- /dev/null
> > +++ b/doc/src/sgml/ref/create_variable.sgml
>
> > + <para>
> > + 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.
> > + </para>
>
> "regular variables in PL languages" -> "variables in procedural languages"
>
> > + <para>
> > + Schema variables are retrieved by the <command>SELECT</command> SQL
> command.
> > + Their value is set with the <command>LET</command> SQL command.
> > + While schema variables share properties with tables, their value
> cannot be updated
> > + with an <command>UPDATE</command> command.
> > + </para>
>
> That's just a literal copy from the tutorial section. I have the same
> comments
> as there.
>
> > + <varlistentry>
> > + <term><literal>NOT NULL</literal></term>
> > + <listitem>
> > + <para>
> > + The <literal>NOT NULL</literal> 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.
> > + </para>
> > + </listitem>
> > + </varlistentry>
>
> 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
>
> > + <varlistentry>
> > + <term><literal>sql_expression</literal></term>
> > + <listitem>
> > + <para>
> > + An SQL expression. The result is cast into the schema variable's
> type.
> > + </para>
> > + </listitem>
> > + </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
>
> > + <varlistentry>
> > + <term><option>-A <replaceable
> class="parameter">schema_variable</replaceable></option></term>
> > + <term><option>--variable=<replaceable
> class="parameter">schema_variable</replaceable></option></term>
> > + <listitem>
> > + <para>
> > + Restore a named schema variable only. Multiple schema
> variables may be specified with
> > + multiple <option>-A</option> switches.
> > + </para>
> > + </listitem>
> > + </varlistentry>
>
> 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
>
>
>