Re: Schema variables - new implementation for Postgres 15

On Fri, Sep 10, 2021 at 10:06:04AM +0200, Pavel Stehule wrote:
> Hi
>
> čt 9. 9. 2021 v 12:21 odesílatel Erik Rijkers <er(at)xs4all(dot)nl> napsal:
>
> > > [schema-variables-20210909.patch]
> >
> > Hi Pavel,
> >
> > The patch applies and compiles fine but 'make check' for the
> > assert-enabled fails on 131 out of 210 tests.
> >
> > (while compiling HEAD checks run without errors for both assert-disabled
> > and assert-enabled)
> >
> >
>
> Please, check, attached patch. I fixed a routine for processing a list of
> identifiers - now it works with the identifier's node more sensitive.
> Previous implementation of strVal was more tolerant.
>

Hi Pavel,

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

TEMP variables are not schema variables? at least not attached to the
schema one expects:

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

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

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 will test more this patch tomorrow. Great work, very complete.

--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL