Re: Schema variables - new implementation for Postgres 15

út 23. 8. 2022 v 14:57 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)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(at)gmail(dot)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