Chaining materialised views

A chain causes one view to refresh immediately after another finishes. This is useful when views are layered — a summary view that reads from a detail view should always refresh after the detail view refreshes.

-- After daily_revenue refreshes, weekly_summary refreshes automatically
SELECT pgauto_mv.chain_mv('reporting.daily_revenue', 'reporting.weekly_summary');

Both views must be registered before you can chain them. The second view does not need watch tables — the chain provides its trigger. Nor does it need its own Schedule, when the first view is refreshed on a schedule, the chaining will ensure the chained view is then refreshed.

-- Register the downstream view without watch tables
SELECT pgauto_mv.register_mv(
    p_mv_name      => 'weekly_summary',
    p_mv_schema    => 'reporting',
    p_watch_tables => NULL,    -- no triggers; driven by the chain
    p_cooldown     => 60.0
);

SELECT pgauto_mv.chain_mv('reporting.daily_revenue', 'reporting.weekly_summary');

Chains can span multiple levels:

SELECT pgauto_mv.chain_mv('reporting.daily_revenue',  'reporting.weekly_summary');
SELECT pgauto_mv.chain_mv('reporting.weekly_summary', 'reporting.monthly_report');

After daily_revenue refreshes, weekly_summary refreshes, and then monthly_report refreshes. Each step happens only when the previous step completes successfully.

Finding base tables:

Before deciding which tables to watch, use get_view_tables() to see every base table your view reads:

SELECT schema_name, table_name
FROM pgauto_mv.get_view_tables('reporting', 'weekly_summary');
-- Shows: sales.orders (via daily_revenue → orders)

Confirming a chain:

SELECT
    parent.mv_name AS refreshes_first,
    child.mv_name  AS then_refreshes
FROM pgauto_mv.mv_chains c
JOIN pgauto_mv.materialised_views parent ON parent.auto_mv_id = c.parent_mv_id
JOIN pgauto_mv.materialised_views child  ON child.auto_mv_id  = c.child_mv_id;

Removing a chain:

SELECT pgauto_mv.unchain_mv('reporting.daily_revenue');

This removes the chain link from daily_revenue. It does not affect weekly_summary's registration — weekly_summary remains registered but will no longer refresh automatically after daily_revenue. Nor does it impact weeky_summary's chain.

---