Skip to content

Public API Functions

pgauto_mv.watch(p_table_ref text, p_events text DEFAULT 'INSERT,UPDATE,DELETE') → watch_type

Constructs and validates a watch_type value. Validates: - p_table_ref matches schema.table pattern - The table exists in pg_class - Events are a non-empty subset of {INSERT, UPDATE, DELETE} - TRUNCATE is rejected with an explanatory message directing the caller to use DELETE

Events are uppercased and deduplicated before being stored in the events field.

pgauto_mv.register_mv(p_mv_name, p_mv_schema, p_watch_tables, p_refresh_lag, p_max_wait, p_cooldown, p_concurrent, p_fire_on_replica, p_populate_if_empty, p_schedule_name, p_schedule, p_schedule_interval_hours, p_schedule_interval_minutes, p_auto_mv_id) → uuid

Registers an existing MV. pg_auto_mv does not create the MV. Steps:

  1. Validates that at most one schedule mode is supplied (p_schedule_name / p_schedule / p_schedule_interval_*)
  2. Looks up MV in pg_class — raises if not found or not relkind = 'm'
  3. Validates concurrent = true requires a unique index (pg_index.indisunique)
  4. Uses p_auto_mv_id if supplied (multi-master use case); otherwise gen_random_uuid()
  5. Inserts into materialised_views (including populate_if_empty, default true)
  6. Iterates p_watch_tables, parses event flags, inserts into watch_tables
  7. Inserts initial row into mv_state
  8. Calls _create_triggers()
  9. Population check: p_populate_if_empty is NULL-guarded to the documented default (true) if passed explicitly as NULL. If the resolved value is true, the MV is unpopulated (pg_matviews.ispopulated = false), and the calling role holds EXECUTE on refresh_materialised_view_now(text, text) directly, calls refresh_materialised_view_now() internally — the identical mechanism described in §"refresh_materialised_view_now()" below, including its fire-and-forget semantics. The privilege check exists because register_mv() is SECURITY DEFINER: without it, any role granted EXECUTE on register_mv() would silently inherit the bypass that refresh_materialised_view_now()'s REVOKE ... FROM PUBLIC is meant to restrict (see DBA_GUIDE.md §2). Pass p_populate_if_empty => false to skip this (e.g. the bulk-load pattern in that section, where the MV should stay empty until the manual post-load call).
  10. If schedule configured: inserts queue row, updates mv_state
  11. Inserts REGISTER row into mv_audit_history with a row_to_json() snapshot

Returns auto_mv_id. The UUID is the primary handle for all subsequent operations.

pgauto_mv.unregister(p_mv_schema text, p_mv_name text) → void

  1. Snapshots the registry row to JSONB
  2. Expires active scheduled queue row (expire_at = now() - interval '1 second')
  3. Calls _drop_triggers()
  4. Inserts UNREGISTER row into mv_audit_history (with snapshot, NULL auto_mv_id)
  5. DELETE FROM materialised_views — cascades to mv_state, watch_tables, mv_events, mv_refresh_log, mv_chains

Does not touch the MV itself.

pgauto_mv.update_mv(p_mv_schema, p_mv_name, p_refresh_lag, p_max_wait, p_cooldown, p_concurrent, p_fire_on_replica, p_is_active, p_populate_if_empty, p_schedule_name, p_clear_schedule, p_watch_tables) → void

COALESCE semantics: NULL parameters retain their existing value.

p_populate_if_empty updates the stored registry flag only. It is consulted solely by register() at registration time — changing it via update() has no immediate effect and does not itself trigger a refresh of an already-registered MV.

p_is_active and schedule interaction:

  • p_is_active = false: pauses all refreshes. Events continue to be recorded. Does not touch the schedule queue or config directly (the schedule is killed lazily on the next scheduled dispatch by _process_pending()).
  • p_is_active = true: resumes refreshes. If schedule_id is set in the registry and mv_state.next_scheduled_at IS NULL (schedule was killed while inactive), a new queue row is inserted automatically — the user does not need to re-supply the schedule parameters.

