Horton

Re: Schema variables - new implementation for Postgres 15

Jaime Casanova <jcasanov@systemguards.com.ec>

View thread
From: Jaime Casanova <jcasanov@systemguards.com.ec>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Erik Rijkers <er@xs4all.nl>, Gilles Darold <gilles@darold.net>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Michael Paquier <michael@paquier.xyz>, Amit Kapila <amit.kapila16@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>
Date: 2021-09-12T02:13:38Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow underscores in integer and numeric constants.

  2. Remove special outfuncs/readfuncs handling of RangeVar.catalogname.

  3. Remove extra space from dumped ALTER DEFAULT PRIVILEGES.

  4. Create FKs properly when attaching table as partition

  5. psql: improve tab-complete's handling of variant SQL names.

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