Creating and Changing Jobs¶

A scheduled job connects a name, a schedule spec, and something to run — either SQL of its own (the pg_cron way), or one of your registered channels with a payload.

Submitting a job¶

The parameter order deliberately mirrors cron.schedule(jobname, schedule, command), so the simplest form reads exactly like pg_cron:

-- Run this SQL every night at 03:00:
SELECT pgrelay.schedule_job('nightly_report', '0 3 * * *', 'CALL build_nightly_report()');

The full signature:

SELECT pgrelay.schedule_job(
    p_name            := 'nightly_report',   -- the job's identity
    p_schedule        := '0 3 * * *',        -- when (any of the four forms)
    p_payload         := 'CALL f()',         -- the SQL to run — or a channel's $1
    p_channel         := 'pg_relay_adhoc',   -- optional: which channel fires
    p_first_run_at    := NULL,               -- optional: override the first occurrence
    p_expire_at       := NULL,               -- optional: when the schedule ends
    p_run_as          := NULL,               -- ad-hoc only: run the SQL as this role
    p_timeout_seconds := NULL,               -- ad-hoc only: per-run limit (default 300s)
    p_detached        := false               -- ad-hoc only: the long-maintenance lane
);

Parameter	Default	Meaning
`p_name`	—	Case-insensitive job identity, unique among live jobs. `'Nightly_Report'` and `'nightly_report'` are the same job.
`p_schedule`	—	The spec — see Schedule Syntax. Always validated, even when `p_first_run_at` is given.
`p_payload`	`NULL`	On the default channel: the SQL each run executes. On a named channel: the ordinary `$1` payload passed to that channel's action.
`p_channel`	`'pg_relay_adhoc'`	The channel each run fires. The default is the seeded ad-hoc channel (below); name any registered, active channel to fire it instead — validated exactly like `pgrelay.notify()`, an unknown or disabled channel raises immediately.
`p_first_run_at`	`NULL`	The first run defaults to the next occurrence of the spec from now. Supply a timestamp to override it — taken verbatim, so a past value fires on the next Processor tick. Recurrence afterwards follows the spec either way.
`p_expire_at`	`NULL`	The schedule's end date. When it passes, the job's next launch resolves as `expired` and the job is removed. `NULL` = runs forever.
`p_run_as`	`NULL`	Ad-hoc jobs only: run the SQL as this role instead of `pgrelay` — you must hold the role, and the DBA must have run `GRANT <role> TO pgrelay` once.
`p_timeout_seconds`	`NULL`	Ad-hoc jobs only: per-run limit, 1–43200 (12 h). Unset: 300 s for inline jobs, unlimited for detached ones.
`p_detached`	`false`	Ad-hoc jobs only: run in the maintenance lane (below).

Supplying neither a payload nor a channel is an error — the defaults land on the ad-hoc channel with nothing to run. The return value is the job's id — the pgrelay.queue row that is the job. It stays the same for the job's whole life, keys its entire audit history, and is accepted by pgrelay.cancel().

The ad-hoc channel: `pg_relay_adhoc`¶

The default channel is seeded by pg_relay itself and does one thing: it executes the job's payload as SQL. Because a channel that runs its payload inverts pg_relay's usual rule (payloads are inert data), it is reserved — a protected row, not just a convention:

pgrelay.notify() — the PUBLIC entry point — refuses it. The only ways to put SQL on this channel are schedule_job() and run_sql(), which require a management grant: the same trust boundary pg_cron draws around cron.schedule.
It cannot be unregistered, and its action cannot be repointed by update() — the row is permanently pg_relay's. Tuning still works (update() for max_retries or concurrency_mode := 'channel' to serialise all ad-hoc jobs), and disable()/enable() remain available as a deliberate "suspend every ad-hoc job" lever.

What ad-hoc SQL can be, and how it runs:

