Re: proposal: schema variables

sorry, I sent to wrong email. please ignore.

James Pang <jamespang886(at)gmail(dot)com> 於 2024年10月25日週五 下午3:58寫道：

> 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
>>
>>
>>