public inbox for [email protected]
help / color / mirror / Atom feedFrom: James Pang <[email protected]>
To: Laurenz Albe <[email protected]>
To: [email protected]
Subject: Re: proposal: schema variables
Date: Fri, 25 Oct 2024 15:59:26 +0800
Message-ID: <CAHgTRfeag7Dv3SzUH0vrUGkkyF4cEzja9T+POE0BriCDQjDL4A@mail.gmail.com> (raw)
In-Reply-To: <CAHgTRffZBJkNRm3gyv_JcreuztuSV6WZxQzEw8-P7V3Pj10W+w@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>
<[email protected]>
<CAFj8pRAFktynx5wkanv5SRuzXkZgXu77XpVACiSE=v7i1xHFbw@mail.gmail.com>
<CAFj8pRA=bn_g5T2AZduy5gNOQoOnUJ+pMHmnRMHi6mR0n=TAsA@mail.gmail.com>
<[email protected]>
<CAFj8pRC9de05HSb4tEHDUwJ98+4Wh30W-rJrNOPnTz6ARcv0Fw@mail.gmail.com>
<CAFj8pRBC5Wz1xHKKBmKsM0xYN0+PdSZ5oXPsk5SZt+VprdUW3A@mail.gmail.com>
<CAFj8pRAh4pzMoZrKCLt9h+Lr2L=vhgs2PjAF45uLbp_7sijM5w@mail.gmail.com>
<CAFj8pRA-kxQ1oErcuDeUKYsrgwB5XGLhquatwxOe3dCVy1gcyQ@mail.gmail.com>
<[email protected]>
<CAFj8pRBbt2xhY9PyabOY0ZN+Aig6ee3oCon-DM9qi0Uw_3qfbw@mail.gmail.com>
<CAFj8pRDSa52J7kPmCYXgq1BBbu3YBXwpdSOVpjgU=hnE2k04Cw@mail.gmail.com>
<CAFj8pRD+QiWOoPrFk2NnPs3t5Eaf4X=aGRV-9ww11cnPP+fV4g@mail.gmail.com>
<[email protected]>
<CAFj8pRCGTjqHvH9oeiSf4T6Bydhk9pm033DxxibgF+B7SHC6MQ@mail.gmail.com>
<CAFj8pRAzNDhFgbZnT0T0mJ7ygA1Qje1Hc0TiKwXM8++kGooPYg@mail.gmail.com>
<[email protected]>
<CAFj8pRBarjJYfkN-0-i=JRZJ4PTOYC+K7XgAhfdDqWGqRiPkyw@mail.gmail.com>
<CAFj8pRDqdWdCULxd5asbKs5C4e9kT2TuKBkR5L-e1=hP5wF2uw@mail.gmail.com>
<[email protected]>
<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>
<[email protected]>
<CAHgTRffZBJkNRm3gyv_JcreuztuSV6WZxQzEw8-P7V3Pj10W+w@mail.gmail.com>
sorry, I sent to wrong email. please ignore.
James Pang <[email protected]> 於 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 <[email protected]> 於 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
>>
>>
>>
view thread (439+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: proposal: schema variables
In-Reply-To: <CAHgTRfeag7Dv3SzUH0vrUGkkyF4cEzja9T+POE0BriCDQjDL4A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox