skip to content

In PostgreSQL, when is a NOTIFY issued inside a transaction delivered to listeners, and what happens to it if that transaction rolls back?

level: middleimportance: must knowfreq 58%

answer

  1. transactional, not immediate
  2. queued just before the commit record
  3. busy listener waits for its own transaction
  4. commit order across transactions
  5. duplicates fold inside one transaction

basics

~20 s

A NOTIFY is held until its transaction commits and only then delivered; if the transaction rolls back, nothing is ever sent. A listener that is inside its own transaction receives the notification only after that transaction ends.

solid answer

~40 s

`NOTIFY` and `pg_notify()` are transactional. The call only appends the event to a backend-local list; at commit the backend writes the list into the shared queue and, once committed, signals the listeners. On `ROLLBACK`, or a rollback to a savepoint, the pending notifications are discarded and nobody hears anything. On the receiving side, a listening session gets notifications only **between** transactions, so a listener busy in a long transaction sees them after it commits or rolls back. Order is guaranteed: notifications from one transaction arrive in the order sent, and different transactions' notifications arrive in **commit order**. Within one transaction, repeats of the same channel with an identical payload are folded into one; distinct payloads are always delivered separately, and two transactions are never folded together. `LISTEN` itself also takes effect only at commit.

code

sql · 11 lines
sql
BEGIN;
UPDATE price SET amount = 19.99 WHERE sku = 'SKU-1042';
NOTIFY price_changed, 'SKU-1042';
NOTIFY price_changed, 'SKU-1042';  -- identical: folded into the first
NOTIFY price_changed, 'SKU-2077';
COMMIT;  -- listeners now receive two notifications, in this order

BEGIN;
UPDATE price SET amount = 0 WHERE sku = 'SKU-1042';
NOTIFY price_changed, 'SKU-1042';
ROLLBACK;  -- nothing is delivered

go deeper

for a junior

Remember that NOTIFY is transactional: listeners hear it after COMMIT and never after ROLLBACK.

for a middle

Walk through the commit sequence, why listeners receive only between transactions, commit ordering, and exactly when identical notifications fold.

for a senior

Apply it in design: keep listener connections idle, expect long batch writers to signal late, and pick payloads deliberately to fold or keep bursts.

for a principal

Argue why transactional signalling suits cache invalidation and name its price: no early warnings, and signal latency bound to the writer's transaction length.

## Notifications belong to the transaction In PostgreSQL, `NOTIFY` and `pg_notify()` are **transactional**. Calling them sends nothing: the notification is appended to a list private to the calling backend, and that list is acted on only when the transaction ends. - If the transaction **commits**, listeners receive the notifications. - If it **rolls back**, the list is thrown away and nothing is ever sent. - If a **savepoint** is rolled back, the notifications issued inside that subtransaction are discarded with it, while earlier ones survive. This is deliberate. A notification usually means *a row changed*; if the change was rolled back, the signal would be false, and the server cannot take back a message once a client has seen it. ## The commit sequence 1. `NOTIFY` appends the event to the backend-local pending list, folding duplicates as it goes. 2. At pre-commit, before the commit record is written, the backend takes a server-wide lock that serializes notifying transactions and copies its pending events into the shared notification queue. A failure here, such as a full queue, still rolls the transaction back. 3. The commit is recorded. 4. After commit, the backend signals the listening backends. 5. Each listening backend reads the new queue entries and forwards matching ones to its client, but only when it is idle between transactions. A listening backend stops reading at any queue entry whose transaction is not yet visible to it as committed. So by the time the client sees `price_changed` for `SKU-1042`, a fresh query on the `price` table returns the new price, not the old one. That is what makes *notify, then reread* safe for cache invalidation. ## The listening side waits too A listening session inside its own transaction does not receive notifications until that transaction ends, by commit or by rollback. Notifications are delivered only **between** transactions, because a notification handed to a client inside a transaction that later aborted could not be undone. The manual's practical rule is to keep transactions short in applications that rely on notifications. A listener connection should normally do nothing but wait, idle and outside any transaction block. `LISTEN` is transactional as well: it takes effect when its transaction commits, and a `LISTEN` in a transaction that rolls back leaves the session's channels unchanged. ## Ordering and de-duplication | Situation | What listeners receive | |---|---| | Same channel, identical payload, several times in one transaction | one notification | | Same channel, different payloads in one transaction | one per distinct payload, in the order sent | | Identical notifications from two different transactions | two notifications, never folded | | Notifications from two committed transactions | in the order the transactions **committed**, not the order `NOTIFY` ran | The folding saves work: a row trigger on a large update would otherwise send one identical event per row. If every event matters, make each payload distinct, for example the SKU; if only the fact of a change matters, a constant payload collapses the whole burst into one signal per transaction. ## Edge cases interviewers like - A procedure that runs `COMMIT` part-way through sends its notifications to other sessions right after each commit, but its own client receives notifications only when the whole `CALL` finishes. - A transaction that has executed `LISTEN`, `UNLISTEN` or `NOTIFY` cannot be prepared for two-phase commit: `PREPARE TRANSACTION` fails with `cannot PREPARE a transaction that has executed LISTEN, UNLISTEN, or NOTIFY`. - A long writer transaction delays its own signals. A pricing batch that runs for ten minutes in one transaction invalidates caches only when it commits, all at once. - A session that is listening and notifying on the same channel receives its own notifications too, after its transaction commits. - Notifications waiting for a busy listener are not lost; they stay in the shared queue until that listener's transaction ends. That is also how a listener stuck in a transaction can eventually fill the queue, a separate failure with its own symptoms. ## Why this question is asked Candidates who expect immediate, broker-style delivery design caches that wait for signals that arrive late, or they put `NOTIFY` before a statement that may still fail and expect the signal to go out regardless. The correct model fits in one line: **a notification is a post-commit side effect of its transaction**, delivered to whoever is listening and idle, in commit order, with identical repeats inside one transaction folded into one.

  • In PostgreSQL, what happens to a LISTEN issued inside a transaction that then rolls back?
    Nothing changes: `LISTEN` and `UNLISTEN` take effect only at commit, so the session's set of channels stays as it was. This is also why a listener should run `LISTEN` and commit it before reading the state it caches; registration starts during that commit.
  • Can a PostgreSQL transaction that ran NOTIFY be prepared with PREPARE TRANSACTION?
    No. `PREPARE TRANSACTION` fails with `cannot PREPARE a transaction that has executed LISTEN, UNLISTEN, or NOTIFY`, SQLSTATE `0A000` (feature_not_supported). Notification delivery is tied to the session's own commit, which two-phase commit would separate from the session.
  • In PostgreSQL, why does a listener that rereads the price table on receiving a notification see the new price?
    Delivery starts only after the sender commits, and a listening backend stops at any queue entry whose transaction is not yet visible to it as committed. By the time the client sees the notification, a new query sees the committed row.

  • NOTIFY is sent the moment the statement runs, before COMMIT.
  • A rolled-back transaction's NOTIFY still reaches the listeners.
  • Ten identical NOTIFYs in one transaction arrive as ten notifications.
  • Notifications from different transactions arrive in the order NOTIFY was called.
  • Distinct payloads on one channel merge into one notification per transaction.