Skip to content

Wiring in Delivery (and pg_relay_notifier)

Everything in this book so far produces signals — outbox rows, doorbell notifications, function calls. Turning a signal into an email, a Slack message, or a PagerDuty incident is deliberately outside this framework. There are two delivery styles, and they meet at the one durable outbox:

  1. Your own delivery — no external extension. Either a function notification whose routine calls whatever your environment uses, or an outbox (or notify) notification plus your own consumer that drains pghf.list_pending_notifications().
  2. pg_relay_notifier — the sister extension purpose-built for real delivery (email/Slack/PagerDuty/webhooks), which is simply one such consumer. Optional; nothing here requires it.

Wire in your own delivery (generic)

The durable outbox is the single seam. A consumer — cron job, a LISTEN loop on a notify channel, a pg_relay channel action — drains the pending rows and hands each to whatever you use, acknowledging as it goes. This works for any transport, pg_relay_notifier or not:

SELECT pghf.create_notification('alerts', 'outbox', p_min_severity => 'warning', p_fire_on_clear => true);

CREATE OR REPLACE PROCEDURE my_schema.deliver_pending()
LANGUAGE plpgsql AS $$
DECLARE r RECORD;
BEGIN
    FOR r IN SELECT * FROM pghf.list_pending_notifications(p_limit => 100) LOOP
        BEGIN
            -- r.payload is self-contained: check_id, transition, new_status,
            -- message, record_pk, source_table, ... — no joins needed.
            PERFORM my_transport.send(r.payload);
            PERFORM pghf.mark_notification_delivered(r.notification_log_id, true);
        EXCEPTION WHEN OTHERS THEN
            PERFORM pghf.mark_notification_delivered(r.notification_log_id, false, SQLERRM);
        END;
    END LOOP;
END;
$$;

Because the outbox is durable, this survives everything: a missed doorbell doesn't matter (the row is still pending), the consumer can be down and catch up, failures are recorded and re-driveable (mark_notification_delivered() is re-markable), and pghf.list_notification_log() answers "did we notify, and was it delivered?" end to end. Swap my_transport.send for anything — this is the whole "wire in your own notification system" story.

Push instead of poll. The consumer above polls; to be woken instead, use a notify notification (a standard PostgreSQL NOTIFY of the row id) and have your consumer LISTEN on the channel — the outbox is still the safety net if it misses one. And if you run pg_relay, a pg_relay_notify notification hands delivery to pg_relay's own workers: it enqueues the row id on a pg_relay channel, and the channel's registered action (which you can point at pgrelay_notifier.send() below, or your own handler) delivers and acks — durable and parallel, no consumer of your own to run. (pg_relay_notify requires the pg_relay extension; registering it without pg_relay is rejected outright.)

What pg_relay_notifier gives you

One call — pgrelay_notifier.send(profile, recipients, subject, body, ...) — composes a notification that commits with your transaction and is delivered outside it by the pg_relay Processor, with retries and a full audit trail. A rolled-back transaction sends nothing; a committed one is at-least-once. That transactional-and-async shape makes it safe to call from inside a notification routine (the call is instant; no external server is touched from your evaluation transaction) or from an outbox consumer. Setup is on the notifier's side (profiles, channels, the sender grant); everything below assumes a profile 'mailer' and that the delivering role holds the sender grant.

Pattern A — an on_change function: email on breach and recovery

CREATE OR REPLACE FUNCTION my_schema.email_on_transition(
    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);
    PERFORM pgrelay_notifier.send(
        p_profile   => p_args ->> 'profile',
        p_to        => ARRAY[p_args ->> 'to'],
        p_subject   => format('[pghf] %s (%s) is %s', v.check_name, v.check_id, v.severity),
        p_body_text => format(E'%s\nrun: %s', coalesce(v.message, ''), v.run_id));
    PERFORM pghf.mark_notification_delivered(p_notification_log_id, true, 'emailed');
END;
$$;

-- one GLOBAL notification: anything reaching critical, anywhere — and its recovery
SELECT pghf.create_notification('email crit', 'function', p_min_severity => 'critical',
    p_routine       => 'my_schema.email_on_transition(bigint, bigint, jsonb)'::regprocedure,
    p_function_args => '{"profile": "mailer", "to": "[email protected]"}'::jsonb,
    p_fire_on_clear => true);

