Re: proposal: schema variables

út 23. 7. 2024 v 23:41 odesílatel Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>