Skip to content
Tutorials Center
My learning

Topics

Lesson 53 of 90 ยท Functions, PL/pgSQL and Triggers

Event Triggers, LISTEN and NOTIFY

Use PostgreSQL event triggers to react to DDL, and LISTEN/NOTIFY with pg_notify for cache invalidation and queues, including limits and the outbox pattern.

Ordinary triggers react to changes in table data. Two other PostgreSQL features cover different situations. Event triggers react to changes in the database's structure: someone runs CREATE TABLE, ALTER TABLE or DROP. LISTEN and NOTIFY let one session send a message to other sessions that are waiting for it, which is a lightweight way to tell an application "something changed, come and look."

Both are small features with big uses (schema auditing and guardrails, cache invalidation, waking up workers) and both come with limits that are easy to overlook. This lesson shows how they work, where they fall short, and the outbox pattern that turns notifications into something reliable.

What runs live here. The in-browser editor uses SQLite, which has neither event triggers nor LISTEN and NOTIFY, so all PostgreSQL code in this lesson is read-only. The live examples build the outbox and the queue-consumer idea with ordinary tables and a SQLite trigger. That part is portable, and it is the pattern you would rely on in production.

Event triggers: reacting to DDL

An event trigger fires on commands such as CREATE, ALTER and DROP rather than on row changes. It is created by a superuser, applies to the whole database, and calls a function that returns the special type event_trigger:

SQL
CREATE TABLE ddl_log (
  id         bigserial PRIMARY KEY,
  happened   timestamptz NOT NULL DEFAULT now(),
  who        text NOT NULL DEFAULT current_user,
  command    text NOT NULL,
  object     text
);

CREATE FUNCTION log_ddl()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    INSERT INTO ddl_log (command, object)
    VALUES (r.command_tag, r.object_identity);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER audit_ddl
ON ddl_command_end
EXECUTE FUNCTION log_ddl();

There are four events:

Event Fires Typical use
ddl_command_start before a DDL command runs block a command, enforce naming rules
ddl_command_end after a DDL command finished audit, post-processing
sql_drop after objects have been dropped log what disappeared
table_rewrite before ALTER TABLE rewrites a table warn or block expensive changes

Inside ddl_command_end the helper pg_event_trigger_ddl_commands() lists each command with its tag (CREATE TABLE) and the affected object. In sql_drop, use pg_event_trigger_dropped_objects(). Inside any event trigger the variable TG_TAG holds the command tag (such as 'DROP TABLE'), and you can restrict a trigger to certain commands:

SQL
CREATE EVENT TRIGGER forbid_drop_table
ON ddl_command_start
WHEN TAG IN ('DROP TABLE', 'DROP SCHEMA')
EXECUTE FUNCTION refuse_drops();

If the function raises an exception, the command is cancelled. That makes event triggers useful as guardrails on a shared database, for instance forbidding drops on production or requiring every new table to follow a naming rule. A broken event trigger function can also block all DDL, including the command that would fix it. If that happens, start the server in single-user mode, or disable event triggers for a session with the setting event_triggers = off (superuser only). Keep these functions tiny and test them on a copy first.

Note that event triggers do not fire for changes to shared objects such as roles, databases or tablespaces, nor for event triggers themselves.

LISTEN and NOTIFY

NOTIFY sends a message on a named channel. Every session that has run LISTEN on that channel receives it:

SQL
-- Session A
LISTEN order_events;

-- Session B
NOTIFY order_events, 'order 4821 paid';

-- Session A then receives (in psql):
-- Asynchronous notification "order_events" with payload "order 4821 paid"
--   received from server process with PID 8123.

The payload is an optional string, up to just under 8000 bytes. Inside a function, use pg_notify(channel, payload), which also accepts a computed channel name:

SQL
SELECT pg_notify('order_events', json_build_object('id', 4821, 'status', 'paid')::text);

Three properties define how notifications behave, and all three matter when you design with them:

  • They are transactional. A notification is delivered only when the sending transaction commits. A rollback sends nothing. That is usually what you want: nobody is told about a change that never happened.
  • They are deduplicated within a transaction. If one transaction sends the identical channel and payload several times, listeners receive it once.
  • They are not stored. A notification reaches only sessions that are listening at that moment. If a listener is disconnected, restarting, or slow, the message is gone.

Because of the last point, NOTIFY is best thought of as a doorbell, not a mailbox. It says "ring ring, new data", and the listener goes and reads the actual data from a table.

A typical use: cache invalidation

An application keeps a configuration table cached in memory. A trigger announces every change, and each app instance listening on the channel reloads its cache:

SQL
CREATE FUNCTION announce_settings_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pg_notify('settings_changed', COALESCE(NEW.key, OLD.key));
  RETURN NULL;
END;
$$;

CREATE TRIGGER settings_notify
AFTER INSERT OR UPDATE OR DELETE ON settings
FOR EACH ROW EXECUTE FUNCTION announce_settings_change();

