Horton

Re: Schema variables - new implementation for Postgres 15

Julien Rouhaud <rjuju123@gmail.com>

View thread
From: Julien Rouhaud <rjuju123@gmail.com>
To: Pavel Stehule <pavel.stehule@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-23T12:57: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.

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.