Re: EphemeralNamedRelation and materialized view

On Fri, 26 Jul 2024 at 12:07, Yugo Nagata <nagata@sraoss.co.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@sraoss.co.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