Re: Schema variables - new implementation for Postgres 15

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?