Skip to content

Catalog synchronisation

sync_mv() reconciles the pg_auto_mv registry against the actual database catalog. It handles:

  • Renamed MVs — updates mv_schema/mv_name when the OID is still valid but the name has changed.
  • Dropped MVs — deactivates (is_active = false) registrations whose OID no longer exists.
  • Missing unique indexes — marks can_build_concurrently = false for concurrent-mode registrations that have lost their unique index.

Running a manual sync

Sync all registered MVs:

CALL pgauto_mv.sync_mv();

Sync one specific MV by auto_mv_id:

CALL pgauto_mv.sync_mv(
    p_auto_mv_id => 'your-uuid-here'::uuid
);

Viewing sync history

SELECT sync_started, mvs_checked, mvs_deactivated, mvs_reconciled
FROM pgauto_mv.sync_log
ORDER BY sync_id DESC
LIMIT 10;

Scheduled sync via pg_relay

pg_auto_mv registers a daily sync_mv() job on the pg_auto_mv.sync_mv channel at install time. pg_relay dispatches it automatically. The job has a 1-hour cooldown enforced inside the procedure — bulk sync runs cannot overlap.

Enabling per-MV sync after each refresh

For environments where MV renames are frequent, you can enable per-refresh reconciliation:

UPDATE pgauto_mv.materialised_views
SET sync_after_refresh = true
WHERE mv_schema = 'reporting' AND mv_name = 'daily_revenue';

This is a superuser-level configuration — it is not exposed through the public update_mv() API.