Thread: automatically refresh all materialized views?
Is there a way to automatically refresh all materialized views, preferably in order of dependendency?
I should be able to chase pg_depends entries to create this ordering, right?
Thanks,
Reece
On Fri, Feb 14, 2014 at 11:26 AM, Reece Hart <reece@harts.net> wrote: > Is there a way to automatically refresh all materialized views, preferably > in order of dependendency? > > I should be able to chase pg_depends entries to create this ordering, right? yeah -- you could do this with some gymnastics and some dynamic SQL. If I were lazy (check), I would just encode the order in the name of the view somehow. merlin
On Fri, Feb 14, 2014 at 10:15 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
yeah -- you could do this with some gymnastics and some dynamic SQL.
If I were lazy (check), I would just encode the order in the name of
the view somehow.
Thanks. That's exactly what I do already. Apparently, I'm even lazier than you!
In case anyone out there is looking for a convention, here's one that I find useful: _v for views intended for interactive queries, _dv (defining views) for views that are too slow to use interactively, and _mv for the materialized _dv views.
On Fri, Feb 14, 2014 at 7:26 PM, Reece Hart <reece@harts.net> wrote: > I should be able to chase pg_depends entries to create this ordering, right? Not always, there may be circular dependencies between them. Regards, Marti
Marti Raudsepp <marti@juffo.org> wrote: > Reece Hart <reece@harts.net> wrote: > >> I should be able to chase pg_depends entries to create this >> ordering, right? > > Not always, there may be circular dependencies between them. 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? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Kevin Grittner <kgrittn@ymail.com> writes:
> Marti Raudsepp <marti@juffo.org> wrote:
>> Not always, there may be circular dependencies between them.
> 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?
It'd be pretty trivial if we had CREATE OR REPLACE MATERIALIZED VIEW.
Which I assume is not there only for lack of round tuits, and not
because you deliberately intend that it will never be implemented.
regards, tom lane
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