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

From: Laurent Sartran <lsartran(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Cannot create matview when referencing another not-populated-yet matview in subquery
Date: 2013-10-07 14:57:44
Message-ID: CAAaLM5wQBNKQgSb3Qd+woq-+6b2brupVsLXhBapCEiqh7L9LYQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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(at)[local]:5432) [ls] > CREATE MATERIALIZED VIEW t1 AS SELECT text 'foo'
AS col1 WITH NO DATA;
SELECT 0
(ls(at)[local]:5432) [ls] > CREATE MATERIALIZED VIEW t2a AS SELECT * FROM t1
WITH NO DATA;
SELECT 0
(ls(at)[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(at)[local]:5432) [ls] > CREATE FUNCTION f() RETURNS text AS $$ SELECT
LEAST(col1) FROM t1 $$ LANGUAGE sql;
CREATE FUNCTION
(ls(at)[local]:5432) [ls] > CREATE MATERIALIZED VIEW t2c AS SELECT * FROM t1
WHERE col1 = f() WITH NO DATA;
SELECT 0

Kind regards,

Laurent Sartran

Responses

Browse pgsql-general by date

  From Date Subject
Next Message sunpeng 2013-10-07 15:02:28 Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL?
Previous Message Ovid 2013-10-07 14:51:11 Altering parent table breaks child table defaults

Browse pgsql-hackers by date

  From Date Subject
Next Message David Fetter 2013-10-07 15:43:27 Re: old warning in docs
Previous Message Atri Sharma 2013-10-07 14:56:49 Re: Re: custom hash-based COUNT(DISTINCT) aggregate - unexpectedly high memory consumption