Re: Cannot create matview when referencing another not-populated-yet matview in subquery

Laurent Sartran <lsartran(at)gmail(dot)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