Triggers are only recreated when fire_on_replica or p_watch_tables change. Changes to refresh_lag, max_wait, and cooldown do not require trigger recreation because those values are computed at query time in mv_state_v.

When p_watch_tables is supplied: drops all existing watch_tables rows for the auto_mv, inserts new rows from the array, calls _drop_triggers() then _create_triggers().

pgauto_mv.status() → TABLE(...)

Returns one row per registered auto_mv. Sources timing columns from mv_state_v. Joins to mv_refresh_log (lateral, most recent row) for duration_ms. Aggregates watch_tables into a text[] of schema.table strings.

pgauto_mv.get_mv(p_mv_name, p_mv_schema, p_mv_oid) → TABLE(...)

Returns 0 or 1 rows. Lookup precedence: OID > name+schema. Case-insensitive name matching via lower(). Returns 0 rows (no exception) when not found. Raises if neither parameter is supplied.

watch_tables is returned as a jsonb array with one element per watch table row (including boolean event flags). state is a jsonb object containing all fields from mv_state_v for this auto_mv_id. Timestamps are serialised as ISO 8601 strings via PostgreSQL's default timestamptz::text cast.

pgauto_mv.list_mv(p_schema, p_status, p_include_missing) → TABLE(...)

p_status controls which rows are returned:

  • 'ok': registered MVs whose mv_oid resolves in pg_class
  • 'invalid': registered MVs whose mv_oid is absent from pg_class
  • 'missing': MVs in pg_matviews not in the registry (forces p_include_missing = true)
  • 'all': all three categories

For 'missing' rows, only mv_oid, mv_schema, mv_name, is_populated, and status are populated; all other columns are NULL. The pgauto_mv schema is always excluded from the missing scan. Raises for any unknown p_status value.

pgauto_mv.refresh_materialised_view_now(p_mv_schema text, p_mv_name text) → void

Requests an immediate refresh of a registered MV outside the normal trigger/schedule flow. This is the mechanism for operator-initiated refreshes, bulk-load patterns, and any case where the application owns the refresh timing rather than pg_auto_mv.

