Re: Schema variables - new implementation for Postgres 15

út 23. 8. 2022 v 7:56 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)com> napsal:

> Hi,
>
> On Tue, Jan 18, 2022 at 10:01:01PM +0100, Pavel Stehule wrote:
> >
> > pá 14. 1. 2022 v 3:44 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)com>
> napsal:
> >
> > > On Thu, Jan 13, 2022 at 07:32:26PM +0100, Pavel Stehule wrote:
> > > > čt 13. 1. 2022 v 19:23 odesílatel Dean Rasheed <
> dean(dot)a(dot)rasheed(at)gmail(dot)com
> > > >
> > > > > On Thu, 13 Jan 2022 at 17:42, Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com>
> > > > > wrote:
> > > > > >
> > > > > > I like the idea of prioritizing tables over variables with
> warnings
> > > when
> > > > > collision is detected. It cannot break anything. And it allows to
> using
> > > > > short identifiers when there is not collision.
> > > > >
> > > > > Yeah, that seems OK, as long as it's clearly documented. I don't
> think
> > > > > a warning is necessary.
> > >
> > > What should be the behavior for a cached plan that uses a variable
> when a
> > > conflicting relation is later created? I think that it should be the
> same
> > > as a
> > > search_path change and the plan should be discarded.
> > >
> > > > The warning can be disabled by default, but I think it should be
> there.
> > > > This is a signal, so some in the database schema should be renamed.
> > > Maybe -
> > > > session_variables_ambiguity_warning.
> > >
> > > I agree that having a way to know that a variable has been bypassed
> can be
> > > useful.
> > >
> >
> > done
>
> 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;
^
Regards

Pavel