The Processor executes it on a dedicated connection, outside any transaction — so VACUUM, CREATE INDEX CONCURRENTLY, and REINDEX CONCURRENTLY can be scheduled. psql -c rules apply: a non-transactional command must be the payload's only statement, while multi-statement payloads ('DELETE FROM myapp.stale; ANALYZE myapp.stale;') run atomically in an implicit transaction.
By default it executes as the pgrelay role — grant that role whatever the SQL touches, and schema-qualify names (myapp.refresh(), not refresh()). Or give the job a p_run_as role for least-privilege execution: you can only nominate a role you actually hold (pg_cron's rule), and the DBA opts each job role in once with GRANT <role> TO pgrelay. Session state never leaks between jobs — the connection is reset after every payload.

Inline or detached — two lanes¶

An ad-hoc job runs in one of two lanes:

Inline (the default) — for short jobs. The run is bounded by p_timeout_seconds (default 300 s); a run that would exceed it is cancelled and logged as an error. While an inline job runs it occupies one of this Processor instance's workers and delays that instance's next poll. With a fleet of two or more instances the others keep dispatching, so if you set long inline timeouts (the cap is 12 h), run more than one instance. Crash recovery re-fires the occurrence (at-least-once).
Detached (p_detached := true) — for maintenance-class work like a long VACUUM. The job runs on its own connection in the background. The Processor keeps its 1-second cadence for everything else, and any number of other channels tick along. A still-running previous occurrence is never overlapped: the new occurrence is skipped with an overlap_skipped row in the run history instead. No default timeout (set one if you want a runaway guard, up to 12 h). How many detached jobs may run at once per instance is a fleet setting: SELECT pgrelay.set_option('exec_max_detached', '10'); (default 5; picked up at startup and on fleet reload). At the cap, a due detached job simply waits — it starts the moment a slot frees. A crash mid-run loses that occurrence and the next one proceeds (at-most-once — exactly what you want for maintenance).

-- The classic maintenance job, in full:
SELECT pgrelay.schedule_job('weekly-vacuum', '0 2 * * 0',
                            'VACUUM ANALYZE myapp.big_table',
                            p_detached := true,
                            p_run_as   := 'maintenance_jobs',
                            p_timeout_seconds := 21600);  -- 6h runaway guard

One of each spec form¶

-- Daily maintenance at 03:00 (traditional cron; one call, no channel needed):
SELECT pgrelay.schedule_job('daily_purge', '0 3 * * *',
                            'SELECT pgrelay.purge(p_hours := 168)');

-- A near-real-time refresh every 10 seconds (seconds cron — pg_cron can't):
SELECT pgrelay.schedule_job('dash_tick', '*/10 * * * * *',
                            'REFRESH MATERIALIZED VIEW CONCURRENTLY ops.dash_mv');

-- Keep a cache warm every 90 minutes, whenever that lands (interval),
-- firing one of YOUR channels with a payload:
SELECT pgrelay.register('warm_chan', 'CALL ops.warm_cache($1)');
SELECT pgrelay.schedule_job('cache_warm', '90 minutes', 'eu-region', 'warm_chan');

-- The 2nd Tuesday at 09:00 UTC (pgrelay.schedule companion value):
SELECT pgrelay.schedule_job('board_pack', pgrelay.nth_dow('tue', '09:00', 2)::text,
                            'CALL reports.build_board_pack()');

The channel form is what connects scheduling to everything else pg_relay can do: a 'notify' channel makes a recurring email, a pg_relay.health channel a recurring host snapshot, and a node-restricted channel a per-node job — the payload always arriving as the action's ordinary $1.

Deferring the first run¶

p_first_run_at decouples when the job starts from its rhythm — start a weekly digest next Monday, not the Monday that already passed this week:

SELECT pgrelay.schedule_job('weekly_digest', '0 6 * * 1', 'weekly', 'report_chan',
                            p_first_run_at := '2026-09-07 06:00+10');

A past p_first_run_at is honoured too: the job fires on the next Processor tick and then falls into its rhythm — handy for "run it once right now, then nightly":

SELECT pgrelay.schedule_job('nightly_report', '0 3 * * *', 'CALL build_nightly_report()',
                            p_first_run_at := now());

Giving a job an end date¶

p_expire_at ends a schedule automatically — a campaign job that must stop at the end of the financial year, with no one needing to remember:

SELECT pgrelay.schedule_job('eofy_export', '0 22 * * *', 'CALL exports.eofy()',
                            p_expire_at := '2027-06-30 23:59+10');

Running SQL once¶

Not everything recurs. pgrelay.run_sql() is schedule_job()'s one-shot sibling: it submits a single ad-hoc statement — no name, no schedule spec — and returns a queue row id:

-- Run it on the Processor's next 1-second poll:
SELECT pgrelay.run_sql('VACUUM ANALYZE myapp.big_table');

-- Or defer it — "at 02:00 tonight, once" — and give up on it after 04:00:
SELECT pgrelay.run_sql('REINDEX INDEX CONCURRENTLY myapp.big_idx',
                       p_run_at    := date_trunc('day', now()) + interval '1 day 2 hours',
                       p_expire_at := date_trunc('day', now()) + interval '1 day 4 hours');

The statement runs exactly as a scheduled ad-hoc job's would — on the same reserved channel, outside a transaction, in either lane — and run_sql() takes the same three execution options: p_run_as, p_timeout_seconds, and p_detached (a detached one-shot is ideal for kicking off a long VACUUM right now without occupying the Processor). What differs from a job:

No name, no listing. Identity is the returned id — it never appears in list_scheduled_jobs(). pgrelay.cancel(<id>) withdraws it while it is still pending, and SELECT * FROM pgrelay.log WHERE queue_id = <id> is its audit trail after it runs.
No retries. A failed one-shot is recorded in the audit log for you to inspect and resubmit — arbitrary SQL is not retried blind.
Consumed, not perpetual. Once run (or cancelled, or expired), the row is finished and purge_queue() eventually removes it.

Together the three submission functions form a deliberate triangle: notify() fires a one-shot event on your channel (PUBLIC — its payload is inert data), run_sql() a one-shot SQL statement on pg_relay's channel (management-granted), and schedule_job() a recurring job of either kind.

Altering a job¶

There is no separate "alter" function, because schedule_job() is the alter. Calling it again with the same name updates the job in place — same id, same audit history, same run counters. The new channel, payload, spec, first-run, and end date take effect immediately:

-- The 03:00 report moves to 04:30 and now runs the summary variant:
SELECT pgrelay.schedule_job('nightly_report', '30 4 * * *', 'CALL build_nightly_report(''summary'')');

What an update keeps and what it replaces:

Kept	Replaced
The job id (and with it the whole `pgrelay.log` history)	Channel, payload, schedule spec
`created_at`, `first_run_at`, `run_count`	The next run time (recomputed from the new spec, or your new `p_first_run_at`)
—	`expire_at` (set to whatever you pass — `NULL` means no end date, not unchanged)
—	Any pause (next chapter) — re-registering a job also resumes it

Pass every option you still want

Because the update is a full re-registration, options you leave at their defaults are reset to those defaults — an expire_at you set last month disappears if you re-register without passing it again. Read the job back with list_scheduled_jobs() first if you're not sure what it carries.

Withdrawing a job¶

SELECT * FROM pgrelay.unschedule_job('nightly_report');
--  channel      | outcome   | log_id
-- --------------+-----------+--------
--  report_chan  | cancelled | 87161900114116700

unschedule_job() goes through the same machinery as pgrelay.cancel(): the job's row is stamped cancelled, a 'cancelled' audit row is written (that's the log_id returned), and purge_queue() eventually removes the remains. The name is immediately reusable — scheduling 'nightly_report' again creates a fresh job with a fresh id.

Two conventions worth knowing:

An unknown name — or a job something else is touching at this exact moment — returns outcome = 'skipped' and changes nothing. unschedule_job() never raises and never blocks.
Since the job id is a queue row id, SELECT * FROM pgrelay.cancel(<id>) withdraws a job too. Use whichever handle you have.

A run that is already executing when you withdraw the job finishes normally (and is audited); there is simply never a next one.

Permissions¶

Everything on this page is management-grade — none of it is callable by default. Grant the whole scheduling set (alongside channel management) with:

SELECT pgrelay.grant_user('your_admin_role');

The Processor's own role needs nothing new: scheduled rows travel through the claim/complete functions it could already execute.

Continue to Pausing, Resuming, and Running Now.