Re: Schema variables - new implementation for Postgres 15

Le 08/09/2021 à 13:41, Pavel Stehule a écrit :
> Hi
>
> so 28. 8. 2021 v 11:57 odesílatel Gilles Darold <gilles(at)darold(dot)net
> <mailto:gilles(at)darold(dot)net>> napsal:
>
> Hi,
>
> Review resume:
>
>
> This patch implements Schema Variables that are database objects
> that can hold a single or composite value following the data type
> used at variable declaration. Schema variables, like relations,
> exist within a schema and their access is controlled via GRANT and
> REVOKE commands. The schema variable can be created by the CREATE
> VARIABLE command, altered using ALTER VARIABLE and removed using
> DROP VARIABLE.
>
> 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.
>
> Schema variables are retrieved by the SELECT SQL command. Their
> value is set with the LET SQL command. While schema variables
> share properties with tables, their value cannot be updated with
> an UPDATE command.
>
>
> The patch apply with the patch command without problem and
> compilation reports no warning or errors. Regression tests pass
> successfully using make check or make installcheck
> It also includes all documentation and regression tests.
>
> Performances are near the set of plpgsql variable settings which
> is impressive:
>
> do $$
> declare var1 int ; i int;
> begin
>   for i in 1..1000000
>   loop
>     var1 := i;
>   end loop;
> end;
> $$;
> DO
> Time: 71,515 ms
>
> CREATE VARIABLE var1 AS integer;
> do $$
> declare i int ;
> begin
>   for i in 1..1000000
>   loop
>     let var1 = i;
>   end loop;
> end;
> $$;
> DO
> Time: 94,658 ms
>
> There is just one thing that puzzles me.We can use :
>
>     CREATE VARIABLE var1 AS date NOT NULL;
>     postgres=# SELECT var1;
>     ERROR:  null value is not allowed for NOT NULL schema variable
> "var1"
>
> which I understand and is the right behavior. But if we use:
>
>     CREATE IMMUTABLE VARIABLE var1 AS date NOT NULL;
>     postgres=# SELECT var1;
>     ERROR:  null value is not allowed for NOT NULL schema variable
> "var1"
>     DETAIL:  The schema variable was not initialized yet.
>     postgres=# LET var1=current_date;
>     ERROR:  schema variable "var1" is declared IMMUTABLE
>
> It should probably be better to not allow NOT NULL when IMMUTABLE
> is used because the variable can not be used at all.  Also
> probably IMMUTABLE without a DEFAULT value should also be
> restricted as it makes no sens. If the user wants the variable to
> be NULL he must use DEFAULT NULL. This is just a though, the above
> error messages are explicit and the user can understand what wrong
> declaration he have done.
>
>
> I wrote a check that disables this case.  Please, see the attached
> patch. I agree, so this case is confusing, and it is better to disable it.
>

Great, I also think that this is better to not confuse the user.

    postgres=# CREATE IMMUTABLE VARIABLE var1 AS date NOT NULL;
    ERROR:  IMMUTABLE NOT NULL variable requires default expression

Working as expected. I have moved the patch to "Ready for committers".
Thanks for this feature.

--
Gilles Darold
http://www.darold.net/