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