Schema and Data Model¶
3.1 Composite Types¶
pgauto_mv.watch_type — input type for register() and update(). Constructed and validated by pgauto_mv.watch(), never built directly.
Schedules use pgrelay.schedule, the shared recurrence type published with pg_relay 1.1.2 — pg_auto_mv defines no schedule type of its own. It is a validating domain over the composite pgrelay.schedule_type, built with the pg_relay constructors (pgrelay.on_dow(), pgrelay.daily(), pgrelay.weekdays(), …) and evaluated with pgrelay.next_run().
The type is not part of the pg_relay extension and is not in the pg_relay release tarball: it ships in the pg_relay repository under schedule/ and is installed manually by a superuser after CREATE EXTENSION pg_relay. pg_auto_mv--1.0.sql opens with a DO block that checks for the domain and fails with an actionable message if it is absent, because requires = 'pg_relay' in the control file cannot express a dependency on a non-extension object.
Full type documentation: https://pg-relay.pebbleit.com.au/latest/schedule-type/01-overview/
3.2 Registry Table: pgauto_mv.materialised_views¶
One row per managed MV. The primary key is auto_mv_id uuid (generated via gen_random_uuid()). The physical identity of the MV is stored as mv_oid oid — the pg_class.oid at registration time. This is the stable, rename-resistant identifier: mv_schema and mv_name are cached display values reconciled on OID drift.
Timing parameters — refresh_lag, max_wait, cooldown — are stored as double precision seconds. They are never stored as interval. Conversion to interval happens at point of use in mv_state_v via (value || ' seconds')::interval.
Scheduling is not stored on this table. schedule_id uuid is a nullable FK into pgauto_mv.schedules (ON DELETE RESTRICT); a registered MV has at most one schedule attached. See §6.
sync_after_refresh boolean controls whether _process_pending() queues a per-MV catalog sync via pg_auto_mv.sync_mv after each successful refresh.
The UNIQUE (mv_oid) constraint prevents double-registration.
3.3 Watch Tables: pgauto_mv.watch_tables¶
One row per (auto_mv, watch table) pair. Stores three boolean event flags: watch_insert, watch_update, watch_delete. There is no watch_truncate column — TRUNCATE is always included when watch_delete = true. This coupling is enforced in _create_triggers().
3.4 Operational State: pgauto_mv.mv_state¶
One row per managed MV. Stores only raw timestamps and flags — no derived or computed values. All timing calculations are done in mv_state_v.
Key raw fields:
- is_pending: true when at least one unprocessed DML event exists
- refresh_source: the origin of the current pending refresh — 'event' (DML trigger), 'chain' (chain propagation), or 'direct' (refresh_materialised_view_now() call); reset to 'event' after each refresh
- first_event_at, latest_event_at: clock_timestamp() of first and most recent events in the current pending window
- last_refresh_started, last_refresh_completed: used to detect in-progress refreshes and compute cooldown_until
- next_scheduled_at, scheduled_queue_id: schedule tracking
- direct_refresh_at: set by refresh_materialised_view_now() to the call timestamp; causes mv_state_v to override refresh_due_at to bypass refresh_lag and max_wait while still honouring cooldown; cleared after each refresh attempt
All PL/pgSQL code reads timing state from mv_state_v, never from mv_state directly.
3.5 Computed View: pgauto_mv.mv_state_v¶
A JOIN of mv_state and materialised_views. Provides all derived timing columns:
| Column | Formula |
|---|---|
max_refresh_time |
first_event_at + max_wait (NULL when max_wait = 0) |
latest_refresh_time |
latest_event_at + refresh_lag |
due_at |
LEAST(max_refresh_time, latest_refresh_time) |
cooldown_until |
last_refresh_completed + cooldown |
refresh_due_at |
GREATEST(COALESCE(cooldown_until, '-infinity'), due_at) — see note on direct-refresh override |
LEAST() with a NULL argument ignores the NULL, so when max_wait = 0 (and max_refresh_time IS NULL), due_at collapses to latest_refresh_time.
refresh_due_at is the single value _process_pending() compares against now(). It is NULL when is_pending = false (no events recorded yet).
Direct-refresh override: when mv_state.direct_refresh_at IS NOT NULL, the view overrides refresh_due_at to GREATEST(COALESCE(cooldown_until, '-infinity'), direct_refresh_at). This bypasses refresh_lag and max_wait (the MV is due now) while still honouring cooldown (if the MV is in cooldown the refresh is deferred until cooldown_until). This override is set by refresh_materialised_view_now() and cleared by _process_pending() after any exit path.
3.6 Event Log: pgauto_mv.mv_events¶
One row per trigger firing or direct-refresh call. Uses clock_timestamp(). The action column values are:
action |
operation |
Source |
|---|---|---|
'opened' |
INSERT / UPDATE / DELETE / TRUNCATE |
First DML event opening a pending window |
'updated' |
INSERT / UPDATE / DELETE / TRUNCATE |
Subsequent DML event in an already-pending window |
'direct' |
'NONE' |
refresh_materialised_view_now() call; watch_schema and watch_table are empty strings |
NONE is the only operation value that does not represent a DML statement. The NOT NULL constraint is preserved — direct calls supply '' for watch_schema and watch_table.
3.7 Refresh Log: pgauto_mv.mv_refresh_log¶
One row per refresh attempt (successful, failed, or skipped). When a scheduled slot is consumed without actually refreshing, skip_reason is set and success is true (the slot was processed correctly — it just had nothing to do).
The refresh_source column records what initiated the refresh:
| Value | Trigger |
|---|---|
'event' |
DML trigger on a watch table |
'schedule' |
Scheduled slot (next_scheduled_at reached) |
'chain' |
Chain propagation from a parent MV's refresh |
'direct' |
refresh_materialised_view_now() call |
Valid skip_reason values:
| Value | Condition |
|---|---|
inactive |
is_active = false — scheduled queue is killed, not advanced |
mv_missing |
OID no longer in pg_class as relkind = 'm' |
in_cooldown |
now() < cooldown_until |
refresh_in_progress |
last_refresh_started > last_refresh_completed |
The scheduled path checks all four; the event-driven path checks none of them (the timing gate in mv_state_v handles cooldown implicitly via refresh_due_at). An unpopulated MV never produces a skip_reason for any dispatch type — see §5.2 below: a concurrent = true MV that is still unpopulated always silently downgrades to a non-concurrent refresh instead.
3.8 Audit History: pgauto_mv.mv_audit_history¶
Lifecycle events: REGISTER, UPDATE, UNREGISTER. The definition column is a jsonb snapshot of the registry row at operation time. On UNREGISTER, auto_mv_id is NULL (the registry row is already deleted); schema/name are preserved from the pre-deletion snapshot.
3.9 TRUNCATE Signal Bridge: pgauto_mv.mv_truncate_signals¶
The one table that is the exception to the node-local rule. In multi-master deployments, statement-level triggers cannot fire during logical apply (a categorical PostgreSQL constraint, not overridable with ENABLE ALWAYS). This table bridges the gap: the origin-node TRUNCATE trigger inserts a row here; the row replicates; the ENABLE ALWAYS row-level trigger pgauto_mv_on_truncate_signal fires on each replica and updates local mv_state. Signal rows are deleted by _process_pending() after each refresh attempt.
3.10 Refresh Chains: pgauto_mv.mv_chains¶
A pair table (parent_mv_id, child_mv_id) that defines a linear refresh sequence. The PRIMARY KEY (parent_mv_id) and UNIQUE (child_mv_id) constraints together enforce a strict linked-list topology — each MV has at most one child and at most one parent. CHECK (parent_mv_id <> child_mv_id) prevents self-loops.
Chains of arbitrary length are possible by chaining multiple pairs: A→B, B→C produces a three-node chain. chain_mv() uses a recursive CTE to detect if the proposed link would create a cycle before inserting.
refresh_on_error boolean controls whether the chain propagates even when the parent refresh raises an error.
3.11 Sync Log: pgauto_mv.sync_log¶
One row per bulk run of sync_mv() (per-MV calls are not logged here). Used for the 1-hour cooldown on bulk catalog scans. Fields include counts of mvs_checked, mvs_deactivated, mvs_reconciled written on completion.