Re: EphemeralNamedRelation and materialized view

On Fri, 26 Jul 2024 at 12:07, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
>
> Hi,
>
> While looking into the commit b4da732fd64e936970f38c792f8b32c4bdf2bcd5,
> I noticed that we can create a materialized view using Ephemeral Named
> Relation in PostgreSQL 16 or earler.
>
>
> postgres=# create table tbl (i int);
> CREATE TABLE
> ^
> postgres=# create or replace function f() returns trigger as $$ begin
> create materialized view mv as select * from enr; return new; end; $$ language plpgsql;
> CREATE FUNCTION
>
> postgres=# create trigger trig after insert on tbl referencing new table as enr execute function f();
> CREATE TRIGGER
>
> postgres=# insert into tbl values (10);
>
> postgres=# \d
> List of relations
> Schema | Name | Type | Owner
> --------+------+-------------------+--------
> public | mv | materialized view | yugo-n
> public | tbl | table | yugo-n
> (2 rows)
>
>
> We cannot refresh or get the deinition of it, though.
>
> postgres=# refresh materialized view mv;
> ERROR: executor could not find named tuplestore "enr"
>
> postgres=# \d+ mv
> ERROR: unrecognized RTE kind: 7
>
> In PostgreSQL 17, materialized view using ENR cannot be created
> because queryEnv is not pass to RefreshMatViewByOid introduced by b4da732fd64.
> When we try to create it, the error is raised.
>
> ERROR: executor could not find named tuplestore "enr"
>
> Although it is hard to imagine users actually try to create materialized view
> using ENR, how about prohibiting it even in PG16 or earlier by passing NULL
> as queryEnv arg in CreateQueryDesc to avoid to create useless matviews accidentally,
> as the attached patch?
>
>
> Regards,
> Yugo Nagata
>
> --
> Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>

Hi
I think this is a clear bug fix, and should be backported in pg v12-v16.
LTGM

P.S should be set https://commitfest.postgresql.org/49/5153/ entry as RFC?

--
Best regards,
Kirill Reshke