Complete example

This end-to-end example covers the main features from registration through chaining and cleanup.

-- ============================================================
-- Create and populate source tables and materialised views
-- ============================================================

CREATE MATERIALIZED VIEW reporting.daily_revenue AS
SELECT
    date_trunc('day', o.created_at) AS day,
    sum(o.total_amount)             AS revenue,
    count(*)                        AS order_count
FROM sales.orders o
WHERE o.status = 'paid'
GROUP BY 1;

CREATE UNIQUE INDEX ON reporting.daily_revenue (day);
REFRESH MATERIALIZED VIEW reporting.daily_revenue;

CREATE MATERIALIZED VIEW reporting.weekly_summary AS
SELECT
    date_trunc('week', day) AS week,
    sum(revenue)            AS weekly_revenue,
    sum(order_count)        AS weekly_orders
FROM reporting.daily_revenue
GROUP BY 1;

CREATE UNIQUE INDEX ON reporting.weekly_summary (week);
REFRESH MATERIALIZED VIEW reporting.weekly_summary;


-- ============================================================
-- Register daily_revenue: watch orders, tune for busy table
-- ============================================================

SELECT pgauto_mv.register_mv(
    p_mv_name      => 'daily_revenue',
    p_mv_schema    => 'reporting',
    p_watch_tables => ARRAY[
        pgauto_mv.mv_watch('sales.orders')
    ],
    p_refresh_lag  => 10.0,
    p_max_wait     => 20.0,
    p_cooldown     => 30.0
);


-- ============================================================
-- Add a nightly schedule as a safety net
-- ============================================================

SELECT pgauto_mv.create_mv_schedule(
    p_name     => 'daily_0530',
    p_schedule => ARRAY[pgrelay.daily('05:30')]
);

SELECT pgauto_mv.update_mv(
    p_mv_schema     => 'reporting',
    p_mv_name       => 'daily_revenue',
    p_schedule_name => 'daily_0530'
);


-- ============================================================
-- Register weekly_summary and chain it to daily_revenue
-- ============================================================

SELECT pgauto_mv.register_mv(
    p_mv_name      => 'weekly_summary',
    p_mv_schema    => 'reporting',
    p_watch_tables => NULL,     -- no triggers; driven by chain
    p_cooldown     => 2.0
);

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


-- ============================================================
-- Insert some data and watch the refresh happen
-- ============================================================

INSERT INTO sales.orders (customer_id, total_amount, status, created_at)
VALUES (42, 150.00, 'completed', now());

-- Wait a few seconds, then check:
SELECT mv_name, is_pending, last_refresh_completed, duration_ms
FROM pgauto_mv.get_mv_status();


-- ============================================================
-- Pause for a maintenance window, then resume
-- ============================================================

SELECT pgauto_mv.update_mv('reporting', 'daily_revenue', p_is_active => false);
-- ... maintenance work ...
SELECT pgauto_mv.update_mv('reporting', 'daily_revenue', p_is_active => true);


-- ============================================================
-- Trigger an on-demand refresh after a critical data load
-- ============================================================

INSERT INTO products.pricing SELECT * FROM staging.pricing_import;
SELECT pgauto_mv.refresh_materialised_view_now('reporting', 'daily_revenue');


-- ============================================================
-- Check for problems
-- ============================================================

SELECT * FROM pgauto_mv.get_mv_problems();


-- ============================================================
-- Clean up — remove chain, then unregister
-- ============================================================

SELECT pgauto_mv.unchain_mv('reporting.daily_revenue');
SELECT pgauto_mv.unregister_mv('reporting', 'weekly_summary');
SELECT pgauto_mv.update_mv('reporting', 'daily_revenue', p_clear_schedule => true);
SELECT pgauto_mv.delete_mv_schedule('daily_0530');
SELECT pgauto_mv.unregister_mv('reporting', 'daily_revenue');

-- The views still exist with their data — only the auto-refresh management was removed

---