Re: proposal: schema variables

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

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

Yours,
Laurenz Albe