One source

This page is the project's CHANGELOG.md, included as-is. The newest release is first; the [Unreleased] section lists what has landed on main since it. For how pg_relay began, see Where pg_relay Started.

Changelog¶

Full documentation: https://pg-relay.pebbleit.com.au/latest/

All notable changes to pg_relay are documented in this file.

The format is based on Keep a Changelog, and this project adheres to Semantic Versioning.

[Unreleased]¶

pg_relay 1.6.0 adds the file spool: a Processor-native action type, pg_relay.file_spool, that writes each queued event's payload to a file in a local spool directory, atomically, so any process watching that directory can pick it up — log shippers, ETL pick-up jobs, batch loaders, agents on the same host, air-gapped exports. The hand-off is transactional (the file appears only if the enqueuing transaction commits) and the directory is environment, not data: the channel registration stores nothing site-specific, the Processor learns where to write from its own environment, and a restored or cloned database needs no data change. The extension changes ship as the upgrade script sql/pg_relay--1.5--1.6.sql — existing installs run ALTER EXTENSION pg_relay UPDATE;, fresh installs chain the 1.0 script and all six deltas automatically. Documented in its own book on the docs site, File Spool.

Added¶

Reserved action type pg_relay.file_spool — seeded by the 1.6 delta (run_order 1, alongside 'sql', 'pg_relay.exec', and 'pg_relay.otlp'). Like pg_relay.otlp, a file_spool channel is an ordinary registration (never reserved): pgrelay.register('exports', '', p_action_type := 'pg_relay.file_spool'). The action column is optional — empty means every default — or carries the file options as JSON: filename (template, default {channel}-{event_id}.json; tokens {channel}, {event_id}, {queued_at} as 20260907T041200Z, and {payload.<key>} for a top-level string or number of a JSON payload), extension_tmp (default .tmp), mode (default 0640, POSIX only), and replace (default false). The options describe the file, never the place.
Environment variables (hardcoded names, the lifecycle-notification precedent; read fresh on every event, never stored): PG_RELAY_SPOOL_DIR (base; a channel without its own directory writes to <base>/<channel>/), PG_RELAY_SPOOL_<CHANNEL>_DIR (per-channel override; <CHANNEL> = the name upper-cased with non-alphanumerics → _), PG_RELAY_SPOOL_MAX_BYTES / PG_RELAY_SPOOL_<CHANNEL>_MAX_BYTES (cap on the total size of files in a directory), PG_RELAY_SPOOL_MAX_FILE_BYTES (cap on one payload, default 16 MiB). The Processor never creates a directory; a size variable that is present but not an integer refuses startup (spool_config_invalid), and a configured directory that does not exist is reported at start (spool_dir_unavailable).
The atomic write: payload bytes verbatim (no parsing, no trailing newline) to <dir>/<name><extension_tmp> (created exclusively; a stale temporary file from a crashed attempt is removed first, a symlink under either name is never followed), fsync, mode applied, close, rename to <dir>/<name>, directory fsync — a consumer never observes a partial file under the final name. os.Rename is MoveFileEx with replace semantics and File.Sync is FlushFileBuffers on Windows; the directory fsync and mode are POSIX-only.
Idempotency and replace: an event whose file already exists is reported as success with a note in pgrelay.log.error (spool_exists logged) — the crash-between-rename-and-commit / at-least-once redelivery case, which the deterministic default template makes a safety net rather than a design dependency (SKIP LOCKED already gives one Processor per event). With replace: true on the channel the rename atomically overwrites the old file instead (spool_replaced); a JSON-object payload can override the channel default per event with a top-level boolean "replace" member (a non-boolean is a permanent error; a non-JSON payload is never inspected).
Failure semantics, mapped onto what the Processor already does: a directory that is not configured, missing, or unwritable, or that would be at or over its MAX_BYTES cap after the write, withholds the event — the claim rolls back and the same row stays pending, re-offered every tick until the environment is fixed or the consumer drains the directory (the exec_withheld precedent: nothing lost, no retry burned, no audit row; spool_unavailable / spool_available logged once per channel transition, spool_withheld per event at debug). An oversized payload, an unsafe or empty rendered filename (only [A-Za-z0-9._-], no .., under 200 bytes), or a bad channel option is permanent (one error audit row, filesystem never touched). A disk error during the write or rename follows the ordinary retry chain (_queue_insert_retry up to the channel's max_retries), the temporary file removed if possible.
Go: the pg_relay.file_spool event handler (internal/processor/spool.go) — routed in the dispatch worker like every other Processor-native type, using the same held-transaction claim ceremony: claim → options via the existing pgrelay._fetch_action() → environment → write → _write_log → _queue_mark_done (+ _queue_insert_retry on a disk error) → COMMIT. {queued_at} is recovered from the queue id's snowflake timestamp (every file_spool row postdates the 1.1 delta). Payload content and the rendered filename never reach stdout at any level; the directory path (the operator's own environment) does, in the spool_* lines.
Go suite internal/processor/spool_test.go — option parsing and validation, every filename token and the full unsafe-name matrix (traversal, separators in payload values, .., length, unknown tokens) with the error text proven never to echo payload content, environment resolution (base fallback, per-channel override, case-insensitive channel keys, invalid caps named), the startup check, and full processSpoolEvent drives against the held-tx fakeConn and a real temp directory: exactly one complete file with the bytes unchanged and mode 0640; a high-frequency poller never observing a partial file under the final name during an 8 MiB write; redelivery as a logged no-op; replace on the channel and overridden either way by the payload; MAX_BYTES withholding (rollback, no audit, one spool_unavailable) then draining once files are removed; the same database conversation under two different PG_RELAY_SPOOL_DIR values with output following the environment; a missing directory reported at start and withheld until created; unconfigured and unwritable directories withheld; unsafe templates permanent with the filesystem untouched; oversized payloads permanent; a disk error scheduling a retry then terminal when exhausted; stale and symlinked temporary names replaced without being followed; expired/invalid preconditions; and dispatch routing.
v1.6 SQL test suite test/pg_relay_test--1.6.sql — replaces the 1.5 suite as the suite CI runs (make test-sql). Adds the 1.5→1.6 leg of the upgrade-path test and six new tests (129–134) covering what SQL owns: the seed row and the still-reserved pg_relay.* prefix, a file_spool channel as an ordinary registration with an empty or optional-JSON action (never a path), notify() storing an opaque payload verbatim (trailing newline and all, NULL accepted), the transactional hand-off proven from a separate dblink session (a rolled-back notify() leaves nothing, a committed one exactly one pending row), queue_pending_ids() offering (id, 'pg_relay.file_spool') and _queue_claim() handing the row to one session only under a concurrent dblink claim, and old-binary degradation to unsupported_action_type.
File Spool — a new five-page book on the documentation site: an overview (when to choose a file over a webhook, the write sequence, what the action never does); setting up the spool directory (shared group, 0770/0640, the ReadWritePaths= line the hardened unit needs, every environment variable, why the path is not in the database with the clone-and-restore example, Windows and container notes); filenames and replacing files (the template tokens, the safe-name rule, replace: true for keep-current files with the per-event payload override and a redelivery/retry matrix); reading the spool (the consumer's three rules, inotifywait, polling, and PowerShell consumers, consuming a replace channel, backpressure); and a configuration and failure reference (options, variables, the write sequence, the full outcome table, log lines, monitoring, grants, platform summary). Cross-linked from Common Uses, Managing Channels, the Database Tables reference, and the Glossary. The variables join the Technical Guide's Processor page (a new "Other environment variables" reference), the log-line reference, both deploy/*.env.example files (commented out), and the Security Guide's ProtectSystem=strict note.

Changed¶

CI: native Windows verification on w tags. A new test-windows job runs the Go unit suite, go vet, a native build of pg_relay-windows-x86_64.exe, and a -h smoke run on GitLab.com's hosted Windows Server 2022 runner (saas-windows-medium-amd64). It is triggered only by a tag matching w{major}.{minor}.{patch} (w1.6.0), a separate family from release tags, so a Windows check can be pushed at will without cutting a release and a release never waits on a Windows runner. Documented in RELEASING.md.
CI: the test stage is now three jobs with three separate triggers. The full 14-job Linux matrix (test) runs only on a b{major}.{minor}.{patch} tag (b1.6.0); a new one-job smoke check (test-quick, PostgreSQL 18 on Ubuntu 26.04) runs on any push that changes the Go or SQL sources, go.mod/go.sum, the Makefile, or the control file; test-windows stays on w tags. A v release tag runs no tests — only build, publish, and the security scans — so run a b tag (and a w tag if wanted) on the release commit first; RELEASING.md documents the order. Both Linux jobs share one hidden body (.test-linux). GitLab evaluates rules: changes as true on every tag pipeline, so test-quick carries a when: never guard for tags. The Go toolchain version every job installs is a single top-level GO_VERSION variable.
The 1.5 → 1.6 delta is deliberately tiny — the pg_relay.file_spool seed row and the version bump are the entire script. No new SQL function, no new grant, no preflight() change, and no reserved channel: the claim → options-read → audit → done sequence reuses _queue_claim, _fetch_action, _write_log, _queue_mark_done, and _queue_insert_retry exactly as they already stand. A Processor already running pg_relay ≥ 1.1 picks up the feature with zero operator action beyond setting PG_RELAY_SPOOL_DIR.
Running a pre-1.6 binary against 1.6 SQL degrades loudly and safely: a file_spool row routes to process_one(), which resolves it as unsupported_action_type — one audit row per occurrence, never a retry loop. Upgrade the binary to start writing.
Release tarball is now pg_relay--1.6.sql.tar.gz, containing pg_relay.control, sql/pg_relay--1.0.sql, and the six upgrade deltas (plus the schedule/ companion files as before).

[1.5.0] - 2026-09-05¶

pg_relay 1.5.0 adds OTLP metrics: a Processor-native action type, pg_relay.otlp, that sends a gauge metric point to any OpenTelemetry Protocol (OTLP) HTTP/JSON metrics receiver — Grafana Alloy's OTLP intake is the driving use case, but the wire format is the OTLP spec itself, not anything Alloy-specific. A metric measured inside the database (a connection count, a replication lag, a queue depth, a business figure) becomes one pgrelay.notify() call on an otlp channel, and the durable queue, retry chain, and audit log carry it to the receiver exactly as they carry every other event. The extension changes ship as the upgrade script sql/pg_relay--1.4--1.5.sql — existing installs run ALTER EXTENSION pg_relay UPDATE;, fresh installs chain the 1.0 script and all five deltas automatically. Documented in its own book on the docs site, OTLP Metrics (overview, a worked Grafana Alloy setup, and the full reference).

Added¶

Reserved action type pg_relay.otlp — seeded by the 1.5 delta (run_order 1, alongside 'sql' and 'pg_relay.exec': ordinary work, never ahead of the run_order-0 control types). It follows the Processor-native pg_relay.reload/health/exec pattern — no companion extension — with one deliberate difference: those three carry an empty action because their payload is all they need, whereas a metric point has to be sent somewhere, and different channels may target different receivers. So a pg_relay.otlp channel is an ordinary, unreserved registration whose action column carries the receiver configuration as a JSON object instead of SQL: pgrelay.register('otel_metrics', '{"endpoint": "http://localhost:4318/v1/metrics"}', p_action_type := 'pg_relay.otlp'). An operator registers as many otlp channels as there are receivers; register/update/get/list treat them like any other channel, and pgrelay.queue_stats() reports them like any other action type.
Channel config (the action JSON): endpoint (required — the URL the export request is POSTed to), service_name (optional, default pg_relay — sent as the service.name resource attribute), auth (optional — the webhook transport's exact auth.style vocabulary, bearer_header | custom_header | basic_auth, applied only when the key is present, since a local OTLP receiver is commonly unauthenticated), and timeout_seconds (default 30, cap 120, via the shared send timeout). Any _env:VAR_NAME string anywhere in the config is resolved from the Processor host's environment immediately before use — the same rule 'notify' profiles follow — never cached, never stored, never logged. host.name is never configurable: every point carries this Processor instance's own OS hostname, as pg_relay.health answers already report their host.
The metric payload — the notify() payload is one gauge data point: {"name": "...", "value": <number>, "timestamp": "<RFC 3339>", "attributes": {...}}. name, value, and timestamp are all required: the timestamp is the moment the measurement was taken, supplied by the producer, and deliberately never a default-to-now fallback — a row can sit in the queue before dispatch, and a send-time stamp would silently report the wrong moment. value maps to OTLP's asInt (whole number) or asDouble (fractional part). attributes is optional and typed from the JSON value itself — string → stringValue, boolean → boolValue, whole number → intValue, fractional → doubleValue; a nested object or array is rejected, naming the key, since OTLP attribute values are not shape-blind the way the webhook transport's body_merge is. Every event maps to exactly one standard OTLP resourceMetrics export request — pg_relay never batches queue rows into one request, so each stays independently retryable.
Go: the pg_relay.otlp event handler (internal/processor/otlp.go) — routed like sql/notify/pg_relay.reload/health/exec in the dispatch worker, using the same held-transaction claim ceremony: claim → parse the payload → read the channel config via the existing pgrelay._fetch_action() (the very call process_one makes for a 'sql' channel's action text, here holding JSON) → resolve _env: references → build the OTLP envelope → POST → audit → done, with a transient failure scheduling a retry through the ordinary chain. Classification is the acs/gmail split, not the webhook transport's classify_webhook_response delegation (OTLP has a fixed, well-known response shape; there is no companion to ask): 2xx is success; a malformed payload or channel config (including a missing endpoint) is permanent with no request sent — configuration cannot improve by retrying; a 3xx redirect (never followed, mirroring the webhook transport) or any other 4xx is permanent; 429, 5xx, and a network/DNS/TLS/timeout failure are transient. The payload and channel config are never logged to stdout at any level — only channel, queue id, and log id reach the Processor's own log lines — and a dial failure's error text has the endpoint URL stripped before it can reach pgrelay.log.error, since a signed push URL is itself a credential.
Go suite internal/processor/otlp_test.go — the payload's required-field matrix and attribute typing, config parsing with _env: resolution end to end (an unset variable named as a permanent error), the exact wire envelope, the full status-classification matrix against real httptest receivers (including a redirect resolved without being followed and a genuine dial failure classified transient), and a full processOTLPEvent drive asserting the held-transaction statement order, the metric name absent from every log line, permanent resolution with no retry for bad input, and a transient 503 scheduling one.
v1.5 SQL test suite test/pg_relay_test--1.5.sql — replaces the 1.4 suite as the suite CI runs (make test-sql). Adds the 1.4→1.5 leg of the upgrade-path test and five new tests (124–128) covering what SQL owns: the seed row and the still-reserved pg_relay.* prefix, an otlp channel as an ordinary registration carrying real config (never reserved, never empty), notify() enqueuing with no special-casing, queue_pending_ids() offering a due otlp row, and old-binary degradation. The send and classification logic is Go-tested.
Resource identity on pg_relay.otlp channels — the channel config gains two first-class keys alongside service_name: service_namespace (→ the service.namespace resource attribute) and deployment_environment (→ deployment.environment.name, the current OpenTelemetry semantic-convention key; the deprecated deployment.environment is never emitted), plus a free-form resource_attributes object for any other resource attribute (service.version, service.instance.id, cloud.region, …), typed from each JSON value exactly like data-point attributes and sorted by key. These are what convention-aware backends group by — Grafana derives its job label as <service.namespace>/<service.name> and maps services to environments by deployment.environment.name; without them pg_relay's metrics appear ungrouped. Because they live in the channel config, _env:VAR_NAME references resolve in all of them (an environment name can come from the deployment's own environment), and the four keys with dedicated handling — service.name, service.namespace, deployment.environment.name, and the never-configurable host.name — are rejected inside resource_attributes so a free-form entry can never shadow them. A non-object resource_attributes or a nested value inside it is a permanent error with no request sent, the existing malformed-config rule. Go-only; no SQL delta, no new grant. Documented in the OTLP Metrics book's new Building a Metric Document chapter — a step-by-step guide for developers assembling the JSON a producer sends — and the reference.
Security Guide — a new book on the documentation site covering where the Processor should run and how to lock it down: the rule that co-locating the Processor with PostgreSQL is for sandboxes and development only (every other deployment runs it on its own host in a restricted, no-inbound zone with allowlisted egress); the cloud-managed equivalents (private networking, identity-based database credentials, no long-lived password); an honest blast-radius assessment of a fully compromised Processor (what the pgrelay role can and cannot do, why GRANT … TO pgrelay is the one knob that sets it, detection, and a containment runbook); the hardened Linux service (a dedicated nologin user, the full systemd sandbox — NoNewPrivileges, ProtectHome, PrivateTmp, ProtectSystem=strict, ProtectProc=invisible, and the rest — hidepid=2 on /proc, core dumps disabled at the unit, systemd-coredump, and kernel layers, a root-only environment file the Processor's own user cannot read, and credential rotation); database-side controls (pg_hba.conf per-host hostssl + SCRAM, verify-full TLS, the pgrelay role's shape, PUBLIC privilege review); and a per-tier checklist. No Processor code change. Ships with two new deploy files — deploy/pg_relay-hardened.service (the production unit: dedicated nologin user, the full sandbox, LimitCORE=0, annotated prerequisites) and deploy/pg_relay-hardened.env.example (TCP, verify-full, proxy and secret placeholders; installed root-only since systemd reads it before dropping privileges) — while deploy/pg_relay.service and pg_relay.env.example remain the sandbox-tier examples (User=postgres, Unix socket) and now say so in their headers.

Changed¶

The 1.4 → 1.5 delta is deliberately tiny — the pg_relay.otlp seed row and the version bump are the entire script. No new SQL function, no new grant, no preflight() change, and no reserved channel: the claim → config-read → audit → done sequence reuses _queue_claim, _fetch_action, _write_log, _queue_mark_done, and _queue_insert_retry exactly as they already stand, all already granted to the pgrelay role and already checked. A Processor already running picks the whole feature up on upgrade with zero operator action — not even a reload; grant_user() is untouched, so unlike the 1.1–1.4 upgrades there is nothing to re-grant.
sanitizeURLError (internal/processor/default_notifications.go) takes an operation label so the otlp handler shares the lifecycle-webhook path's URL-stripping rule; its existing callers now label their errors notify send explicitly (message text unchanged).
Running a pre-1.5 binary against 1.5 SQL degrades loudly and safely: an otlp row routes to process_one(), which resolves it as unsupported_action_type — one audit row per occurrence, never a retry loop. Upgrade the binary to start sending.
Release tarball is now pg_relay--1.5.sql.tar.gz, containing pg_relay.control, sql/pg_relay--1.0.sql, and the five upgrade deltas (plus the schedule/ companion files as before).
Go toolchain bumped from go1.26.5 to go1.26.7, pgx from v5.7.5 to v5.10.0, and the minimum Go version raised from 1.23 to 1.25. pgx 5.8.0 through 5.10.0 bring a substantial hardening pass against a malicious or compromised server (bounded binary decoders, capped SCRAM iteration counts, cancel requests sent over TLS when the main connection used TLS, the new require_auth / PGREQUIREAUTH setting to refuse authentication downgrades under sslmode=prefer), the 5.9.2 fix for placeholder confusion inside dollar-quoted strings (GHSA-j88v-2chj-qfwx — reachable only through the simple protocol with a caller-supplied argument, which the Processor never does), SCRAM-SHA-256-PLUS channel binding, and fixes to connect-time context handling (a data race on cancellation during connect, a goroutine leak in the context watcher, a fresh context for the sslmode=prefer fallback). The wire protocol still defaults to 3.0, so connections to PostgreSQL 15–17 and to poolers are unchanged. golang.org/x/crypto leaves the dependency graph entirely (pgx 5.8.0 dropped it); golang.org/x/text moves to v0.41.0. pgx 5.9.0 requires Go 1.25, so the go directive moves from 1.23.0 to 1.25.0 — the minimum to build from source — while the pinned toolchain does the actual compiling and is fetched automatically by any Go 1.25 installation. govulncheck reports no known vulnerabilities; no Processor source changed and all Go and SQL tests pass unmodified. The CI test and build stages now install Go 1.26.7 directly instead of relying on the toolchain auto-switch from an end-of-life 1.23. No SQL files are affected.
Documentation site restructured for readability. The thirteen top-level books are regrouped under five tabs — Start Here, Deploy, Features, Reference, About — so a newcomer sees the User Guide first and the optional books (multi-master, OTLP, the schedule type, which is now an appendix of Scheduled Jobs) in their place. The home page is cut to a one-paragraph explanation, the flow diagram, and a "where to start" table; the User Guide is the single full explanation of what pg_relay is, and the other books link to it instead of re-explaining. Every feature book opens with a "Who needs this book" note. Three oversized pages are split at their headings (Function Reference into three; Lifecycle Notifications' message variables and the Security Guide's host settings into their own chapters), and the longest sentences across the Technical Guide, Scheduled Jobs, and Security Guide are broken up. Acronyms get hover definitions site-wide from one shared abbreviations file, replacing inline glosses. The site is now the only user-facing documentation: the root-level USER_GUIDE.md, TECHNICAL_GUIDE.md, CLOUD_SETUP.md, EXAMPLE_USAGE.md, and MULTI_MASTER_DEPLOYMENT.md duplicates are removed, the site's Release Notes page includes this CHANGELOG.md directly instead of paraphrasing it, and README.md is a short pitch plus links (the public repository's README is now this file as-is, no longer a renamed USER_GUIDE.md). Version numbers are no longer hand-maintained on the home page or README — the Release Notes are the one source.

Deferred¶

Only the gauge metric point type ships in 1.5. Sum and histogram points are deliberately not inferred from the payload's shape — each needs an explicit payload-contract decision first (aggregation temporality for sums, bucket boundaries for histograms) and will arrive in a future delta with one.

[1.4.0]¶

pg_relay 1.4.0 adds scheduled jobs: a pg_cron replacement built directly on the durable queue, with second-level scheduling pg_cron cannot do. A scheduled job is a perpetual queue row — its run_at always holds the next occurrence, so the Processor's existing 1-second poll detects every instance with zero changes to the dispatch queries and zero changes to the Go binary. The call shape is pg_cron's own: pgrelay.schedule_job('nightly', '0 3 * * *', 'CALL run_report()') is a complete job — the SQL payload runs on the seeded, reserved pg_relay_adhoc channel — while naming a channel (schedule_job('nightly', '0 3 * * *', 'summary', 'report_chan')) fires that channel with the payload as its ordinary $1. Because a scheduled job is an ordinary queue row on an ordinary channel, every existing capability composes for free — any action type (a recurring Processor health snapshot is one call), concurrency_mode, node-restricted channels, multi-node ownership and adoption, pause/reload, and the audit log. The extension changes ship as the upgrade script sql/pg_relay--1.3--1.4.sql — existing installs run ALTER EXTENSION pg_relay UPDATE;, fresh installs chain the 1.0 script and all four deltas automatically.

Added¶

Perpetual schedule rows — six new pgrelay.queue columns: schedule (the recurrence spec; NULL = ordinary one-shot event, completely unchanged), schedule_name (the job's case-insensitive identity, unique among live schedules via a partial index), the job-record stats schedule_updated_at, schedule_first_run_at, schedule_run_count, and the pause bookkeeping schedule_paused_until. A schedule row is never consumed: dispatched_at stays NULL for its whole life, and existing column semantics extend naturally — expire_at is the schedule's end date (the expired branch deletes it), cancel(id) stops it, pgrelay.log WHERE queue_id = <id> is its complete run history under one stable id, and purge_queue() never touches a live schedule.
Schedule syntax (pgrelay._next_run(spec, after) — the one calculator every path uses, doubling as the registration-time validator): a PostgreSQL interval ('30 seconds', '2 hours'), traditional 5-field cron ('*/5 * * * *' — min hour dom month dow, with lists, ranges, steps, and the standard dom/dow OR rule; dow 0 and 7 both Sunday), 6-field cron whose first field is seconds ('20 * * * * *' — second-granularity calendar scheduling, one better than pg_cron), or a pgrelay.schedule companion value supplied as its composite literal (e.g. pgrelay.daily('04:30')::text) — complex calendar arrangements (nth weekday, month-end, per-day times) delegated to the optional schedule/ companion's own pgrelay.next_run() through guarded dynamic SQL, so the extension keeps no static reference to it and a companion-literal spec without the companion installed raises a clear error at registration. Cron evaluates in the session timezone; missed occurrences are skipped — a late row (Processor down, fleet paused) fires once and the next occurrence is computed strictly after now.
The claim-time advance — _queue_claim() moves a scheduled row's run_at to the next occurrence at claim, before the payload runs, inside the claim's own transaction: fixed cadence (computed from launch time, not completion time), crash-safe (a crash mid-payload rolls the advance back and the job re-fires — the same at-least-once contract every event has), and outcome-independent (a payload error is contained by process_one()'s savepoint, so the schedule marches on, each run audited). The same UPDATE maintains schedule_run_count and schedule_first_run_at, so they count committed launches. Because every action type begins with _queue_claim(), scheduling works identically for sql, notify, pg_relay.reload, and pg_relay.health events — with no Go changes at all. A spec that stops being evaluable (a companion literal after the companion is uninstalled) is treated as a poison event: the schedule stops loudly, once — an error audit row naming the cause — never an error-per-tick loop.
The overrun bump — _queue_mark_done() leaves a scheduled row pending, and if a run outlasted the occurrence the claim computed, re-advances run_at past now() — re-applying skip-missed instead of firing back-to-back to catch up. Ordinary rows are consumed exactly as before; retries of a failed scheduled run are ordinary one-shot rows (the schedule is never copied).
pgrelay.schedule_job(p_name, p_schedule, p_payload DEFAULT NULL, p_channel DEFAULT 'pg_relay_adhoc', p_first_run_at DEFAULT NULL, p_expire_at DEFAULT NULL, p_run_as DEFAULT NULL, p_timeout_seconds DEFAULT NULL, p_detached DEFAULT false) — registers (or re-registers) a named job, returning its stable queue row id. The parameter order deliberately mirrors cron.schedule(jobname, schedule, command): with only a payload, the job runs it as SQL on the reserved adhoc channel; with a channel, the payload is that channel's ordinary $1; an exec channel with no payload raises (which is also the neither-supplied error), and the three exec-only parameters raise on any other channel. Validates like notify() (unregistered/inactive channel raises; the spec is validated at the call site even when p_first_run_at is supplied). Upserts by name: an existing live schedule keeps its id, history, and launch stats while channel/payload/spec/first-run/end-date update in place. p_first_run_at overrides the computed first occurrence verbatim (a past value fires on the next tick); recurrence follows the spec either way. Named schedule_job — not schedule — because pgrelay.schedule is the companion's recurrence domain.
The pg_relay.exec action type — ad-hoc SQL outside a transaction. The Processor gains a fourth event handler (internal/processor/exec.go): the job's payload is executed as SQL on a dedicated autocommit connection, top-level, so VACUUM, CREATE INDEX CONCURRENTLY, and REINDEX CONCURRENTLY can be scheduled — the pg_cron capability an in-transaction action could never offer. The payload is sent as one query string (psql -c semantics: a multi-statement payload runs in the protocol's implicit transaction — atomic, but a non-transactional command must then be the payload's only statement). Two lanes per job:
- Inline (default): the notify-style held claim, payload under a timeout (p_timeout_seconds, default 300s, cap 43200s), at-least-once (a crash re-fires the occurrence). An inline payload occupies its worker — and this instance's dispatch barrier — for its duration; other Processor instances keep draining via SKIP LOCKED (run ≥2 instances if you use long inline timeouts).
- Detached (p_detached := true) — the maintenance lane: the claim commits immediately (worker freed, the whole instance keeps its 1-second cadence), the payload runs in its own goroutine on its own connection, a session advisory lock on the job id guarantees occurrences never overlap even across instances (a still-running prior occurrence resolves the new one as an overlap_skipped audit row), concurrency is capped by the pgrelay.options key exec_max_detached (default 5; at the cap the row simply stays pending until a slot frees), no default timeout (cap 43200s when set), at-most-once (a crash loses that occurrence; the next proceeds).
- p_run_as — least-privilege execution, pg_cron's rule: the payload runs under SET ROLE <run_as>, where the scheduler must hold the role (pg_has_role, checked at schedule_job() time — you cannot schedule privileges you don't have) and the DBA must have opted the role in with GRANT <role> TO pgrelay (checked too, with the remedy in the error text). The shared inline connection runs DISCARD ALL between payloads, so no job's role or session state leaks into the next. Within the management tier this is least-privilege hygiene, not a hard boundary — documented as such.
The reserved pg_relay_adhoc channel + pgrelay.actions.reserved — the seeded default channel (action_type pg_relay.exec, empty action — the Processor does the work). A channel whose payload is executable SQL inverts the queue's core safety property (payloads are inert data), so it is protected by a row-level reserved flag, not a name string: notify() — the one PUBLIC entry point — refuses reserved channels (schedule_job() and run_sql(), both management-granted, are the only ways onto them, the same trust level as pg_cron's schema grant); unregister() refuses to drop them (channels have no rename path, so the identity is pinned for good); update() refuses to repoint their action while every tuning knob (max_retries, concurrency_mode, notes) still works; enable()/disable() remain available as a deliberate suspend-all-adhoc-jobs lever; and register() never exposes the flag, so user channels are always unreserved.
pgrelay.run_sql(p_sql, p_run_at DEFAULT now(), p_expire_at DEFAULT NULL, p_run_as DEFAULT NULL, p_timeout_seconds DEFAULT NULL, p_detached DEFAULT false) — schedule_job()'s one-shot sibling: submits a single ad-hoc statement, with no name and no recurrence spec, as an ordinary (schedule-NULL) queue row on the reserved pg_relay_adhoc channel. The statement runs exactly as a scheduled ad-hoc job's would — by the Processor, outside a transaction, with the same p_run_as/p_timeout_seconds/p_detached execution options — on the next 1-second poll, or deferred via p_run_at ("run this VACUUM at 02:00, once"), with p_expire_at discarding it if it has not run by then. Returns the queue row id: cancel(id) cancels it while pending, and pgrelay.log.queue_id keys its audit row after. Failures are terminal (no retries) — the audit row records them for inspection and resubmission. Completes the submission triangle: notify() = one-shot event on your channel (PUBLIC, payload inert), run_sql() = one-shot SQL on pg_relay's channel (management), schedule_job() = recurring either. Zero Go changes — a schedule-NULL exec row is the shape the handler already processes.
pgrelay.unschedule_job(p_name) — stops a job through the cancel() machinery ('cancelled' outcome and audit row; the name is immediately reusable). An unknown or concurrently-locked name is 'skipped' — never raises, never blocks.
pgrelay.pause_job(p_name, p_until DEFAULT NULL) / pgrelay.resume_job(p_name) — suspend one job without cancelling it (the fleet-wide stop()/pause_*() still pause everything). Implemented by repositioning run_at itself — 'infinity' for an indefinite pause, the first occurrence at/after p_until for a bounded one — so the dispatch queries need no new logic and a bounded pause self-resumes database-side, exactly like the fleet's pause_until. The job keeps its id, history, and stats; resume_job() recomputes the next occurrence from now and is idempotent (it never postpones a due run); re-registering with schedule_job() also lifts a pause. pgrelay.queue.schedule_paused_until records the pause for the listing, which now carries effective paused + paused_until columns.
pgrelay.run_job_now(p_name) — fires an immediate extra occurrence (something pg_cron cannot do): the row becomes due now, the next 1-second poll launches it through the ordinary claim — so it can never overlap an executing run, is audited under the job's stable id, and counts in run_count — and the claim-time advance then restores the normal rhythm. Raises on a paused job (resume first) rather than silently lifting the pause.
pgrelay.list_scheduled_jobs() — the cron.job analogue: id (the log-history key), name, channel, spec, payload, next_run, expire_at, created_at, updated_at, first_run_at, and run_count — the last two maintained on the row itself, so they survive log purges.
pgrelay.scheduled_job_runs(p_name DEFAULT NULL, p_limit DEFAULT 100, p_within DEFAULT NULL, p_status DEFAULT NULL) — the cron.job_run_details analogue: per-run history from pgrelay.log via the job's stable queue_id, newest first — status (ok/error/retry_scheduled/invalid/…), error text, elapsed_ms, and when it ran. Three freely-combinable optional filters: one job by name, p_within (an interval — only completions inside the trailing window, e.g. p_within := '1 hour' for the last hour's runs), and p_status (one outcome, case-insensitive — p_status := 'error' for just the failures).
v1.4 SQL test suite test/pg_relay_test--1.4.sql — replaces the 1.3 suite as the suite CI runs (make test-sql). Adds the 1.3→1.4 leg of the upgrade-path test and fourteen new tests (109–122) covering the calculator's grammars and validation, registration/upsert/unschedule/listing/run-history, the claim-time advance (including its rollback with the claim transaction and the poison-spec stop), the full perpetual-row lifecycle through process_one(), the overrun bump, dispatch composition with node restriction, privileges, pause/resume (infinity parking, bounded self-resume, idempotency, the stale-marker tidy-up), run_job_now(), the reserved-channel guards, the pg_cron-simple SQL surface (exec-typed dispatch, run_as/timeout/detached validation and storage, safe old-binary degradation), and run_sql() (validation, the one-shot row shape, cancel by id, purge cleanup, the disabled-channel refusal). Ad-hoc execution itself — both lanes, timeouts, SET ROLE, the advisory-lock overlap skip, capacity withholding — is covered by the new Go suite internal/processor/exec_test.go.
pgrelay.queue_pending(p_limit DEFAULT 99) — the pending (undispatched) event rows, deferred ones included, in dispatch order: id, channel, payload, queued/run/expire times, retry_count, parent_id, and the node columns. The management complement to cancel(): the returned id is exactly what cancel() takes, so a management role can now find the pending row it wants to withdraw. Excludes perpetual schedule rows (list_scheduled_jobs() is their view) and takes no locks — a passive read, not the Processor's dispatch feed.
pgrelay.log_report(p_limit DEFAULT 99, p_within DEFAULT NULL, p_status DEFAULT NULL, p_channel DEFAULT NULL) — the audit-log read surface (the health_report() pattern applied to pgrelay.log): recent completions of every queued item, newest first, with combinable case-insensitive filters for a trailing window, one status, and one channel. Until now the log was readable only by superusers or through the schedule-scoped scheduled_job_runs().
psql shortcuts (deploy/pg_relay_shortcuts.sql) — \set query macros to reference from ~/.psqlrc: :schedules (every live job) and :schedules5m/15m/1h/4h/8h/1d (jobs due within the coming period); :jobs (the last 99 completions, newest first, every status) and :jobs5m…:jobs1d (completions within the trailing period); :jobs_err (the last 99 not-ok completions) and :jobs5m_err…:jobs1d_err (the same, windowed); :queue (the pending event rows, in dispatch order) and :queue_done (the last 99 completed queue items of any kind). Plain macros over the management read functions — they need only the grant_user() grant, nothing installed in the database.

