Re: Schema variables - new implementation for Postgres 15

ne 12. 9. 2021 v 17:38 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

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

any value of a schema variable has a session (or transaction) life cycle.
But the schema variable itself is persistent. temp schema variable is an
exception. It is limited by session (and the value stored in the variable
is limited to session too).

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