LISTEN/NOTIFY: transactional wake-up calls

PostgreSQL 18's NOTIFY contract defers delivery until the notifying transaction commits. That signal does not prove the payload truthfully describes data or that the data remains unchanged until the listener reads it.

A listener you can see

The scenario uses a psql subprocess in the Docker Compose PostgreSQL container. It waits for LISTEN to complete before sending notifications. The periodic SELECT makes this psql listener print pending notifications; it is not a limitation of every PostgreSQL notification client.

/**
 * This example uses a psql subprocess as its visible notification listener.
 * Its output exposes the channel, payload, and sending backend.
 * ponytail: assumes the docker-compose stack from this repo (run from its root).
 */
class Listener {
  private proc = Bun.spawn(
    ["docker", "compose", "exec", "-T", "postgres", "psql", "-U", "postgres", "-X", "-q", "-A", "-t"],
    { stdin: "pipe", stdout: "pipe" },
  );
  private out = "";

  static async start(channel: string): Promise<Listener> {
    const l = new Listener();
    l.drain();
    l.proc.stdin.write(`LISTEN ${channel};\nSELECT 'listening';\n`);
    const deadline = Date.now() + 15_000;
    while (!l.out.includes("listening")) {
      if (Date.now() > deadline) throw new Error("psql listener failed to start");
      await Bun.sleep(25);
    }
    return l;
  }

  private async drain() {
    for await (const chunk of this.proc.stdout) this.out += new TextDecoder().decode(chunk);
  }

  /** psql only checks for notifications around command execution — poke it, then look. */
  private poke() {
    this.proc.stdin.write("SELECT 1;\n");
  }

  /** Wait for the next notification psql prints. */
  async next(): Promise<{ channel: string; payload: string; pid: number }> {
    const deadline = Date.now() + 10_000;
    while (Date.now() < deadline) {
      const m = this.out.match(
        /Asynchronous notification "(\w+)" with payload "([^"]*)" received from server process with PID (\d+)\./,
      );
      if (m) {
        this.out = this.out.slice(m.index! + m[0].length);
        return { channel: m[1]!, payload: m[2]!, pid: Number(m[3]) };
      }
      this.poke();
      await Bun.sleep(50);
    }
    throw new Error("no notification arrived within 10s");
  }

  /** Assert that nothing has been delivered — poke for a while and check. */
  async nothingYet(ms = 700): Promise<void> {
    const deadline = Date.now() + ms;
    while (Date.now() < deadline) {
      this.poke();
      await Bun.sleep(50);
    }
    if (this.out.includes("Asynchronous notification")) {
      throw new Error(`expected silence, got: ${this.out}`);
    }
  }

  stop() {
    this.proc.kill();
  }
}

Three claims, one run

const listener = await Listener.start("orders");
t.note("A psql process is attached and ran LISTEN orders; — a separate client, not one of our sessions.");

const [me] = await A`SELECT pg_backend_pid() AS pid`;

await A`BEGIN`;
await A`INSERT INTO orders VALUES (1, 'alice')`;
await A`NOTIFY orders, 'order 1 placed'`;
await listener.nothingYet();
t.note("The listener hears nothing — the notification is queued inside A's transaction.");

await A`COMMIT`;
const n = await listener.next();
eq(n.channel, "orders");
eq(n.payload, "order 1 placed");
eq(n.pid, Number(me!.pid)); // sent by A's backend
t.note(
  `The listener wakes up: Asynchronous notification "orders" with payload "order 1 placed" received from server process with PID pid(A).`,
);

t.note("A rolled-back NOTIFY never happened at all:");
await A`BEGIN`;
await A`NOTIFY orders, 'order 2 placed'`;
await A`ROLLBACK`;
await listener.nothingYet();
t.note("Silence — order 2's notification died with its transaction.");

t.note("Identical notifications in one transaction are de-duplicated:");
await A`BEGIN`;
await A`NOTIFY orders, 'order 3 placed'`;
await A`NOTIFY orders, 'order 3 placed'`;
await A`COMMIT`;
const once = await listener.next();
eq(once.payload, "order 3 placed");
await listener.nothingYet();
t.note("Two NOTIFYs went in, one notification came out.");

listener.stop();

A psql process is attached and ran LISTEN orders; — a separate client, not one of our sessions.

A> SELECT pg_backend_pid() AS pid;
  pid   
--------
 pid(A) 
(1 row)

A> BEGIN;
BEGIN

A> INSERT INTO orders VALUES (1, 'alice');
INSERT 0 1

A> NOTIFY orders, 'order 1 placed';
NOTIFY

The listener hears nothing — the notification is queued inside A's transaction.

A> COMMIT;
COMMIT

The listener wakes up: Asynchronous notification "orders" with payload "order 1 placed" received from server process with PID pid(A).

A rolled-back NOTIFY never happened at all:

A> BEGIN;
BEGIN

A> NOTIFY orders, 'order 2 placed';
NOTIFY

A> ROLLBACK;
ROLLBACK

Silence — order 2's notification died with its transaction.

Identical notifications in one transaction are de-duplicated:

A> BEGIN;
BEGIN

A> NOTIFY orders, 'order 3 placed';
NOTIFY

A> NOTIFY orders, 'order 3 placed';
NOTIFY

A> COMMIT;
COMMIT

Two NOTIFYs went in, one notification came out.

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The listener is silent during the configured observation before commit, then reports the asserted channel, payload and sender. It is silent in the observation window after rollback. Two identical notifications in one transaction produce one observed payload and no second notification in the window. These are Demonstrated behaviors; the manual supplies the general commit, rollback and same-transaction folding contracts.

NOTIFY is not a durable queue for disconnected listeners. The LISTEN contract says LISTEN takes effect at commit and describes the startup race: commit LISTEN, inspect database state in a new transaction, then use notifications for later changes. Listener transactions can also delay delivery. Keep transactions short and retain an outbox read/polling recovery path. No latency improvement is measured by this example.

Both the LISTEN and NOTIFY manuals prohibit preparing a transaction that performed the respective command for two-phase commit. That is a Documented contract, not exercised by this listener scenario.

The outbox retains event intent; a notification can wake a connected relay to inspect it. No relay delivery, disconnect replay, or consumer deduplication is tested here.

Further reading

• PostgreSQL 18: NOTIFY
• PostgreSQL 18: LISTEN
• MySQL outbox polling

The reconstructed support assessment records the exact manual support and execution limits for this lesson.