Re: automatically refresh all materialized views?

On Tue, Mar 11, 2014 at 9:17 PM, Kevin Grittner <kgrittn@ymail.com> 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