Re: proposal: schema variables - Mailing list pgsql-hackers

On Wed, 2024-07-24 at 17:19 +0200, Pavel Stehule wrote:
> >   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 think it is correct. When you use SELECT str, then DEFAULT expression is
> executed, and then the result is assigned to a variable, and there is NOT NULL
> guard, which raises an exception. The variable is not initialized when you run
> DDL, but it is initialized when you first read or write from/to the variable.
> The DEFAULT expression is not evaluated in DDL time. In this case, I can detect
> the problem in DDL time because the result is transformed to NULL node, but
> generally there can be SQL non immutable function, and then I need to wait until
> the DEFAULT expression will be evaluated - and it is time of first reading.
> Unfortunately, there is not an available check if some expression is NULL,
> that I can use in DDL time, but I have it in plpgsql_check.

That makes sense to me.

In that case, I think we can drop the requirement that NOT NULL variables
need a default clause.

>
> >   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.
>
> Primary I think about IMMUTABLE variables like about some form of cache.
> This cache is protected against unwanted repeated write - and can help with
> detection of this situation.

We can leave it as it is.  The IMMUTABLE feature need not go into the first
release, so that can be discussed some more later.

Thanks for the new patch set; I'll look at it soon.

Yours,
Laurenz Albe