Skip to content

pg_relay_notifier

Send email from SQL, durably.

An INSERT trigger, a stored procedure, an application role — anything that can run SQL can compose and send an email, and have it actually arrive: queued, retried on transient failure, and tracked attempt-by-attempt, all inside PostgreSQL.

pg_relay_notifier stores the message; the companion pg_relay extension delivers it — durable queueing, retry with backoff, concurrency control, and a full audit trail, the same machinery pg_relay already provides for every other kind of dispatched work.

Why not just call an SMTP library from application code?

You can, and for a lot of systems that is the right answer. pg_relay_notifier exists for the case where the email needs to be sent from the same transaction that decided to send it.

Application-code sending means the send happens outside the database transaction. If the transaction that decided "send this email" later rolls back, you have already sent it. If the send fails after the transaction commits, nothing retries it unless your application code remembers to.

A trigger that calls out to SMTP directly blocks the transaction on a network round trip to a mail server you do not control, for as long as that server takes to respond — or hangs.

pg_relay_notifier takes the same path pg_relay already takes for everything else: the SQL call commits the message with your transaction and returns immediately. Delivery happens afterwards, in the background, by a process built to retry, back off, and report failures — not by whatever code happened to be running when someone decided an email was needed.

What you get

  • Two transports — SMTP (with full TLS/auth, HTML/plain/multipart bodies, attachments, CC/BCC/Reply-To) and Microsoft 365 via the Graph API (OAuth2 client-credentials, token caching) — behind the same calls.
  • Durable, at-least-once delivery. The claim, the send, and the status write commit together in one transaction; a crash mid-send is retried, never silently dropped.
  • Retry with backoff on transient failures (SMTP 4xx, connection refused, timeouts, Graph 429/5xx); permanent failures (bad address, rejected credentials) fail immediately without burning retries.
  • Per-attempt status and a debug trace — know exactly what happened to a message, including the provider's own response.
  • Secrets stay out of the database. Passwords and client secrets are _env:VAR_NAME references, resolved by the Processor from its own host environment — never a literal value in a table.
  • Oracle UTL_MAIL compatibility, in two layers: UTL_MAIL-shaped functions built into the core extension, and — if migrated PL/SQL needs to call UTL_MAIL.SEND(...) unmodified — a separate, optional extension providing the literal schema. See the UTL_MAIL Compatibility book.

A first look

SELECT pgrelay_notifier.create_channel('notifications');
SELECT pgrelay_notifier.create_profile('mailer', 'smtp',
    '{"host": "smtp.example.com", "username": "app",
      "password": "_env:SMTP_PASSWORD", "from": "[email protected]"}'::jsonb,
    p_channel := 'notifications');

SELECT pgrelay_notifier.send('mailer', ARRAY['[email protected]'],
    'Your order shipped', 'Order 1234 shipped today.');

That call commits with the current transaction and returns a notification_id. pg_relay's Processor delivers it within about a second, and pgrelay_notifier.get_status(id) tells you exactly what happened.

Where to start

  • New to pg_relay_notifier?

    Start with the User Guide. It assumes you know SQL and nothing else about this extension.

  • Installing it?

    The DBA Guide covers installation, SMTP and Microsoft 365 endpoint setup, channels, grants, and day-to-day maintenance.

  • On a managed cloud database?

    RDS, Aurora, Azure, and Cloud SQL do not allow CREATE EXTENSION for third-party extensions. The Cloud Setup book explains the (straightforward) way around that.

  • Migrating PL/SQL that calls UTL_MAIL?

    The UTL_MAIL Compatibility book covers the separate, optional extension that lets UTL_MAIL.SEND(...) run unmodified.

  • Want the internals?

    The Technical Reference documents the pg_relay interface contract, the schema, security model, and every function.

  • Hit an unfamiliar word?

    The Glossary explains the terms this documentation uses.

Before you install

pg_relay_notifier needs two things in place:

  1. PostgreSQL 15 or later.
  2. pg_relay v1.1 or later, with its Processor running and connected to the same database. pg_relay is the companion extension that provides the queue, the retries, and the SMTP/Microsoft 365 delivery code itself — pg_relay_notifier only stores what to send.

pg_relay has its own documentation at https://pg-relay.pebbleit.com.au/.