Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers
| From | Gilles Darold |
|---|---|
| Subject | Re: Schema variables - new implementation for Postgres 15 |
| Date | |
| Msg-id | 44b94969-5e95-6626-a241-d411c0f8e67d@darold.net Whole thread Raw |
| In response to | Re: Schema variables - new implementation for Postgres 15 (Pavel Stehule <pavel.stehule@gmail.com>) |
| Responses |
Re: Schema variables - new implementation for Postgres 15
Re: Schema variables - new implementation for Postgres 15 |
| List | pgsql-hackers |
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
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
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"
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.DETAIL: The schema variable was not initialized yet.
postgres=# LET var1=current_date;
ERROR: schema variable "var1" is declared IMMUTABLE
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 Darold http://www.darold.net/
pgsql-hackers by date: