Skip to content

Scheduling

Scheduling is a named entity, not columns on the registry. pgauto_mv.schedules holds one row per named schedule; materialised_views.schedule_id is a nullable FK into it (ON DELETE RESTRICT). A schedule is either shared (referenced by many MVs) or private (is_private = true, owned by exactly one MV, named _private_mv_<auto_mv_id>, deleted when that MV is unregistered or its schedule cleared).

6.1 The two schedule kinds

schedules.schedule_kind is 'time' or 'interval':

  • 'interval' — a fixed period in interval_seconds. There is no pgrelay.schedule equivalent (the shared type is calendar-based), so pg_auto_mv keeps this kind natively.
  • 'time' — calendar recurrence, stored in schedule_spec pgrelay.schedule[].

schedule_spec is an array, not a single value. Per-day times alone no longer require this: as of pg_relay 1.1.2, pgrelay.on_day_times('mon=0500', 'tue=1730,2000') expresses those in a single value. The array is what allows a schedule to mix recurrence modes — per-day times alongside a month-end slot, say — which no single value of any one mode can express. The domain's own CHECK fires per element, so a malformed day or time is rejected at INSERT, not at dispatch. A CHECK constraint enforces the kind/column consistency in both directions: 'interval' requires interval_seconds > 0 and schedule_spec IS NULL; 'time' requires interval_seconds IS NULL and a non-empty schedule_spec.

6.2 _next_scheduled_at(p_schedule_name, p_from)

Resolves a schedule by name and returns the next slot strictly greater than p_from: - 'interval': p_from + (interval_seconds || ' seconds')::interval - 'time': min(pgrelay.next_run(s, p_from)) across every element of schedule_spec

pgrelay.next_run() searches from p_from + 1 microsecond, so a p_from landing exactly on a slot advances to the next one rather than re-firing the current one. All evaluation is UTC. min() ignores NULLs, so an element with no reachable slot inside pg_relay's 400-day search window simply contributes no candidate.

6.3 _advance_schedule(p_auto_mv_id, p_schedule_name, p_old_queue_id, p_base_time)

Called after every scheduled refresh or skip. In one step: 1. Expires the old queue row: UPDATE pgrelay.queue SET expire_at = now() - interval '1 second' 2. Inserts a new queue row at _next_scheduled_at(p_schedule_name, p_base_time) 3. Updates mv_state.next_scheduled_at and mv_state.scheduled_queue_id

If _next_scheduled_at() returns NULL both state columns are cleared instead. Because each MV keeps its own mv_state row, a shared schedule advances each attached MV independently.

6.4 Schedule CRUD

Function Purpose
create_mv_schedule(p_name, p_schedule, p_interval_hours, p_interval_minutes, p_is_private, p_owner_auto_mv_id) Create a named schedule. p_schedule (a pgrelay.schedule[]) and the interval parameters are mutually exclusive; exactly one mode is required.
set_mv_schedule_day(p_name, p_dow, p_times) Replace the times for one day, leaving other days untouched. p_times = NULL clears that day.
update_mv_schedule(p_name, p_new_name, p_schedule, p_interval_hours, p_interval_minutes) Rename, or replace the whole spec / interval. Kind is immutable.
delete_mv_schedule(p_name) Delete; raises if any MV still references it.
_get_mv_schedule(p_name) Internal. Returns the definition as jsonb for get_mv().

set_mv_schedule_day() decomposes and recomposes the array via pgrelay.parse_dow(): an element covering 'mon,wed,fri' when Wednesday is being replaced is rewritten to 'mon,fri' rather than discarded, so the untargeted days keep their original times. It raises rather than clearing the last remaining day, since the table CHECK requires a 'time' schedule to retain at least one element — delete_mv_schedule() is the way to remove a schedule entirely.

6.5 Schedule on register_mv() and update_mv()

register_mv() accepts three mutually exclusive schedule modes: p_schedule_name attaches an existing (typically shared) schedule; p_schedule builds an inline private 'time' schedule; p_schedule_interval_hours/p_schedule_interval_minutes build an inline private 'interval' schedule. Supplying none registers the MV with schedule_id = NULL. When a schedule is attached it computes the first next_scheduled_at and inserts the initial queue row.

update_mv() acquires FOR UPDATE on mv_state before touching the schedule, expires the old queue row, and inserts a new one. p_schedule_name re-attaches; p_clear_schedule = true detaches, deleting the schedule if it was private, and clears both state columns without inserting a new queue row.