Skip to content

Creating a Notification

SELECT pghf.create_notification(
    p_notification_name text,               -- a label; every firing carries it
    p_delivery_kind     text,               -- 'outbox' | 'notify' | 'pg_relay_notify' | 'function'
    p_check_id          text    DEFAULT NULL,  -- scope: one check ...
    p_namespace_code    text    DEFAULT NULL,  -- ... or a category (with p_category_code) ...
    p_category_code     text    DEFAULT NULL,  -- ... or neither = GLOBAL
    p_trigger_mode      text    DEFAULT 'on_change',   -- 'on_change' | 'on_state'
    p_severities        text[]  DEFAULT NULL,  -- the exact set that qualifies ...
    p_min_severity      text    DEFAULT NULL,  -- ... OR a floor (expands to rank-≥ set)
    p_fire_on_clear     boolean DEFAULT false, -- on_change only
    p_cooldown_minutes  integer DEFAULT 0,     -- throttle (on_state only)
    p_run_sequence      integer DEFAULT 100,
    p_channel_name      text    DEFAULT NULL,  -- required for 'notify'
    p_routine           regprocedure DEFAULT NULL,  -- required for 'function'
    p_function_args     jsonb   DEFAULT NULL,  -- optional, 'function' only
    p_is_active         boolean DEFAULT true
);
-- => returns the new notification_id (bigint)

Everything is validated immediately — get one wrong and creation raises a specific error, not a generic constraint violation.

The severity set: what qualifies

A notification fires on confirmed severity — the post-debounce value, the same one the reporting functions and events respect (see How Firing Actually Works). Which severities qualify is a set, given one of two ways:

  • p_severities => ARRAY['critical'] — an explicit set (any of the eight ladder values).
  • p_min_severity => 'warning' — a floor: expands to warning and everything that ranks worse (invalid, critical, error_in_performing_check), matching pghf.list_alert_status()'s floor semantics.

An explicit set is what lets on_state route a different routine per severity result — one notification on ARRAY['critical'], another on ARRAY['ok'].

Scope: check, category, or global

Exactly one of: p_check_id (one check), p_namespace_code + p_category_code (one category), or neither (global — every check). One global row expresses "anything reaching the set, anywhere." Any existing check or category, built-in or your own.

Trigger mode: when it fires

  • p_trigger_mode => 'on_change' (default) — fire once on the transition into the set (a 'breach'), and — with p_fire_on_clear => true — once when it leaves (a 'clear'). For persistent, polled conditions.
  • p_trigger_mode => 'on_state' — fire on every in-set evaluation (a 'state' firing). For on-event checks (one notification per occurrence) and nag reminders. Throttle with p_cooldown_minutes (engine-guaranteed; anti-flap — an intervening ok never resets it).

p_fire_on_clear is meaningless for on_state and is rejected there.

Delivery kind: how it's delivered

Every firing lands in the durable outbox pghf.notification_log 'pending' first. Then:

outbox

SELECT pghf.create_notification('crit outbox', 'outbox', p_min_severity => 'critical');

Nothing beyond the journal row. A pull consumer (your own daemon, a cron job, pg_relay_notifier) drains pghf.list_pending_notifications() and acknowledges each with pghf.mark_notification_delivered(). The most reliable shape: a missed poll loses nothing, the consumer can be down for an hour and catch up, and every firing is auditable.

notify (standard PostgreSQL NOTIFY)

SELECT pghf.create_notification('crit doorbell', 'notify', p_min_severity => 'critical', p_channel_name => 'pghf_alerts');

LISTEN pghf_alerts;
-- ... PGHF11-009 breaches ...
-- Asynchronous notification "pghf_alerts" with payload "17" received.
SELECT * FROM pghf.list_notification_log(p_limit => 1);   -- outbox row 17: the full firing

Plain core PostgreSQL — nothing to do with pg_relay. Same as outbox, plus a standard pg_notify(channel, notification_log_id) doorbell — the payload is deliberately just the outbox row's id, because a NOTIFY caps at 8000 bytes and a missed one is gone forever. The durable, self-contained payload rides the outbox row, not the NOTIFY. If nothing is listening the doorbell is simply dropped — the row is still 'pending' for a pull consumer. A database NOTIFY is not a queue; the outbox is what makes delivery at-least-once-able.

pg_relay_notify (via pg_relay)

SELECT pghf.create_notification('crit relay', 'pg_relay_notify', p_min_severity => 'critical', p_channel_name => 'pghf_alerts');

The pg_relay counterpart of notify: instead of a standard pg_notify, it enqueues the outbox row's id via pg_relay's own pgrelay.notify(channel, id), so pg_relay's workers deliver it — durable, async, and parallel across workers. This kind requires the pg_relay extension: choosing it when pg_relay isn't installed is an error, so create_notification()/update_notification() reject it up front rather than letting it silently do nothing at firing time. (A runtime problem — the pg_relay channel not being registered/active — is recorded on the outbox row as 'failed' with the error and a warning, surfaced but not aborting evaluation.) The row stays 'pending' until a pg_relay worker delivers it and acks via pghf.mark_notification_delivered(). Register the pg_relay channel and its delivery action separately (see pg_relay and Wiring in Delivery).

function

CREATE OR REPLACE FUNCTION my_schema.notify_slack(
    p_notification_log_id bigint, p_evaluation_id bigint, p_args jsonb
) RETURNS void
LANGUAGE plpgsql AS $$
DECLARE
    v RECORD;
BEGIN
    SELECT * INTO v FROM pghf.notification_context(p_evaluation_id);   -- one-call context
    -- ... call out however your environment does (an HTTP extension, a
    -- bridge process, pg_relay_notifier) using v.check_id / v.severity /
    -- v.record_pk / v.source_table / v.message and p_args ...
    PERFORM pghf.mark_notification_delivered(p_notification_log_id, true, 'sent to slack');
END;
$$;

SELECT pghf.create_notification('slack crit', 'function', p_check_id => 'PGHF11-009',
    p_severities => ARRAY['critical'],
    p_routine       => 'my_schema.notify_slack(bigint, bigint, jsonb)'::regprocedure,
    p_function_args => '{"webhook_url": "https://hooks.example.com/..."}'::jsonb);

Every 'function' routine must have exactly (p_notification_log_id bigint, p_evaluation_id bigint, p_args jsonb) RETURNS void — verified against PostgreSQL's own catalog at registration, so a wrong signature is rejected then, not discovered later. The engine writes the outbox row 'pending' first, runs the routine inline, then marks it 'delivered' (unless the routine already reported an outcome via pghf.mark_notification_delivered(), or raised — in which case it's marked 'failed' with the error). p_function_args lets one routine back many notifications — the same function, registered many times with different args, sends to many destinations.

Failure isolation: a raising routine is caught, logged 'failed' with its error, a warning is emitted, and the next notification still fires — it never aborts the evaluation run. A broken notifier degrades to "this one stopped working," not "the whole run failed," and the failure is durable in the outbox (unlike a lost NOTIFY).

Continue to How Firing Actually Works.