Обсуждение: Cannot create matview when referencing another not-populated-yet matview in subquery
Cannot create matview when referencing another not-populated-yet matview in subquery
От
Laurent Sartran
Дата:
Hello,
I observe the following behavior with PostgreSQL 9.3.0 when I create a materialized view with the no data option, which references in a subquery another materialized view also created with the no data option:
(ls@[local]:5432) [ls] > CREATE MATERIALIZED VIEW t1 AS SELECT text 'foo' AS col1 WITH NO DATA;
SELECT 0
(ls@[local]:5432) [ls] > CREATE MATERIALIZED VIEW t2a AS SELECT * FROM t1 WITH NO DATA;
SELECT 0
(ls@[local]:5432) [ls] > CREATE MATERIALIZED VIEW t2b AS SELECT * FROM t1 WHERE col1 = (SELECT LEAST(col1) FROM t1) WITH NO DATA;
ERROR: materialized view "t1" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
I do not understand why creating t2b would require t1 to be populated, as its schema is fully defined, just like t2a's. Is this behavior expected?
The following workaround seems to work fine:
(ls@[local]:5432) [ls] > CREATE FUNCTION f() RETURNS text AS $$ SELECT LEAST(col1) FROM t1 $$ LANGUAGE sql;
CREATE FUNCTION
(ls@[local]:5432) [ls] > CREATE MATERIALIZED VIEW t2c AS SELECT * FROM t1 WHERE col1 = f() WITH NO DATA;
SELECT 0
Kind regards,
Laurent Sartran
Re: Cannot create matview when referencing another not-populated-yet matview in subquery
От
Kevin Grittner
Дата:
Laurent Sartran <lsartran@gmail.com> wrote: > CREATE MATERIALIZED VIEW t1 AS SELECT text 'foo' AS col1 > WITH NO DATA; > CREATE MATERIALIZED VIEW t2b AS SELECT * FROM t1 > WHERE col1 = (SELECT LEAST(col1) FROM t1) > WITH NO DATA; > > ERROR: materialized view "t1" has not been populated > HINT: Use the REFRESH MATERIALIZED VIEW command. > Is this behavior expected? No, and git bisect shows that it worked until commit 5194024d72f33fb209e10f9ab0ada7cc67df45b7. Moving to -hackers list for discussion of how to fix it. It looks like the above commit missed a trick here: diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 791f336..0b47106 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -865,7 +865,8 @@ InitPlan(QueryDesc *queryDesc, int eflags) * it is a parameterless subplan (not initplan), we suggest that it be * prepared to handle REWIND efficiently; otherwise there is no need. */ - sp_eflags = eflags & EXEC_FLAG_EXPLAIN_ONLY; + sp_eflags = eflags + & (EXEC_FLAG_EXPLAIN_ONLY | EXEC_FLAG_WITH_NO_DATA); if (bms_is_member(i, plannedstmt->rewindPlanIDs)) sp_eflags |= EXEC_FLAG_REWIND; The test case provided works with this change. Does anyone see a problem with that? If not, I'll push it with the above test case added to the regression tests. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company