Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: Schema variables - new implementation for Postgres 15 |
| Date | |
| Msg-id | CAFj8pRA-y9aTVwYPuVcdLpeb1F5M4n_mDmz-eec3ipJsC79GeA@mail.gmail.com Whole thread Raw |
| In response to | Re: Schema variables - new implementation for Postgres 15 (Jaime Casanova <jcasanov@systemguards.com.ec>) |
| Responses |
Re: Schema variables - new implementation for Postgres 15
Re: Schema variables - new implementation for Postgres 15 Re: Schema variables - new implementation for Postgres 15 |
| List | pgsql-hackers |
Hi
Just noted that there is no support for REASSIGN OWNED BY:
"""
regression=# create variable random_number numeric;
CREATE VARIABLE
regression=# alter variable random_number owner to jcm;
ALTER VARIABLE
regression=# reassign owned by jcm to jaime;
ERROR: unexpected classid 9222
"""
should be fixed by the attached patch, please check.
TEMP variables are not schema variables? at least not attached to the
schema one expects:
temp variables are schema variables like any other. But they are created in temp schema - like temp tables.
I designed it in consistency with temporary tables.
"""
regression=# create temp variable random_number numeric ;
CREATE VARIABLE
regression=# \dV
List of variables
Schema | Name | Type | Is nullable | Is mutable | Default | Owner | Transaction
al end action
-----------+---------------+---------+-------------+------------+---------+----------+------------
--------------
pg_temp_4 | random_number | numeric | t | t | | jcasanov |
(1 row)
regression=# select public.random_number;
ERROR: missing FROM-clause entry for table "public"
LINE 1: select public.random_number;
^
"""
There was a comment that TEMP variables should be DECLAREd instead of
CREATEd, i guess that is because those have similar behaviour. At least,
I would like to see similar messages when using the ON COMMIT DROP
option in a TEMP variable:
I don't remember this comment. When I talked about similarity with the DECLARE statement, I thought about semantic similarity with T-SQL (Microsoft SQL) DECLARE command. Unfortunately, DECLARE command is pretty messy - it exists in SQL, it exists in SQL/PSM and it exists in T-SQL - and every time has similar syntax, but partially different semantics. For me - CREATE TEMP VARIABLE creates session's life limited variable (by default), similarly like DECLARE @localvariable command from T-SQL.
"""
regression=# create temp variable random_number numeric on commit drop;
CREATE VARIABLE
regression=# \dV
Did not find any schema variables.
regression=# declare q cursor for select 1;
ERROR: DECLARE CURSOR can only be used in transaction blocks
"""
I have different result
postgres=# create temp variable random_number numeric on commit drop;
CREATE VARIABLE
postgres=# \dV
List of variables
┌────────┬───────────────┬─────────┬─────────────┬────────────┬─────────┬───────┬──────────────────────────┐
│ Schema │ Name │ Type │ Is nullable │ Is mutable │ Default │ Owner │ Transactional end action │
╞════════╪═══════════════╪═════════╪═════════════╪════════════╪═════════╪═══════╪══════════════════════════╡
│ public │ random_number │ numeric │ t │ t │ │ tom2 │ │
└────────┴───────────────┴─────────┴─────────────┴────────────┴─────────┴───────┴──────────────────────────┘
(1 row)
CREATE VARIABLE
postgres=# \dV
List of variables
┌────────┬───────────────┬─────────┬─────────────┬────────────┬─────────┬───────┬──────────────────────────┐
│ Schema │ Name │ Type │ Is nullable │ Is mutable │ Default │ Owner │ Transactional end action │
╞════════╪═══════════════╪═════════╪═════════════╪════════════╪═════════╪═══════╪══════════════════════════╡
│ public │ random_number │ numeric │ t │ t │ │ tom2 │ │
└────────┴───────────────┴─────────┴─────────────┴────────────┴─────────┴───────┴──────────────────────────┘
(1 row)
About that, why are you not using syntax ON COMMIT RESET instead on
inventing ON TRANSACTION END RESET? seems better because you already use
ON COMMIT DROP.
I thought about this question for a very long time, and I think the introduction of a new clause is better, and I will try to explain why.
One part of this patch are DDL statements - and all DDL statements are consistent with other DDL statements in Postgres. Schema variables DDL commands are transactional and for TEMP variables we can specify a scope - session or transaction, and then clause ON COMMIT DROP is used. You should not need to specify ON ROLLBACK action, because in this case an removing from system catalogue is only one possible action.
Second part of this patch is holding some value in schema variables or initialization with default expression. The default behaviour is not transactional, and the value is stored all session's time by default. But I think it can be very useful to enforce initialization in some specific times - now only the end of the transaction is possible to specify. In the future there can be transaction end, transaction start, rollback, commit, top query start, top query end, ... This logic is different from the logic of DDL commands. For DDL commands I need to specify behaviour just for the COMMIT end. But for reset of non-transactional schema variables I need to specify any possible end of transaction - COMMIT, ROLLBACK or COMMIT or ROLLBACK. In this initial version I implemented "ON COMMIT OR ROLLBACK RESET", and although it is clean I think it is more readable is the clause that I invented "ON TRANSACTION END". "ON COMMIT RESET" is not exact. "ON COMMIT OR ROLLBACK RESET" sounds a little bit strange for me, but we use something similar in trigger definition "ON INSERT OR UPDATE OR DELETE ..." My opinion is not too strong if "ON TRANSACTION END RESET" or "ON COMMIT OR ROLLBACK RESET" is better, and I can change it if people will have different preferences, but I am sure so "ON COMMIT RESET" is not correct in implemented case. And from the perspective of a PLpgSQL developer, I would have initialized the variable on any transaction start, so I need to reset it on any end.
Regards
Pavel
I will test more this patch tomorrow. Great work, very complete.
--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL
Attachment
pgsql-hackers by date: