Re: Schema variables - new implementation for Postgres 15

Hi

so 28. 8. 2021 v 11:57 odesílatel Gilles Darold <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/