Re: EphemeralNamedRelation and materialized view

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>