Changed¶

pgrelay.grant_user() additionally grants schedule_job(), run_sql(), unschedule_job(), pause_job(), resume_job(), run_job_now(), list_scheduled_jobs(), scheduled_job_runs(), queue_pending(), log_report(), and _next_run() (the last as an operator spec validator / next-occurrence previewer). If you are upgrading: re-run pgrelay.grant_user(role) for each management role, exactly as the 1.1, 1.2, and 1.3 upgrade notes asked. grant_relay() and preflight() are untouched — the Processor drives scheduled rows through functions it can already execute, so a running Processor picks the feature up with no operator action (a reload or restart is not even required).
_queue_claim() and _queue_mark_done() are recreated (CREATE OR REPLACE, ACLs preserved) with the schedule-aware behaviour above; their signatures and every dispatch predicate are unchanged.
pgrelay.notify(), pgrelay.unregister(), and pgrelay.update() are recreated (CREATE OR REPLACE, ACLs preserved — notify() stays PUBLIC) with the reserved-channel refusals described above; behaviour on every ordinary channel is byte-identical. grant_relay() is recreated to additionally grant _exec_options() (the exec handler's per-job settings read) and get_option() (the exec_max_detached read), both also granted to the pgrelay role directly by the delta — a running Processor executes ad-hoc jobs with no operator action. Running a pre-1.4 binary against 1.4 SQL degrades loudly and safely: each exec occurrence logs unsupported_action_type and the schedule itself is preserved.
Release tarball is now pg_relay--1.4.sql.tar.gz, containing pg_relay.control, sql/pg_relay--1.0.sql, and the four upgrade deltas (plus the schedule/ companion files as before).

[1.3.0]¶

pg_relay 1.3.0 adds Processor health snapshots: an on-demand way to ask a running Processor instance, through the database, to report on the host it runs on — CPU utilisation, memory utilisation, and disk space (os_metrics), the host's process table, optionally filtered by a search string (processes), or the Linux database-host audits: kernel and tuning posture (os_config), path-to-device storage resolution (storage), and network interfaces (network) — collected with the Go standard library only (no agents, no new dependencies) and answered into a durable table. It also adds lifecycle notifications: the Processor speaking for itself, out of band — an outage alert when the database becomes unreachable (the one failure it can never report through its own queue) with a recovery message when it returns, plus start/stop announcements — each an environment-variable contract over any transport (webhook, SMTP, M365, ACS, Gmail), with template variables in the messages and a no-database --test-notify mode. The extension changes ship as the upgrade script sql/pg_relay--1.2--1.3.sql — existing installs run ALTER EXTENSION pg_relay UPDATE;, fresh installs chain the 1.0 script and all three deltas automatically. Documented on its own page: the Technical Guide's "Processor Health Snapshots" chapter.

Added¶

Reserved action type pg_relay.health — seeded by the 1.3 delta (run_order 0, so health requests surface with reload requests ahead of ordinary work). Follows the pg_relay.reload pattern exactly: register a channel with this action type (empty action) and enqueue requests through the ordinary pgrelay.notify(). The payload is the request — empty for all defaults, a bare string as shorthand for just the calling reference, or a JSON object with the optional keys reference (a caller-chosen correlation string echoed onto the answer row, NULL when omitted), request_type (default os_metrics; an unrecognised value resolves the event as a permanent error naming the supported types), disk (os_metrics only: an OS-specific path qualifying the space calculation — /var/lib, D:\ — defaulting to the Processor's working directory), and search (processes only: a case-insensitive substring filter over process name and command line; absent/empty = every process). Because a request is an ordinary queue event, p_run_at, p_expire_at, p_deduplicate, and cancel() all apply unchanged. Exactly one instance claims each request (normal SKIP LOCKED semantics); a fleet survey means one request per instance.
pgrelay.processor_health — the private answer table: responder identity in real columns (instance, node, host, os), request correlation (queue_id, request_type, reference, collected_at), and the results as a single jsonb data document — deliberately never hard-coded metric columns, so future request types (process lists, installed versions, …) land without schema changes. Snowflake id (like queue/log), so the table is safe to replicate. For os_metrics the document carries cpu_percent (Linux and Windows, from two counter samples 250 ms apart; not reachable on macOS without cgo), load1 (Linux and macOS; Windows has no load-average concept), mem_total_bytes/mem_available_bytes (all platforms; macOS approximates available as free pages × page size), disk_path/disk_total_bytes/disk_available_bytes (all platforms). Collection is best-effort per metric: anything unavailable is omitted, with the reason under the document's errors object; the metrics describe the Processor host, which is not necessarily the database host.
pgrelay._write_health(p_queue_id, p_request_type, p_reference, p_instance, p_host, p_os, p_data) — the Processor's answer path, called inside the same held transaction as the claim (the pg_relay.reload ceremony). Stamps node from pg_relay.node_id, normalises an empty reference to NULL, and returns the answer row's id. Joins the Processor operating set: granted by grant_relay(), checked by preflight() as a new required grant:_write_health row, and granted to the pgrelay role directly by the delta so existing installs pass with no operator action.
pgrelay.health_report(p_limit, p_reference, p_request_type) — the read surface: answers newest first, optionally filtered by exact reference and/or request type. Granted by grant_user().
The processes request type — the Processor host's process table into the same data document: the echoed search (when given), process_count (the full matched total), truncated, and processes — one object per matching process, worst-resident-memory-first, capped at the 800 largest by RSS (each command line capped at 2048 bytes, cmdline_truncated flagging a cut). Per process, as the platform and privilege allow: pid/ppid/name always; cmdline (on PostgreSQL backends this is the live status line — postgres: alice appdb … idle in transaction), username, state (D = stuck in I/O, Z = zombie), exe (on Linux (deleted) reveals a process running a since-replaced binary), rss_bytes, vsz_bytes, swap_bytes, threads, nice, cpu_seconds, cpu_percent (Linux: two samples 250 ms apart), started_at/elapsed_seconds, read_bytes/write_bytes, open_fds. Linux is first-class via a /proc walk; Windows (Toolhelp32) has no command line, so search matches the executable name only; macOS collects via /bin/ps — the one supported doorway to a kernel ABI the standard library cannot express. Caution: command lines can carry secrets passed as arguments — processor_health is private by design; do not relay process documents onward wholesale.
The os_config request type (Linux) — kernel release and OS name, glibc version (executing libc.so.6 itself — the one documented Linux exec exception, matching macOS's /bin/ps; collation-breaking glibc upgrades are the most notorious PostgreSQL silent killer, and comparing this across a primary and its standbys before an OS upgrade is the early warning), OS locale, transparent-huge-pages enabled/defrag modes (the other classic), the database-host vm.*/net.* sysctls (swappiness, dirty ratios, overcommit, somaxconn, TCP keepalives), cgroup version, SELinux/AppArmor mode, NUMA node count, auto_patching config-presence indicators (presence only — patch history belongs to patch-management tooling), and db_clock_offset_ms — the Processor's clock measured against the database's clock_timestamp() on the request's own connection, a relative-drift signal for audit-timestamp correlation (handler-injected; collectors themselves stay database-free).
The storage request type (Linux) — the request's paths (default: the Processor's working directory) each resolved to mount point, filesystem type, full mount options, device, and the whole disk beneath (device-mapper stacks followed through their slaves), with rotational, the active I/O scheduler, and luks (dm-crypt anywhere in the chain). Pass the database's data_directory and WAL paths to answer the shared-versus-dedicated-device and wrong-scheduler questions directly; nonexistent paths carry per-path errors, network filesystems report mount facts and omit device facts.
The network request type (Linux) — per non-loopback interface: MTU, link state, speed where reported, and bonding mode/slaves from /proc/net/bonding.
processes enrichment — each Linux process now also reports its effective resource limits (limits: nofile/nproc soft and hard from /proc/<pid>/limits — what the process actually got, the question limits.conf-versus-systemd-override comparisons are really asking) and its cpus_allowed/mems_allowed pinning.
pgrelay.purge_health(p_hours) — trims answers older than p_hours (default 168 = 7 days), returning the count; the purge_queue() of the health table. Granted by grant_user().
Go: the pg_relay.health event handler (internal/processor/health.go plus per-OS collectors health_linux.go / health_darwin.go / health_windows.go) — routed like sql/notify/pg_relay.reload in the dispatch worker; claim → collect → _write_health → audit → done in one held transaction; a malformed payload or unknown request_type resolves permanently (audit row, no retries burned). Collectors are stdlib only: procfs and statfs on Linux, sysctl and statfs on macOS, kernel32 (GetSystemTimes / GlobalMemoryStatusEx / GetDiskFreeSpaceExW) via syscall.NewLazyDLL on Windows. Success logs health_reported: <channel>, id: <queue id> at info; the payload — and therefore the reference — is never logged, per the standing rule.
PG_RELAY_START_NOTIFY and PG_RELAY_STOP_NOTIFY — start/stop lifecycle contracts joining the outage dead-man's switch (PG_RELAY_OUTAGE_NOTIFY — its rework from v1.2's prototype is under Changed below), same shape with a single message block, each independently choosing its transport. The start message is sent once, right after pg_relay started (one best-effort attempt; failure warns, never blocks). The stop message is sent on any deliberate exit — clean SIGTERM/SIGINT shutdown and fatal error exits (preflight failure, failed reload) alike — as one bounded attempt on the way out; a crash or kill -9 inherently sends nothing. New log lines: start_notify_sent/start_notify_failed, stop_notify_sent/stop_notify_failed.
Template variables in lifecycle-notification messages — message-block strings may embed {{variable}} tokens, expanded at send time with Processor-local facts that never require a database query (the outage message fires precisely when the database is gone): {{time}}/{{time_local}}, {{host}}, {{os}}, {{pid}}, {{instance}}, {{event}}, {{database}}/{{dbhost}}, and — in outage contracts only — {{outage_started}}, {{outage_seconds}}, {{outage_duration}} (1m 34s). Timestamp variables accept a PostgreSQL to_char()-style format after a colon ({{time:YYYY-MM-DD HH24:MI:SS}}; supported subset documented, literal letters double-quoted exactly as in to_char, Month/Day unpadded, OF always with minutes). Everything validates at startup: an unknown variable, a format on a non-timestamp variable, an outage-only variable in a start/stop contract, or an unrecognized to_char token all refuse startup naming the offender — a typo can never render garbage into a 3 a.m. page. Non-matching brace shapes pass through verbatim, so bodies aimed at systems with their own templating are never mangled; --test-notify prints the fully rendered message and so doubles as a template previewer.
Literal-brace escape variables {{open}} / {{close}} in lifecycle-notification messages — two new registry variables rendering the literal characters {{ and }}, for message bodies aimed at a downstream system whose own templating collides with the token shape: {{open}}incident_url}} (or the symmetric {{open}}incident_url{{close}}) lands on the wire as the literal {{incident_url}}. Expansion is a single pass, so the braces they emit are never re-expanded — {{open}}time}} reliably delivers {{time}}, not a timestamp. A bare trailing }} already passed through verbatim, making {{close}} purely cosmetic. Built into the binary like every other variable — nothing to configure or register.
--test-notify [VAR] + PG_RELAY_TEST_NOTIFY — the one notification tester (replacing --test-env). With a variable name (pg_relay --test-notify PG_RELAY_STOP_NOTIFY) it sends that production contract exactly as the daemon will fire it: an outage contract sends outage_message (or recovery_message with --recovery), a start/stop contract its message; email transports report the provider's reference in place of an HTTP status. Bare, it sends the complete, self-contained contract in PG_RELAY_TEST_NOTIFY — an administrator's connection test: transport + profile + a message with every key the transport requires, template variables included, a harmless message of your own before the production variables depend on the connection. The daemon never reads PG_RELAY_TEST_NOTIFY, so a broken test contract cannot affect a running Processor; validation and delivery are exactly the daemon's own; exit 0 only on a delivered send — a config linter and template previewer in one.

