Re: EphemeralNamedRelation and materialized view

On Fri, 15 Nov 2024 at 13:37, Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:
>
> On Sun, 03 Nov 2024 13:42:33 -0500
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> writes:
> > > While looking into the commit b4da732fd64e936970f38c792f8b32c4bdf2bcd5,
> > > I noticed that we can create a materialized view using Ephemeral Named
> > > Relation in PostgreSQL 16 or earler.
> >
> > Yeah, we should reject that, but I feel like this patch is not
> > ambitious enough, because the 17-and-up behavior isn't exactly
> > polished either.
> >
> > I tried variants of this function in HEAD:
> >
> > 1. With "create table mv as select * from enr", it works and
> > does what you'd expect.
> >
> > 2. With "create view mv as select * from enr", you get
> >
> > regression=# insert into tbl values (10);
> > ERROR: relation "enr" does not exist
> > LINE 1: create view mv as select * from enr
> > ^
> > QUERY: create view mv as select * from enr
> > CONTEXT: PL/pgSQL function f() line 2 at SQL statement
> > regression=# \errverbose
> > ERROR: 42P01: relation "enr" does not exist
> > LINE 1: create view mv as select * from enr
> > ^
> > QUERY: create view mv as select * from enr
> > CONTEXT: PL/pgSQL function f() line 2 at SQL statement
> > LOCATION: parserOpenTable, parse_relation.c:1452
> >
> > 3. With "create materialized view ..." you get
> >
> > regression=# insert into tbl values (10);
> > ERROR: executor could not find named tuplestore "enr"
> > CONTEXT: SQL statement "create materialized view mv as select * from enr"
> > PL/pgSQL function f() line 2 at SQL statement
> > regression=# \errverbose
> > ERROR: XX000: executor could not find named tuplestore "enr"
> > CONTEXT: SQL statement "create materialized view mv as select * from enr"
> > PL/pgSQL function f() line 2 at SQL statement
> > LOCATION: ExecInitNamedTuplestoreScan, nodeNamedtuplestorescan.c:107
> >
> > I don't think hitting an internal error is good enough.
> > Why doesn't this case act like case 2?
>
> I agree that raising an internal error is not enough. I attached a updated
> patch that outputs a message saying that an ENR can't be used in a matview.
>
> > You could even argue that case 2 isn't good enough either,
> > and we should be delivering a specific error message saying
> > that an ENR can't be used in a view/matview. To do that,
> > we'd likely need to pass down the QueryEnvironment in more
> > places not fewer.
>
> We can raise a similar error for (not materialized) views by passing
> QueryEnv to DefineView() (or in ealier stage) , but there are other
> objects that can contain ENR in their definition, for examle, functions,
> cursor, or RLS policies. Is it worth introducing this version of error
> message for all these objects?
>
> Regards,
> Yugo Nagata
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
Hi!

There are review comments that need to be addressed.

Commitfest status is now waiting on the author.

[0] https://www.postgresql.org/message-id/ZzrHUEaWB67EAZpW%40paquier.xyz
[1] https://www.postgresql.org/message-id/222722.1732124596%40sss.pgh.pa.us

--
Best regards,
Kirill Reshke