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@darold.net 
> <mailto:gilles@darold.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/