Changed¶

The GitLab CI test matrix additionally exercises PostgreSQL 18 on Ubuntu 26.04 (PGDG's resolute-pgdg dist) — the newest Ubuntu LTS, newest-PG-only for now, alongside the existing Fedora forward-visibility job.
pgrelay.purge() gained p_processor_health boolean DEFAULT true — the one scheduled purge call now applies its retention rule (age with p_hours, keep-the-newest-N with p_keep_quantity) to pgrelay.processor_health as well as pgrelay.log, returning the combined count. Pass false to leave health answers alone; purge_health() remains the targeted trim with its own retention. If you are upgrading: the parameter list changes, so the old two-argument purge(integer, integer) is dropped and recreated — any per-role grants made directly on it are reset; re-running pgrelay.grant_user(role) (already required by this release, below) restores them on the new signature.
BREAKING: outage alerting is now "lifecycle notifications", and PG_RELAY_OUTAGE_WEBHOOK is renamed to PG_RELAY_OUTAGE_NOTIFY with a new contract shape. The old variable is no longer read and the old top-level url/auth/outage_body layout no longer parses — the contract is now {"transport", "profile", "outage_message", "recovery_message"}, with the connection settings nested under profile and the messages renamed. The --test-env flag is likewise replaced by --test-notify [VAR]. (Accepted as a clean break: the prior shape and flag had no known deployments.)
Every lifecycle contract can now use any medium, not just a webhook. transport selects webhook (the default — JSON POST, hard-coded 2xx success, redirects never followed; the notifier webhook transport's body_merge is honoured with identical semantics — profile-owned keys, _env:-resolved, overlaid onto the message at send time and overwriting any same-named key, so a body-authenticating credential like PagerDuty's routing_key lives in an environment variable instead of literally in the contract; a non-object body_merge, or a non-object message while one is configured, refuses startup) or smtp / m365 / acs / gmail, whose profile and message blocks use the identical schemas as their pg_relay_notifier profiles and route through the very same transport senders — which work with the database down, since none of the email senders touch it (OAuth token endpoints are external HTTPS). A local SMTP relay with auth: "none" is the one medium with no external dependency at all. Email messages are validated at parse time (recipients, a body), so a typo refuses startup rather than warning per send; email Message-IDs are seeded deterministically from the outage episode's start (retries of one episode's message share an id for receiving-side dedup; recovery and start/stop messages get their own).
The contract's profile resolves _env:VAR_NAME references exactly like a notifier profile, so a credential lives in its own environment variable — typically the very one the notifier profile for the same provider already references — and rotation never edits the contract (a Processor restart is still required: the process environment is fixed at exec). The message blocks are sent as written — an _env: string inside one stays literal, mirroring the transports' "_env: resolves in profile, never in message" rule. An _env: reference to an unset variable refuses startup (notify_config_invalid, previously outage_config_invalid) or fails --test-notify, never surfacing mid-outage.

