The Catalog Sync Procedure

pgauto_mv.sync_mv(p_mv_schema, p_mv_name, p_auto_mv_id, _p_commit) → void (PROCEDURE)

Implemented as a PROCEDURE (not FUNCTION) because it issues COMMIT after each MV iteration to release locks during long bulk scans. The _p_commit boolean parameter (default true) allows calling from within a transaction context (e.g. test DO blocks) by suppressing the COMMIT calls.

Bulk path (when p_auto_mv_id IS NULL): - Checks sync_log for a completed run within the last hour; returns early if found (cooldown enforcement) - Inserts a sync_log row, COMMITs, then iterates all matching MVs

Per-MV path (when p_auto_mv_id IS NOT NULL): - Bypasses the cooldown check entirely — intended for sync_after_refresh = true use where per-MV syncs should not be throttled - Does not write to sync_log

For each MV, resolves the actual (nspname, relname) from pg_class using mv_oid. Three outcomes: 1. OID missing from pg_class: if is_active = true, sets is_active = false and inserts an UPDATE row in mv_audit_history 2. OID found but name/schema differs: updates mv_schema/mv_name in registry, inserts an UPDATE audit row with a reconciliation note 3. OID found, name matches: no-op

The procedure is registered with pg_relay on the pg_auto_mv.sync_mv channel. pg_relay calls CALL pgauto_mv.sync_mv(p_auto_mv_id => NULLIF($1, '')::uuid). The NULLIF converts an empty-string payload (bulk run) to NULL.

---