Horton

Re: Schema variables - new implementation for Postgres 15

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, Joel Jacobson <joel@compiler.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-08-24T06:42:07Z
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.

út 23. 8. 2022 v 14:57 odesílatel Julien Rouhaud <rjuju123@gmail.com>
napsal:

> On Tue, Aug 23, 2022 at 11:27:45AM +0200, Pavel Stehule wrote:
> > út 23. 8. 2022 v 7:56 odesílatel Julien Rouhaud <rjuju123@gmail.com>
> napsal:
> >
> > >
> > > I've been thinking a bit more about the shadowing, and one scenario we
> > > didn't
> > > discuss is something like this naive example:
> > >
> > > CREATE TABLE tt(a text, b text);
> > >
> > > CREATE TYPE abc AS (a text, b text, c text);
> > > CREATE VARIABLE tt AS abc;
> > >
> > > INSERT INTO tt SELECT 'a', 'b';
> > > LET tt = ('x', 'y', 'z');
> > >
> > > SELECT tt.a, tt.b, tt.c FROM tt;
> > >
> > > Which, with the default configuration, currently returns
> > >
> > >  a | b | c
> > > ---+---+---
> > >  a | b | z
> > > (1 row)
> > >
> > > I feel a bit uncomfortable that the system allows mixing variable
> > > attributes
> > > and relation columns for the same relation name.  This is even worse
> here
> > > as
> > > part of the variable attributes are shadowed.
> > >
> > > It feels like a good way to write valid queries that clearly won't do
> what
> > > you
> > > think they do, a bit like the correlated sub-query trap, so maybe we
> should
> > > have a way to prevent it.
> > >
> > > What do you think?
> > >
> >
> > I thought about it before. I think valid RTE (but with the missing
> column)
> > can shadow the variable too.
> >
> > With this change your query fails:
> >
> > (2022-08-23 11:05:55) postgres=# SELECT tt.a, tt.b, tt.c FROM tt;
> > ERROR:  column tt.c does not exist
> > LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
> >                            ^
> > (2022-08-23 11:06:03) postgres=# set session_variables_ambiguity_warning
> to
> > on;
> > SET
> > (2022-08-23 11:06:19) postgres=# SELECT tt.a, tt.b, tt.c FROM tt;
> > WARNING:  session variable "tt.a" is shadowed
> > LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
> >                ^
> > DETAIL:  Session variables can be shadowed by columns, routine's
> variables
> > and routine's arguments with the same name.
> > WARNING:  session variable "tt.b" is shadowed
> > LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
> >                      ^
> > DETAIL:  Session variables can be shadowed by columns, routine's
> variables
> > and routine's arguments with the same name.
> > WARNING:  session variable "public.tt" is shadowed
> > LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
> >                            ^
> > DETAIL:   Session variables can be shadowed by tables or table's aliases
> > with the same name.
> > ERROR:  column tt.c does not exist
> > LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
>
> Great, thanks a lot!
>
> Could you add some regression tests for that scenario in the next version,
> since this is handled by some new code?  It will also probably be useful to
> remind any possible committer about that choice.
>

it is there

Regards

Pavel