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