Re: Schema variables - new implementation for Postgres 15

so 28. 8. 2021 v 11:57 odesílatel Gilles Darold <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 thought about this case, and I have one scenario, where this behaviour
can be useful. When the variable is declared as IMMUTABLE NOT NULL without
not null default, then any access to the content of the variable has to
fail. I think it can be used for detection, where and when the variable is
first used. So this behavior is allowed just because I think, so this
feature can be interesting for debugging. If this idea is too strange, I
have no problem to disable this case.

Regards

Pavel

>
> Except that I think this patch is ready for committers, so if there is no
> other opinion in favor of restricting the use of IMMUTABLE with NOT NULL
> and DEFAULT I will change the status to ready for committers.
>
> --
> Gilles Daroldhttp://www.darold.net/
>
>