From: | Kirill Reshke <reshkekirill(at)gmail(dot)com> |
---|---|
To: | Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: EphemeralNamedRelation and materialized view |
Date: | 2024-08-10 07:22:00 |
Message-ID: | CALdSSPggoieisPh27sBcnp8u2_OjozstoSAY3DezDCHer3Ozww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John Naylor | 2024-08-10 09:01:18 | Re: Vacuum ERRORs out considering freezing dead tuples from before OldestXmin |
Previous Message | Peter Eisentraut | 2024-08-10 07:10:16 | libpq minor TOCTOU violation |