Re: proposal: schema variables

út 23. 7. 2024 v 23:41 odesílatel Laurenz Albe <laurenz.albe@cybertec.at>
napsal:

> On Tue, 2024-07-23 at 16:34 +0200, Laurenz Albe wrote:
> > CREATE VARIABLE command:
> >
> >   This is buggy:
> >
> >     CREATE VARIABLE str AS text NOT NULL DEFAULT NULL;
> >
> >   Ugh.
> >
> >     SELECT str;
> >     ERROR:  null value is not allowed for NOT NULL session variable
> "laurenz.str"
> >     DETAIL:  The result of DEFAULT expression is NULL.
> >
> >   Perhaps that is a leftover from the previous coding, but I think there
> need be
> >   no check upon SELECT.  It should be enough to check during CREATE
> VARIABLE and
> >   LET.
>
> I'm having second thoughts about that.
>
> I was thinking of a variable like of a table column, but there is a
> fundamental
> difference: there is a clear moment when a tuple is added (INSERT or
> UPDATE),
> which is the point where a column can be checked for NULL values.
>
> A variable can be SELECTed without having been LET before, in which case it
> has the default value.  But there is no way to test the default value
> before
> the variable is SELECTed.  So while DEFAULT NULL for a non-nullable
> variable
> seems weird, it is no worse than DEFAULT somefunc() for a function that
> returns
> NULL.
>
> So perhaps the behavior I complained about above is actually the right one.
> In the view of that, it doesn't seem necessary to enforce a DEFAULT value
> for
> a NOT NULL variable: NOT NULL might just as well mean "you have to LET it
> before
> you can SELECT it".
>

exactly


>
> > IMMUTABLE variables:
> >
> >     +   <varlistentry id="sql-createvariable-immutable">
> >     +    <term><literal>IMMUTABLE</literal></term>
> >     +    <listitem>
> >     +     <para>
> >     +      The assigned value of the session variable can not be changed.
> >     +      Only if the session variable doesn't have a default value, a
> single
> >     +      initialization is allowed using the <command>LET</command>
> command. Once
> >     +      done, no further change is allowed until end of transaction
> >     +      if the session variable was created with clause <literal>ON
> TRANSACTION
> >     +      END RESET</literal>, or until reset of all session variables
> by
> >     +      <command>DISCARD VARIABLES</command>, or until reset of all
> session
> >     +      objects by command <command>DISCARD ALL</command>.
> >     +     </para>
> >     +    </listitem>
> >     +   </varlistentry>
> >
> >   I can see the usefulness of IMMUTABLE variables, but I am surprised
> that
> >   they are reset by DISCARD.  What is the use case you have in mind?
> >   The use case I can envision is an application that sets a value right
> after
> >   authentication, for use with row-level security.  But then it would be
> harmful
> >   if the user could reset the variable with DISCARD.
>
> I'm beginning to be uncertain about that as well.  You might want to use a
> connection pool, and you LET the variable when you take it out of the pool.
> When the session is returned to the pool, variables get DISCARDed.
>
> Sure, a user can call DISCARD, but only if he or she is in an interactive
> session.
>
> So perhaps it is good as it is.
>

I think this design should work. There are a lot of scenarios, where
session variables can be used well, and sure, there will be scenarios where
it doesn't work well, but now, I think it is a good balance between
usability, complexity and code complexity. There are a lot of lines, but
the code is almost very simple.

Regards

Pavel


>
> Yours,
> Laurenz Albe
>