Breach sends "… is critical"; the fire_on_clear firing sends "… is ok". The routine doesn't need to know which transition — but if your message must say BREACH vs CLEAR, the outbox row's transition column (Pattern B, or a pghf.list_notification_log() lookup) has it.

Pattern B — the outbox consumer

Exactly the generic consumer above, with my_transport.send(r.payload) replaced by a pgrelay_notifier.send(...) built from r.payload. Fully decoupled from evaluation time, catch-up-able, auditable — the recommended production shape, and the one a pg_relay deployment gets almost for free.

Pattern C — on_state per occurrence: one message per on-event occurrence

For an on-event check, each occurrence is its own event — and on_state fires (and emails) for every one, with the offending row right there in notification_context():

CREATE OR REPLACE FUNCTION my_schema.email_each_occurrence(
    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);
    PERFORM pgrelay_notifier.send(
        p_profile   => p_args ->> 'profile',
        p_to        => ARRAY[p_args ->> 'to'],
        p_subject   => format('[pghf] %s: %s in %s', v.severity, v.check_id, v.source_table),
        p_body_text => format('offending row: %s%sdetail: %s', v.record_pk, E'\n', v.event_detail));
    PERFORM pghf.mark_notification_delivered(p_notification_log_id, true);
END;
$$;

SELECT pghf.create_notification('job failed email', 'function', p_check_id => 'X01-003',
    p_trigger_mode => 'on_state', p_severities => ARRAY['critical'],
    p_routine => 'my_schema.email_each_occurrence(bigint, bigint, jsonb)'::regprocedure,
    p_function_args => '{"profile": "mailer", "to": "[email protected]"}'::jsonb);

Pattern D — an on_state nag reminder

"Still critical — remind me every 4 hours" is on_state with a cooldown; the routine is written just like Patterns A and C, but joins the pghf.alert_status projection for "how long / how many runs / last ok":

CREATE OR REPLACE FUNCTION my_schema.nag_while_critical(
    p_notification_log_id bigint, p_evaluation_id bigint, p_args jsonb
) RETURNS void
LANGUAGE plpgsql AS $$
DECLARE v RECORD; a pghf.alert_status;
BEGIN
    SELECT * INTO v FROM pghf.notification_context(p_evaluation_id);
    SELECT * INTO a FROM pghf.alert_status WHERE check_id = v.check_id;
    PERFORM pgrelay_notifier.send(
        p_profile   => p_args ->> 'profile',
        p_to        => ARRAY[p_args ->> 'to'],
        p_subject   => format('[pghf] REMINDER: %s still %s', v.check_id, v.severity),
        p_body_text => format('%s for %s (%s runs). Last ok: %s.', a.alert_name,
                          pghf.friendly_time(now() - a.current_status_first_at),
                          a.current_status_repeat_count,
                          coalesce(pghf.friendly_time(now() - a.last_ok_at) || ' ago', 'never')));
    PERFORM pghf.mark_notification_delivered(p_notification_log_id, true);
END;
$$;

SELECT pghf.create_notification('critical reminder', 'function', p_check_id => 'PGHF11-009',
    p_trigger_mode => 'on_state', p_severities => ARRAY['critical','error_in_performing_check'],
    p_routine => 'my_schema.nag_while_critical(bigint, bigint, jsonb)'::regprocedure,
    p_function_args => '{"profile": "mailer", "to": "[email protected]"}'::jsonb,
    p_cooldown_minutes => 240);   -- at most one per 4 hours; flapping can't beat this

No spam risk from a faster polling loop

If you run a tight p_critical_only loop (see Scheduling) to expedite recovery, polling a still-critical polled check more often doesn't flood anything: an on_change notification fires only on a transition (re-evaluating a check that stays critical produces none), and an on_state notification's cooldown is engine-guaranteed regardless of evaluation frequency. A faster loop changes how quickly a check clears, not how often you're told. (Per-occurrence on_state on an on-event check is different by design — there, each distinct occurrence is a real event you asked to hear about.)

That's the complete guide to notifications. From here, the Reference Guide documents every function's exact parameters, and the User Guide covers the rest of the framework.