Re: Schema variables - new implementation for Postgres 15

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)

> 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
>