Search Postgresql Archives

Re: automatically refresh all materialized views?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Mar 11, 2014 at 9:17 PM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:
> I haven't been able to think of a way to create circular references
> among a set of materialized views, short of committing violence
> against the system catalog tables directly. What have I missed?

Not directly, but you can create circles with matviews selecting from
ordinary views:

create view v_a as select 1 i;
create materialized view m_a as select * from v_a;
create view v_b as select * from m_a;
create materialized view m_b as select * from v_b;
create or replace view v_a as select * from m_b;  -- Cha-ching!

You probably won't hit them if you don't recurse into views in
pg_depends, but then you might miss some necessary dependencies.

I didn't really give this much thought though. Such a setup might not
be refresh'able after a restore because you run into a recursive
"materialized view "x" has not been populated". (Maybe it is possible
if the recursive reference occurs in a subquery that isn't scanned?
dunno)

Now that I tried it, pg_dump has this to say:

pg_dump: [sorter] WARNING: could not resolve dependency loop among these items:
pg_dump: [sorter]   REFRESH MATERIALIZED VIEW m_a  (ID 1971 OID 18834835)
pg_dump: [sorter] WARNING: could not resolve dependency loop among these items:
pg_dump: [sorter]   REFRESH MATERIALIZED VIEW m_b  (ID 1972 OID 18834843)
pg_dump: [sorter] WARNING: could not resolve dependency loop among these items:
pg_dump: [sorter]   REFRESH MATERIALIZED VIEW m_a  (ID 1971 OID 18834835)
pg_dump: [sorter]   REFRESH MATERIALIZED VIEW m_b  (ID 1972 OID 18834843)

Regards,
Marti


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux