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
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 |
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 |