What is pg_relay_notifier?

pg_relay_notifier lets your database code send email — reliably, durably, and with a full delivery audit trail — through SMTP or Microsoft 365, without your transaction ever talking to a mail server.

The idea in one sentence: your code calls pgrelay_notifier.send(...) inside a database transaction, the message commits with your transaction, and the pg_relay Processor delivers it outside your transaction, retrying transient failures and recording every attempt.

Key benefits for the sender:

• Transactional. A rolled-back transaction sends nothing; a committed one is guaranteed at-least-once delivery.
• No mail code in your app. Sending is one function call; the Processor handles SMTP or Microsoft Graph.
• Observable. Every attempt is recorded, and a per-notification debug trace can watch a send in real time.
• Oracle-friendly. The send_mail* functions mirror Oracle's UTL_MAIL package, argument for argument, easing migrations — and if migrated code needs to call UTL_MAIL.SEND(...) literally unmodified, a separate compatibility extension provides that too.

---

How it works

your application / trigger
        │  SELECT pgrelay_notifier.send('mailer', ARRAY['a@example.com'], 'Hi', 'body')
        │      → notification row committed with YOUR transaction
        ▼
pgrelay.queue        (durable; the payload is just the notification id)
        ⋮  up to ~1 second
pg_relay Processor   claims the event, fetches the message, delivers via SMTP
                     or Microsoft Graph under a hard timeout, records the outcome
        ▼
pgrelay.log          one audit row per attempt
pgrelay_notifier     status = sent / retry / failed / expired / invalid

Delivery is at-least-once: a crash mid-send re-offers the event, and in the rare window between provider acceptance and commit a duplicate is possible — a notification is never silently lost.

pg_relay_notifier owns the message (recipients, subject, body, attachments, delivery status); the companion pg_relay extension owns delivery (the queue, retries, concurrency, the SMTP/Graph transports themselves). The two meet at exactly four functions, documented in full in the Technical Reference — as a sender, you never call any of them directly.

---