Use cases:

  • Bulk data load without triggers. Register the MV with p_watch_tables => NULL (no triggers) and p_populate_if_empty => false (so an empty MV isn't refreshed before the load runs). After the load, call refresh_materialised_view_now(). Optionally attach a schedule for ongoing automatic refreshes from that point onwards.
  • Complement to schedule. An MV refreshes every 10 minutes on a schedule. A one-off large import arrives between slots. Call refresh_materialised_view_now() to pull it forward without waiting for the next scheduled slot. The schedule continues from its own next computed slot — the direct call does not advance it.
  • Application-controlled logic. The application knows when a significant state change has occurred (e.g. after a batch job completes). It calls refresh_materialised_view_now() rather than relying on row-level triggers.

Behaviour:

  1. Raises EXCEPTION if the MV is not in the registry.
  2. Inserts a mv_events row with operation = 'NONE', action = 'direct', watch_schema = '', watch_table = ''.
  3. Sets mv_state:
  4. is_pending = true
  5. refresh_source = 'direct'
  6. direct_refresh_at = clock_timestamp()
  7. first_event_at = COALESCE(first_event_at, clock_timestamp()) — preserved if a trigger event is already pending
  8. latest_event_at = clock_timestamp()
  9. Calls pgrelay.notify() with run_at = now() and expire_at = now() + GREATEST(max_wait, cooldown, 60) seconds. deduplicate = true prevents queue pile-up from rapid successive calls.

Timing semantics:

  • refresh_lag and max_wait are bypassed — mv_state_v overrides refresh_due_at to GREATEST(cooldown_until, direct_refresh_at) when direct_refresh_at IS NOT NULL.
  • cooldown is honoured — if the MV is in cooldown, the refresh is deferred until cooldown_until. pg_relay re-dispatches the deferred queue row at that time.
  • The schedule (if any) is not advanced — a direct refresh between scheduled slots does not alter the schedule cadence.
  • Chained MVs are refreshed after a successful direct refresh, exactly as they would be after a trigger- or schedule-driven refresh.

Access control:

EXECUTE is revoked from PUBLIC at install time. The owning organisation must explicitly grant access to any role that should be permitted to call this function:

GRANT EXECUTE ON FUNCTION pgauto_mv.refresh_materialised_view_now(text, text) TO your_app_role;

Example — bulk load pattern:

-- Register with no triggers; schedule for ongoing maintenance.
-- p_populate_if_empty => false: the MV is still empty at registration time (the bulk
-- load hasn't run yet) — skip the automatic populate-on-register so the first refresh
-- happens deliberately, after the load, via the explicit call below.
SELECT pgauto_mv.register_mv(
    p_mv_name                   => 'daily_totals',
    p_mv_schema                 => 'reporting',
    p_watch_tables              => NULL,
    p_populate_if_empty         => false,
    p_schedule_interval_minutes => 60.0
);

-- ... bulk load runs ...
INSERT INTO sales.orders SELECT ... FROM staging.orders;

-- Operator (or application) triggers the refresh immediately after load
SELECT pgauto_mv.refresh_materialised_view_now('reporting', 'daily_totals');
-- pg_relay picks this up within 1 second and refreshes.
-- The hourly schedule continues independently from its next slot.

pgauto_mv.chain_mv(p_from_mv text, p_to_mv text) → void

Both arguments in schema.table format. Both MVs must already be registered. Enforces: - p_from_mv has no existing child - p_to_mv has no existing parent - Neither is the same MV (self-chain) - A recursive CTE walks the descendants of p_to_mv to detect cycles before inserting into mv_chains

Prepending to an existing chain (A has an existing child C; inserting B→A is valid because B has no child yet, and A is not a descendant of B) is permitted.

pgauto_mv.unchain_mv(p_from_mv text) → void

Deletes the mv_chains row where parent_mv_id matches. No-op if no chain link exists from the named MV. Raises if the MV is not registered.

pgauto_mv.purge(p_auto_mv_id, p_audit_history_days, p_events_days, p_refresh_log_days) → TABLE(table_name text, rows_deleted bigint)

Retention management for the three log/audit tables. Each _days parameter: - -1 (default): skip this table - 0: delete all rows (or all rows for the specified auto_mv_id) - N > 0: delete rows older than N days (cutoff keyed on operation_at, event_at, or refresh_started respectively)

Returns one row per table where deletion was performed. Scoped to a single auto_mv when p_auto_mv_id is supplied.

pgauto_mv.refresh_frequency(p_mv_schema, p_mv_name, p_threshold_seconds) → TABLE(...)

Analysis function over mv_refresh_log. Returns rolling window counts for real refreshes (i.e. success = true AND skip_reason IS NULL): refreshes_15m, refreshes_30m, refreshes_60m, refreshes_240m, refreshes_daily, refreshes_weekly.

Also computes over the 7-day window using LAG(): - avg_interval_secs, min_interval_secs: average and minimum gap between consecutive refreshes - rapid_refreshes: count of consecutive pairs where gap < threshold

Default threshold is cooldown * 1.25 per MV. An explicit p_threshold_seconds overrides this for all MVs in the result. Implemented as a pure SQL function using CTEs to avoid cursor overhead.

pgauto_mv.resolve_base_tables(p_schema text, p_view_name text) → TABLE(schema_name text, table_name text)

Recursively resolves all base tables (relkind IN ('r', 'p')) referenced by a VIEW or MATERIALISED VIEW. Uses pg_rewrite and pg_depend to find dependencies:

SELECT d.refobjid, c.relkind, n.nspname, c.relname
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class c ON c.oid = d.refobjid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE r.ev_class = <current oid>
  AND r.rulename = '_RETURN'
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype = 'n'
  AND d.refobjid <> <current oid>

The rulename = '_RETURN' filter selects only the view-definition rule (every view/MV has a _RETURN rewrite rule that records its dependencies). Other rewrite rules (e.g. DO INSTEAD rules) are excluded.

Traversal uses a manual BFS loop with a v_visited oid[] set for cycle detection and a v_emitted oid[] set to deduplicate output rows. Base tables are emitted once; views and MVs are enqueued for further traversal. Raises if the named object does not exist or is not a view/MV.

Does not use information_schema.view_table_usage, which does not include materialised views.