Upgrade notes¶

If you configured the pre-release PG_RELAY_OUTAGE_WEBHOOK, rewrite it as a PG_RELAY_OUTAGE_NOTIFY contract (new shape — see the Technical Guide's "Lifecycle Notifications" chapter) and change --test-env invocations to --test-notify.
grant_user() is recreated to additionally grant health_report() and purge_health(). Re-run pgrelay.grant_user(role) for each management role after upgrading, exactly as the 1.1 and 1.2 upgrade notes asked for their own additions. (grant_relay() is also recreated, but the delta grants the new _write_health() to the pgrelay role itself — no operator action needed for the Processor.)

[1.2.0]¶

pg_relay 1.2.0 pairs one focused extension change with a substantial Processor release.

On the SQL side: cancelling a queued event before the Processor selects it, and a way to find out which event to cancel. The extension changes are delivered as the upgrade script sql/pg_relay--1.1--1.2.sql — existing installs run ALTER EXTENSION pg_relay UPDATE;, fresh installs chain the 1.0 script and both deltas automatically.

On the Processor side: four additions to the 'notify' transports — the first two driven by Microsoft steering bulk/transactional email off Exchange Online toward Azure Communication Services (ACS) and deprecating Exchange Online's own SMTP AUTH in favor of OAuth2, the second two extending the same machinery to Google (sending through an existing Gmail / Google Workspace mailbox, over either SMTP or the Gmail REST API; design: AZURE_COMMUNICATION_SERVICES_DESIGN.md) — plus two webhook-transport profile fields (body_style, body_merge), out-of-band outage alerting for the one failure the queue itself can never report (the database being unreachable), and a Processor that waits instead of exiting when the database is unreachable at startup. The transport additions require paired pg_relay_notifier SQL support (the 'acs'/'gmail' transport values, auth = 'oauth2' profile validation, and the body_merge profile field) — separate work in that project and a hard prerequisite for them to be reachable in practice; until it ships, events targeting them resolve as clear permanent errors rather than being silently dropped.

Added¶

pgrelay.cancel(p_id bigint) — cancels queue row p_id if it has not yet been dispatched. Locks the row with FOR UPDATE SKIP LOCKED, so it never blocks: if the row is already dispatched (normally, or by an earlier cancel) or is locked right now by a concurrent claim or a concurrent cancel, it returns outcome = 'skipped' and changes nothing — the same convention process_one() already uses for an already-claimed row. On success it sets the new pgrelay.queue.cancelled_at column and dispatched_at to the same moment, writes a 'cancelled' pgrelay.log row, and returns outcome = 'cancelled'. Reusing dispatched_at (rather than inventing a status column) means every existing dispatch query — queue_has_work(), queue_probe(), queue_pending_ids(), _queue_claim() — excludes a cancelled row with no changes at all, and purge_queue() cleans one up exactly like a normally-processed row. Deliberately not scoped by the node-restriction/leadership dispatch predicate: a node-restricted or non-leader-owned pending row is still cancellable, because cancelling is not the same question as "who is allowed to dispatch this." Not part of grant_relay() or any preflight() check — the Processor itself never calls it; granted to producer/management roles via grant_user().
pgrelay.queue.cancelled_at — nullable timestamp, set only by cancel().
transport = 'acs' — a new first-class transport for Azure Communication Services' REST emails:send API (internal/processor/acs.go), structurally mirroring the existing m365 transport: client-credentials OAuth2 (tenant_id/client_id/client_secret/endpoint/from in profile) against the https://communication.azure.com/.default scope. ACS's response is asynchronous (202 Accepted with an operation id in the body, not a final delivery result) — treated as terminal sent, matching the M365 sendMail fire-and-forget precedent already in place; the Processor does not poll Operation-Location. provider_ref is the operation id. Unlike Graph (HTML wins when both are given), both body_text/body_html are sent simultaneously when present. Classification matches the existing SMTP/M365 taxonomy: 429/5xx/network transient, other 4xx permanent.
auth = 'oauth2' on the existing transport = 'smtp' (internal/processor/smtp.go) — a generalization, not an ACS-specific addition: a hand-rolled XOAUTH2 SASL mechanism (xoauth2Auth; no new dependency) authenticates using an Entra client-credentials token instead of a static password, read from a nested oauth2 profile block (username/tenant_id/client_id/client_secret/scope). Because scope is a profile field rather than hardcoded, the identical mechanism also serves as Exchange Online's own documented replacement for its SMTP AUTH deprecation — not just ACS's SMTP relay. An AUTH rejection has no HTTP-401-style refresh point the way M365/ACS do, so a failed send now explicitly invalidates that cache entry (invalidateOAuth2Token) rather than relying on natural expiry, bounding the blast radius of an out-of-band secret rotation to one failed send instead of up to an hour of them.
internal/processor/oauth2token.go — the M365 transport's token-cache code, generalized and extracted so acs and smtp's new oauth2 mode share it: getOAuth2Token(ctx, tokenURL, tenantID, clientID, secret, scope, force), cached on (tenant_id, client_id, scope) — scope joins the cache key so a tenant+client pair reused with different scopes across transports cannot collide. M365's own behavior is unchanged (same expiry slack, same refresh-on-401 flow).
transport = 'gmail' — a new first-class transport for the Gmail REST API's messages.send (internal/processor/gmail.go), for sending through an existing Gmail / Google Workspace mailbox (a shared team inbox, a small-scale internal notifier — Google has no ACS-style bulk product, and daily mailbox quotas apply). Authentication is the OAuth2 refresh-token grant — a one-time per-mailbox consent (token_url/client_id/client_secret/refresh_token in a nested oauth2 profile block), never a service-account JWT flow. The request wraps the same MIME message the SMTP transport builds (buildMIMEMessage, deterministic Message-ID included), base64url-encoded into {"raw": ...} and sent as users/me — the token's own mailbox; from must be that mailbox or one of its Send As aliases. bcc recipients ride as a Bcc header (the API has no SMTP envelope). Success is a synchronous 200 whose body carries the sent message's id as provider_ref — a real final result, unlike ACS's async 202. Classification: 429 and 403 (Gmail signals quota/rate exhaustion as 403) plus 5xx/network transient; other 4xx permanent; one automatic refresh-and-retry on 401.
grant_type selector on the SMTP oauth2 block (internal/processor/smtp.go) — the block's grant defaults to client_credentials (the existing ACS/Exchange shape, unchanged, no migration) and now also accepts refresh_token for Gmail / Google Workspace SMTP (smtp.gmail.com, smtp-relay.gmail.com): username/token_url/client_id/client_secret/refresh_token, with token_url a profile field because refresh-token providers use flat endpoints with no tenant segment. Google's SMTP servers accept the identical XOAUTH2 SASL string, so everything downstream — MIME building, STARTTLS, Message-ID — is untouched. An SMTP AUTH rejection invalidates whichever grant's cache entry the profile used (invalidateOAuth2RefreshToken joins invalidateOAuth2Token).
getOAuth2RefreshToken (internal/processor/oauth2token.go) — the refresh-token sibling of getOAuth2Token: same form-encoded POST, response shape, cache, and expiry slack, cached on (client_id, refresh_token) since one refresh token exists per consenting mailbox. Shared by the gmail transport and the SMTP refresh_token grant.
Out-of-band outage alerting (dead-man's switch) — the one failure pg_relay could never report through its own queue is the database being unreachable; now the Processor can. A webhook contract held in the (hardcoded) PG_RELAY_OUTAGE_WEBHOOK environment variable (url, the webhook transport's auth block, outage_body, optional recovery_body) is fired directly by the binary — no queue, no notifier, no SQL — after the database has been unreachable for --startup_timeout (default 120s, a Processor that has never connected: a patched server whose PostgreSQL should be coming back) or --stop_timeout (default 10s, an established connection lost mid-run). Once per outage episode (delivery retried until one 2xx lands, then latched); the recovery message fires only if the outage one was delivered, and resets the episode. Bodies are sent verbatim and never logged; delivery errors are stripped of the endpoint URL before logging (URL-keyed endpoints carry their credential in the URL). New pg_relay --test-env VAR [--recovery] mode sends a nominated contract through the identical code path with no database, printing the provider's response and exiting 0 only on a 2xx — a DBA smoke test and config linter in one. (internal/processor/outage.go; documented in the Technical Guide's "Outage Alerting" chapter.)
body_merge on the webhook transport — an optional profile object whose top-level keys the Processor overlays onto message immediately before encoding, unconditionally overwriting any producer-supplied value for the same key. It exists for APIs that authenticate inside the request body (PagerDuty's Events API routing_key; Vonage's legacy SMS api_key/api_secret): because the block lives in the profile, its _env: references resolve through the existing profile secret pass — the credential stays in the environment, never in the notifier's message data — and a producer has no way to name an environment variable or occupy the merged key. The overlay is shape-blind (top-level assignment only), works identically under both body_styles, and a non-object body_merge is a permanent failure with no request sent, mirroring the malformed-auth contract. Because the merged key always overwrites, the field doubles as pure enforcement for non-secret routing fields — e.g. pinning a Slack profile to a single channel regardless of what the producer supplies. The docs' former "any API that authenticates in the request body is unusable" rule is retired accordingly.
body_style on the webhook transport — an optional profile field selecting the wire encoding of message: "json" (the default; unchanged verbatim json.Marshal) or "form", which re-encodes a flat message as application/x-www-form-urlencoded for providers that reject JSON (Twilio's and Telesign's SMS APIs). Strings pass verbatim, numbers/booleans render in canonical JSON text, null is omitted, and an array of scalars repeats its key (Twilio's MediaUrl convention); a nested value or unknown style is a permanent failure with no request sent, mirroring the malformed-auth contract. Content-Type defaults to urlencoded under form, still overridable via profile.headers.

Changed¶

A database that is unreachable at Processor startup no longer exits the binary. The Processor now waits in-process, retrying with the normal reconnection backoff (so the outage alert above can fire and its once-per-episode latch survives the whole wait), and proceeds into normal startup when the database appears. Startup failures with the database reachable — a preflight error, a registry that predates the binary — still exit non-zero exactly as before. Operators who monitored for a non-zero exit as their "cannot connect at boot" signal should use the outage alert (or the reconnecting log lines) instead.
The 'notify' transports are now routed through a transportSenders registry in notify.go (uniform sendFunc signature) rather than a switch; case "acs" and case "gmail" became registry entries, and the unsupported-transport error text derives its list (acs, gmail, m365, smtp, webhook) from the registry keys.
oauth2ErrorSummary (internal/processor/oauth2token.go) now reads all three error envelopes seen across the OAuth2 transports: the {"error":{"code","message"}} object shape with a string code (Graph, ACS) or a numeric code (Gmail), and the flat RFC 6749 token-endpoint shape {"error":"<code>","error_description":"..."} that both Microsoft's and Google's token endpoints answer with — the last of which the previous parser reported as unrecognised error response whenever the flat error string was present, hiding e.g. invalid_client/invalid_grant codes from the audit trail.
pgrelay.notify() now returns bigint instead of void — the id of the pgrelay.queue row that now represents the event, so a caller has a way to name what it just sent for a later cancel() call. Previously nothing gave a caller that id; only a role with raw access to the private pgrelay.queue table could discover one. Every existing call site keeps working unchanged (PERFORM discards a return value of any type; a bare top-level SELECT just shows an extra column). Under p_deduplicate, if an identical pending row already existed and the insert was suppressed, notify() returns that row's id instead of NULL — the return value always names something a caller can pass straight to cancel(). PostgreSQL does not allow changing a function's return type via CREATE OR REPLACE, so the upgrade script drops and recreates notify(); unlike every management function this release also touches, its PUBLIC EXECUTE grant is restored automatically by the script itself — no operator action needed for notify() specifically. pgrelay.grant_user() was extended to also grant cancel(); if you are upgrading, re-run pgrelay.grant_user(role) for each producer/management role to pick it up.
v1.2 SQL test suite test/pg_relay_test--1.2.sql — replaces the 1.1 suite as the suite CI runs (make test-sql). Adds the 1.1→1.2 leg of the upgrade-path test and nine new tests (91–99) covering cancel()'s outcomes, its interaction with purge_queue(), a genuine cross-session FOR UPDATE SKIP LOCKED proof via dblink, its deliberate absence from the Processor's own grant/preflight surface, and notify()'s new return value including the dedup-suppressed case.
Release tarball is now pg_relay--1.2.sql.tar.gz, containing pg_relay.control, sql/pg_relay--1.0.sql, sql/pg_relay--1.0--1.1.sql, and sql/pg_relay--1.1--1.2.sql.

[1.1.0]¶

Five features: per-channel dispatch concurrency, Processor fleet control (reload and pause), external notification support via the companion pg_relay_notifier extension, a generic private options store (pgrelay.options) for DBA-configurable settings, and a shared recurrence type (pgrelay.schedule) for pg_relay and its related applications. SQL changes to the extension itself are delivered as the extension upgrade script sql/pg_relay--1.0--1.1.sql — existing installs run ALTER EXTENSION pg_relay UPDATE;, fresh installs chain the 1.0 script and the delta automatically. The Processor binary gains the fleet-control behaviours and the notification transports. pgrelay.schedule is the exception to the upgrade-script story — see its entry below.

Added¶

pg_relay_notifier compatibility — pg_relay v1.1 works with pg_relay_notifier 1.0, which caters for SMTP email and Microsoft 365 email via the Graph API. The Processor routes action_type 'notify' events through the interface specified in NOTIFIER_INTERFACE.md: the queue payload is a primary key into the notifier's tables; the Processor fetches the message through pgrelay_notifier.fetch(), delivers it (SMTP with the full TLS/auth matrix, HTML/plain/multipart bodies, attachments, CC/BCC/Reply-To; or Graph sendMail with client-credentials OAuth and token caching), and writes per-attempt status back through pgrelay_notifier.set_status(). The whole claim→fetch→send→status sequence runs in one held transaction, so delivery is at-least-once and concurrency_mode applies to notification channels exactly as to SQL channels. Transient failures (SMTP 4xx, Graph 429/5xx, timeouts) use the existing retry chain; permanent failures (SMTP 5xx, Graph 4xx, bad configuration) resolve immediately. Send timeout: profile-controlled, default 30s, hard cap 120s. Secrets are read from Processor-host environment variables at send time — never stored in the database. Message content is never logged by the Processor. Per-notification debug tracing writes live step rows through pgrelay_notifier.debug_log() on a separate autocommit connection.
pgrelay.processor_control — one-row declarative fleet state: reload_token (uuid; edge-triggered reload signal) and desired_state/pause_until (level-triggered pause). Mutated only through functions; observed by every Processor each second.
pgrelay.queue_probe() — the combined per-tick probe returning (has_work, reload_token, paused) in one round trip (~1µs over queue_has_work(), which remains for monitoring).
pgrelay.request_reload() — replaces the reload token; every Processor performs a full reload (reconnect, re-read application registry, re-run preflight) at its next probe. Also invoked by the new reserved pg_relay.reload action type: register a channel with it and pgrelay.notify() a reload — schedulable via p_run_at, audited in pgrelay.log. The pg_relay.* action_type prefix is reserved (register_action_type now rejects it).
pgrelay.start() / pgrelay.stop() / pgrelay.pause_for(seconds) / pgrelay.pause_to(timestamptz) — fleet pause control. Paused Processors finish their in-flight batch, keep probing, and claim nothing; a bounded pause self-expires database-side; last write wins; updated_by/updated_at audit every change.
Application-registry startup gate — the Processor now calls pgrelay.list_applications() at startup (and on every reload) and refuses to start if the call fails, catching binary/extension version mismatches immediately with an actionable message.
Registry-scoped preflight checks — when pg_relay_applications contains a pg_relay_notifier row, preflight() verifies the notifier schema, the four interface functions, and the pgrelay role's EXECUTE on them (notifier:* checks, WARN class). Two new ERROR-class grant checks cover queue_probe/request_reload.
NOTIFIER_INTERFACE.md — the complete interface specification pg_relay_notifier 1.0 is built against.
pgrelay.actions.concurrency_mode — per-channel dispatch concurrency control: 'concurrent' (default; no restriction — identical to 1.0 behaviour), 'channel' (at most one in-flight event per channel, strictly in queued order), and 'channel_payload' (at most one in-flight event per (channel, payload) pair; different payloads still run concurrently). Enforced in queue_pending_ids(): each poll offers at most one candidate per exclusivity group — always the oldest eligible row — so the guarantee holds across any number of workers and Processor instances. A withheld event is silent: no log row, no retry_count change; it is simply offered on a later poll. NULL payloads group per channel (never across channels); channel grouping is case-insensitive, payload grouping exact.
p_concurrency_mode parameter on pgrelay.register() (default 'concurrent') and pgrelay.update() (default NULL = unchanged), validated against the three modes. get()/list() surface the new column.
pgrelay.queue_stats(p_minutes int DEFAULT 1, p_order_by text DEFAULT 'action') — new monitoring function returning one row per (action_type, channel) pair with either pending queue rows or log activity in the last p_minutes minutes. Columns: action_type, channel, pending (eligible now, excluding expired), future (deferred), pending_restricted (undispatched node-pinned rows, counted regardless of run_at/expire_at — the alerting hook for rows stuck behind a dead/decommissioned node), oldest_pending (key alerting interval), processed (ok log rows), expired, errors (error+invalid+unsupported_action_type log rows). p_order_by='action' (default) sorts by action_type, channel; 'count' sorts by pending DESC. EXECUTE revoked from PUBLIC; grant explicitly to monitoring roles.
Node-restricted channels — a per-channel node_restricted flag (register(p_node_restricted := true) / update()) for actions with node-local side effects (e.g. a materialised view refresh, whose heap is not logically replicated — every node must run its own copy). Queue rows for a flagged channel are stamped with the creating node's pg_relay.node_id (new pgrelay.queue.run_in_node column, populated by a BEFORE INSERT trigger so direct queue inserts and retry rows are covered too — never caller-supplied, origin-only so replicated rows keep their pin) and are dispatched only by that node's Processor in every --mode: never handed to the leader, never adopted or reassigned by the multi-node watchers. p_deduplicate suppresses per node on a restricted channel (one pending copy per node) and stays cluster-wide on unrestricted ones; retries inherit the pin; concurrency_mode serializes per node for restricted channels. In leader mode a non-leader Processor no longer idles — it dispatches its own node-restricted rows every tick (leadership is published per connection as the pg_relay.is_leader GUC and enforced by the SQL dispatch predicate in queue_pending_ids()/_queue_claim()); only unrestricted rows remain leader-only. A pinned row on a permanently dead node never runs and never expires — decommissioning requires the manual cleanup documented in the Multi-Master Deployment book on the documentation site §9. Single-node deployments are unaffected. Design reference: the Node-Restricted Channels chapter of the Multi-Master book.
pgrelay.options — a generic, private key/value settings table for pg_relay itself and for companion applications/developer code to persist DBA-configurable options, plus pgrelay.set_option(p_option_name, p_option_value) (creates the option on first call, updates it on every subsequent call — upsert semantics) and pgrelay.get_option(p_option_name) (returns the current value, or NULL if the option has never been set — never raises). Neither the table nor either function is granted to PUBLIC; grant_user() covers both. Purely additive infrastructure — v1.1 does not read or write any option itself.
pgrelay.schedule — a validating domain (pgrelay.schedule over the composite pgrelay.schedule_type) describing a recurring schedule: every matching weekday, the nth weekday of the month, every N weeks, specific days of the month, or (new) independent per-day times (day_times, e.g. 'mon=05:00;tue=17:30,20:00;wed=12:00') — each mode carrying one or more UTC times except day_times, where each group carries its own. Ships in schedule/ as a separate, manually-installed companion, versioned independently in schedule/CHANGELOG.md — like the options store, pg_relay itself never reads or writes a pgrelay.schedule value, and installing it changes nothing about pg_relay's own behaviour; unlike the other v1.1 additions, it is not part of sql/pg_relay--1.0--1.1.sql and does not install via CREATE EXTENSION/ALTER EXTENSION UPDATE — run schedule/install.sql yourself after the extension is installed, and it's always safe to re-run (it detects what's installed and upgrades in place — no more unconditional DROP ... CASCADE, which was a real, reproduced hazard against a database with consumer tables using this type; schedule/uninstall.sql likewise now refuses to cascade into consumer data by default). Constructors (pgrelay.weekdays(), pgrelay.nth_dow(), pgrelay.fortnightly(), pgrelay.on_dom(), pgrelay.on_day_times(), pgrelay.month_end(), and friends) build and validate a schedule at the call site; evaluators (pgrelay.fires_on(), pgrelay.occurrences(), pgrelay.next_run(), pgrelay.matches(), pgrelay.describe()) answer "does/when/is this due," each also available as a pgrelay.schedule[] overload for combining several schedules (including mixed modes) into one answer. Intended as the watermark for a next_run_at column in your own scheduled-job table — see schedule/README.md for the full pattern, including how to connect a due watermark to pg_relay's actual queue via pgrelay.notify(). Verified against PostgreSQL 14–18, under three session timezones with byte-identical output, and against 6.4M+ evaluations of an independent Python differential test — see schedule/VERIFICATION.md for the full report. Documented for DBAs and developers in the docs/ site under Schedule Type.
v1.1 SQL test suite test/pg_relay_test--1.1.sql — replaces the 1.0 suite as the suite CI runs (make test-sql). Adds the 1.0→1.1 upgrade-path test and twelve dispatch-concurrency tests, including genuine cross-session lock tests via dblink (test-time dependency only). pgrelay.schedule has its own separate test suite — schedule/run_tests.sh — since it is not part of the extension.

Changed¶

pgrelay.register() and pgrelay.update() signatures gained the p_concurrency_mode and p_node_restricted parameters. Upgrade note: the old signatures are dropped and recreated by the upgrade script, which discards per-role EXECUTE grants on those two functions — re-run pgrelay.grant_user(role) for each management role after upgrading. pgrelay.queue_stats() is likewise dropped and recreated (new pending_restricted output column) — re-grant it to monitoring roles.
pgrelay.notify() recreated with the duplicate-suppression key extended from (channel, payload) to (channel, payload, run_in_node) — a no-op for unrestricted channels (all rows compare NULL = NULL), per-node dedup for node-restricted ones. The PUBLIC grant is preserved. notify() also stamps run_in_node explicitly in its INSERTs: the stamping trigger is origin-only and therefore does not fire when notify() is called during logical apply (a companion's ENABLE ALWAYS trigger enqueuing local work while a replicated row is applied — e.g. pg_auto_mv's TRUNCATE-signal handler); without the explicit stamp those rows would be silently unrestricted and run on the wrong node under --mode=leader. On origin the trigger recomputes the identical value, so the two stamps can never disagree.
The Processor refuses to start in --mode=multi-node/leader when pg_relay.node_id resolves to 0 (unset) — two nodes both defaulting to 0 would own (and pin) each other's rows. Set a distinct id per node: ALTER SYSTEM SET pg_relay.node_id = '<n>'; SELECT pg_reload_conf();
pgrelay.grant_relay() additionally grants queue_probe() and request_reload() (the extended Processor operating set); pgrelay.grant_user() additionally grants the fleet-control functions (request_reload, start, stop, pause_for, pause_to).
The Go dbConn surface gains Exec (BEGIN/COMMIT/SAVEPOINT for the held notify/reload transactions); the per-tick probe switches from queue_has_work() to queue_probe() with named-column selection.
The pg_relay application-registry record (pgrelay.pg_relay_applications) is updated to latest_version = '1.1.0' by the upgrade script.
Release tarball is now pg_relay--1.1.sql.tar.gz, containing pg_relay.control, sql/pg_relay--1.0.sql, and sql/pg_relay--1.0--1.1.sql.
Multi-master support via a new --mode parameter (single | multi-node | leader; default single). single is the v1.0 posture plus follow-the-primary HA (idle on a read-only standby, dispatch on promotion). multi-node is owner-partitioned active-active — each node dispatches its own rows in parallel and a conservative per-tick watcher adopts a dead node's rows to the lowest live master. leader runs a single cluster-wide dispatcher (the lowest live write-master processes every unrestricted row; node-restricted rows — see above — always stay on their own node). Supporting changes: pgrelay.queue.id and pgrelay.log.id default to a snowflake bigint (pgrelay._next_id()) so both tables are safe to replicate (cluster-wide audit); a new owner_node column and a mode-aware ownership filter in queue_pending_ids()/_queue_claim(); per-mode SQL gate functions (should_dispatch_single, is_lowest_live_master, multi_master_watch, _startup_queue_check) that the binary calls opaquely, so all recovery/Spock logic lives in SQL; and removal of the old preflight replication warning (snowflake ids make replication safe). Adoption is conservative — a peer is dead only once its replication slot is active=false (after wal_receiver_timeout), never on lag — with the guarantee that every eligible row runs at least once eventually or expires. Split-brain under a sustained partition is documented-and-accepted (no self-fencing). Full operator reference: the Multi-Master Deployment book on the documentation site. Single-node (--mode=single, the default) is entirely unaffected — pg_relay.node_id unset ⇒ node 0, every filter a no-op, identical to before. This is in-core pgrelay SQL + the Processor; there is no separate extension.
Go toolchain bumped from go1.24.4 to go1.26.5. The 1.24.x branch reached end-of-life when Go 1.26 released (its final patch was 1.24.13); the pinned go1.24.4 was nine security-relevant patch releases behind within that now-dead branch, including fixes to crypto/tls and net/textproto — both exercised directly by the SMTP transport (internal/processor/smtp.go) — as well as net/http and net/url, used by the webhook and M365 transports. The go directive (minimum language version, 1.23.0) is unchanged; only the toolchain pin moved, so this is a pure security/currency update with no source-level breaking changes. No SQL files are affected.
GitLab CI test matrix adds fedora:44, scoped to PG_VERSION: ['18'] only rather than the full version sweep. Fedora tracks PGDG's yum-family packages fastest — a new PostgreSQL major version's -devel package often lands there before EL (Rocky/RHEL) catches up, which is exactly the forward-visibility purpose this covers. Fedora is not a supported production platform (its ~13-month release support window rules that out; see Platform and Version Support) — this is CI coverage only, not a new deployment target. Required a dedicated before_script branch: Fedora uses PGDG's pgdg-fedora-repo package and F-<ver> path scheme (not pgdg-redhat-repo/EL-<ver>), and needs neither EPEL nor CRB — both RHEL-family-only concepts, since whatever CRB supplies there (e.g. perl(IPC::Run) for postgresql-devel) already ships in Fedora's own repos.

[1.0.0]¶

Initial release. pg_relay is a durable event processor for PostgreSQL: producers enqueue events with pgrelay.notify(), and a companion binary — the Processor — polls the durable queue once per second and runs each event's registered action. There is no LISTEN/NOTIFY in the dispatch path; the Processor finds work by polling.

Extension (`pgrelay` schema)¶

Action type registry (pgrelay.action_types) — extensibility foundation. Seeded with 'sql' (run_order 1). list_action_types() is PUBLIC (read-only). register_action_type / update_action_type / delete_action_type management functions require explicit grants. delete_action_type is blocked if any channel references the type. Action types carry a run_order that controls dispatch ordering within a batch (NULL sorts last).
Channel registry (pgrelay.actions) with case-insensitive channel names, per-channel max_retries (0–99), per-channel action_type (FK to action_types, default 'sql'), and register / update / enable / disable / unregister / get / list management functions. register and update accept a p_action_type parameter and validate it against action_types.
Durable event queue (pgrelay.queue) written by pgrelay.notify() inside the producer's transaction. Supports deferred events (p_run_at), expiry (p_expire_at), deduplication (p_deduplicate), and automatic retries with backoff (3 s, 5 s, 10 s) via parent_id/retry_count. notify() validates the channel before enqueuing and raises on an unregistered channel.
Per-event processing: queue_has_work() (cheap probe), queue_pending_ids() (returns TABLE(id bigint, action_type text) with FOR UPDATE OF queue SKIP LOCKED, ordered by run_order NULLS LAST, run_at, queued_at), and process_one(id) — which claims one row, runs its action in a savepoint, writes the audit row, and returns (channel, outcome, log_id). Outcomes: ok, error, retry_scheduled, expired, invalid, unsupported_action_type, skipped.
Audit log (pgrelay.log) with one row per processed event, carrying the originating queue_id and the status.
Maintenance: purge (log) and purge_queue (dispatched rows only).
Security model: private tables (all privileges revoked from PUBLIC — now four tables including action_types), SECURITY DEFINER helpers with a fixed search_path, SECURITY INVOKER process_one (action SQL runs as the pgrelay role, never the owner), and EXECUTE hardening — every function revoked from PUBLIC except notify() and list_action_types(). grant_relay() grants the Processor operating set; grant_user() grants the management set (channel and action type management); preflight() self-checks grants and replication configuration at startup.

Processor (Go binary)¶

Poll-only design: a 1-second ticker that probes the queue and processes each eligible event individually. No LISTEN, no channel set, no reload.
queue_pending_ids() now returns (id, action_type) pairs. The Processor routes each event by action_type (type switch); 'sql' goes to processSQL; unknown types delegate to process_one which returns unsupported_action_type. This is the extensibility hook for future non-SQL action handlers.
Concurrent dispatch: --workers N (1–9, default 1) opens N database connections and fans pending events across them. Multiple Processors run against one database and partition work via FOR UPDATE SKIP LOCKED; each claims an instance slot (1–64) and stamps every log line with "instance": N.
Structured JSON logging at warn by default (--verbose → info, --debug → debug). The payload is never logged; only channel, queue id, and log id appear.
Automatic reconnect with backoff; credentials re-read from libpq env vars on every connect, so rotated credentials take effect without a restart. Clean SIGTERM/SIGINT shutdown.
Cross-platform builds (Linux/macOS/Windows, x86_64/arm64) and GitLab CI.

Changelog¶

[Unreleased]¶

Added¶

Changed¶

[1.5.0] - 2026-09-05¶

Added¶

Changed¶

Deferred¶

[1.4.0]¶

Added¶

Changed¶

[1.3.0]¶

Added¶

Changed¶

Upgrade notes¶

[1.2.0]¶

Added¶

Changed¶

[1.1.0]¶

Added¶

Changed¶

[1.0.0]¶

Extension (pgrelay schema)¶

Processor (Go binary)¶

Extension (`pgrelay` schema)¶