The payload carries just the key, so the listener knows what to refresh, and a lost message is not catastrophic: the application can also reload everything on a timer, or whenever it reconnects. That is the safe way to use NOTIFY: for hints, not for facts that must never be lost.

Listening from an application

Drivers expose notifications through their own API. In psycopg (Python) you iterate over conn.notifies(); in node-postgres you register client.on('notification', ...); in JDBC you poll PGConnection.getNotifications(). A listening connection must stay open and must not go through a transaction-mode connection pooler: LISTEN is bound to one session, and a pooler that hands the connection to a different client loses it. Give listeners a direct connection, or a pooler in session mode. See Connection Pooling for the details.

Limits to know before you rely on it

  • Messages can be lost (no listener, disconnect, crash). They are never replayed.
  • No consumer groups. If three workers all LISTEN, all three are woken for every message. NOTIFY does not distribute work; a queue table with FOR UPDATE SKIP LOCKED does.
  • The notification queue is bounded (8 GB by default). A listener that stays in a long transaction without reading its notifications can make the queue fill up, and then senders fail at commit time.
  • A busy channel serializes commits. Commits that send notifications take a global lock briefly, so a very high rate of notifying transactions can limit throughput.
  • Payload size is limited to under 8000 bytes. Send identifiers, not documents.

LISTEN and NOTIFY in four lines

LISTEN A session subscribes to a channel.
NOTIFY Any session sends a short payload.
On commit Delivery happens only after COMMIT.
Doorbell Nothing is stored for absent listeners.

Read the real data from a table after the ping.

1 / 2

The outbox pattern: making events reliable

Suppose an order is paid and the application must tell a payment service, send an email and update a search index. If the application writes to the database and then calls those services, a crash in between loses the events or sends them for rolled-back data. The outbox pattern fixes this:

  1. In the same transaction as the business change, insert a row into an outbox table describing the event.
  2. A worker reads unprocessed outbox rows, performs the side effect, and marks the row as done.
  3. NOTIFY is optional: it wakes the worker immediately, while a periodic poll catches anything missed.

The write and the event commit or roll back together, and the outbox table is durable, so nothing is lost. Run this live to see the shape, with a trigger that creates the outbox row (a SQLite trigger here, a PL/pgSQL one in PostgreSQL):

SQL An outbox filled by a trigger

Press Run (or Ctrl + Enter) to see the result here.

Two outbox rows exist, one for each order that became paid, and the unpaid order left no event. In PostgreSQL the trigger function would also call pg_notify('outbox', '') so a worker wakes up at once.

A worker claiming events

The worker takes pending rows and marks them done in one atomic statement. SQLite supports UPDATE ... RETURNING, so here is the claim-and-process step, live:

SQL Claiming outbox rows with RETURNING

Press Run (or Ctrl + Enter) to see the result here.

The first statement hands back all three events once and stamps them, so a second run finds still_pending = 0. With several workers on PostgreSQL, the claim would use FOR UPDATE SKIP LOCKED so each event goes to exactly one worker; that technique is the subject of the locking lesson and the job-queue project. Marking the row only after the side effect succeeds gives at-least-once delivery, so consumers must tolerate seeing an event twice (be idempotent).

Choosing between the tools

Need Use
Record or block schema changes Event trigger
Tell other processes "something changed" and losing a ping is fine NOTIFY, with a timed refresh
Events that must not be lost Outbox table (plus optional NOTIFY)
Distribute jobs to many workers Queue table with SKIP LOCKED
Stream every change to other systems Logical replication / change data capture

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When does a NOTIFY sent inside a transaction reach listeners?

    Notifications are transactional. They are delivered at commit time, and a rollback discards them, so listeners are never told about changes that did not happen.
  2. 2. What happens to a notification if no session is listening when it is sent?

    NOTIFY does not store messages for absent listeners. That is why the payload should be a hint and the real data should live in a table.
  3. 3. What problem does the outbox pattern solve?

    The outbox row commits or rolls back together with the business change. A worker then delivers it later, so a crash between "write" and "notify other systems" cannot lose it.
  4. 4. Which statement about event triggers is true?

    Event triggers react to DDL events such as ddl_command_end and sql_drop for the whole database. Creating them requires superuser rights, and they are normally written in PL/pgSQL.

Summary

  • Event triggers react to DDL (ddl_command_start, ddl_command_end, sql_drop, table_rewrite); use them for auditing and guardrails, and keep them tiny because a bug can block all DDL.
  • LISTEN subscribes, NOTIFY/pg_notify sends a payload under 8000 bytes, and delivery happens at commit.
  • Notifications are not stored, not load-balanced and can be lost, so treat them as a doorbell and read data from a table.
  • Do not LISTEN through a transaction-mode pooler.
  • The outbox pattern writes the event with the change and lets a worker deliver it, using NOTIFY only to wake it quickly.
  • The live examples ran the outbox and claim steps in SQLite; the event triggers and LISTEN/NOTIFY were read-only.

Next: Other Procedural Languages and Aggregates, where you will see PL/Python, custom aggregates and the question of how much logic belongs in the database.