skip to content

In PostgreSQL, what do LISTEN, NOTIFY and pg_notify() do, and what does a listening client receive with each notification?

level: juniorimportance: should knowfreq 46%

answer

  1. channel, payload, sender
  2. same database only
  3. function form for computed values
  4. identifier folding versus text argument
  5. send a key, not the row

basics

~20 s

LISTEN registers a session on a named channel; NOTIFY or pg_notify(channel, payload) signals every session listening on it in the same database. Each notification carries the channel name, a payload under 8000 bytes and the sender's backend PID.

solid answer

~50 s

`LISTEN price_changed` registers the current session on a channel; `UNLISTEN` or the end of the session removes it. `NOTIFY price_changed, 'SKU-1042'` signals every session listening on that channel **in the same database**, and `pg_notify(text, text)` does the same as a function, so channel and payload can be computed, which is why triggers use it. Each delivered notification carries three things: the channel name, the payload string (empty if omitted, shorter than 8000 bytes in a default build) and the PID of the sending server process. Clients read them through the driver, such as libpq's `PQnotifies()`; psql prints them after each command. Keep the payload small: send the changed key and let the listener reread the row. A session notifying a channel it also listens on gets its own notification back, and can skip it by comparing PIDs.

code

sql · 10 lines
sql
-- session A: an application server's listener connection
LISTEN price_changed;

-- session B: the pricing job
NOTIFY price_changed, 'SKU-1042';
SELECT pg_notify('price_changed', 'SKU-' || 2077);

-- session A, afterwards
SELECT pg_listening_channels();
UNLISTEN price_changed;

go deeper

for a junior

Recall the three statements, the pg_notify() function form, and what each notification carries: channel name, payload and the sender's PID.

for a middle

Explain why triggers call pg_notify(), the 8000-byte payload limit, identifier case folding on channel names, and the same-database boundary.

for a senior

Show the design habits: payload as a key with the table as truth, a dedicated listener connection, no secrets in payloads, listeners on the primary.

for a principal

Frame LISTEN/NOTIFY as a cheap built-in wake-up signal and be explicit about what it is not: a durable or access-controlled message channel.

## The three commands and the function PostgreSQL ships a small built-in signalling mechanism made of three statements and one function: - **`LISTEN channel`** registers the current session as a listener on a named channel. Running it twice is harmless; the second call does nothing. - **`NOTIFY channel [, 'payload']`** sends a notification on that channel to every session currently listening on it **in the same database**. - **`UNLISTEN channel`** or `UNLISTEN *` removes registrations; when a session ends, `UNLISTEN *` runs automatically. - **`pg_notify(channel text, payload text)`** is the function form of `NOTIFY`. It has the same delivery rules but takes ordinary expressions. The `NOTIFY` statement accepts only an identifier for the channel and a simple string literal for the payload, so triggers and dynamic code call `pg_notify()` instead. A **channel** is just a name. Nothing ties it to a table; it is a convention the application chooses, such as `price_changed` for changes to a pricing table. `pg_listening_channels()` returns the channels the current session is registered on. There is no SQL standard behind any of this: `NOTIFY` is a PostgreSQL extension. ## What a notification carries | Field | Content | Limit or use | |---|---|---| | Channel name | for example `price_changed` | at most 63 bytes, the identifier length limit | | Payload | any string, empty if omitted | shorter than 8000 bytes in a default build | | Sender PID | the notifying server process's PID | lets a session recognise its own notifications | That is all. There is no transaction ID, timestamp, role name or table OID in the message. The 8000-byte limit follows from the 8 kB block size; a longer payload raises `payload string too long`. The manual's own advice is to store anything large, or binary, in a table and send its key. ## How a client receives one Notifications are pushed over the session's ordinary connection as asynchronous protocol messages. How the application sees them depends on the client library: 1. In **libpq**, the program calls `PQconsumeInput()` to read pending input and then `PQnotifies()` to take each notification; the returned struct holds `relname` (the channel), `be_pid` (the sender) and `extra` (the payload). 2. In **psql**, notifications are printed after each command, for example: Asynchronous notification "price_changed" with payload "SKU-1042" received from server process with PID 8448. 3. Other drivers wrap the same protocol messages in a callback or a polling call. The listening connection has to stay open. A listener is a live session, not a subscription stored in the database, so a dedicated connection per application server is the usual shape. ## Channel names: identifier versus text The channel in `LISTEN` and `NOTIFY` is an **SQL identifier**, so an unquoted name is folded to lower case: `LISTEN PriceChanged` registers `pricechanged`. The first argument of `pg_notify()` is plain **text** and is used exactly as given. As a result `LISTEN PriceChanged` never receives `pg_notify('PriceChanged', 'x')`. Use lower-case channel names everywhere, or double-quote them consistently in the statements. ## Boundaries worth knowing - **Same database only.** A notification sent in database `shop` never reaches a listener connected to database `reporting`, even on the same server. - **No access control.** Notifications are visible to all users of the database; any session may `LISTEN` on any channel, so a payload must never carry a secret. - **Primary only.** On a hot standby, `LISTEN` fails with `cannot execute LISTEN during recovery`, and `NOTIFY` likewise. Notifications are never written to WAL, so nothing reaches a standby. - **Self-notification.** A session that notifies a channel it also listens on receives its own notification; comparing the sender PID with the session's own backend PID lets it skip that echo without missing anyone else's. ## The typical shape for cache invalidation Each application server opens one dedicated connection and runs `LISTEN price_changed`. A trigger on the `price` table calls `pg_notify('price_changed', NEW.sku)`. On receipt, the server drops or reloads that SKU's cache entry by reading `price` again. The notification says *something changed*; the table says *what it is now*. That split keeps payloads tiny and means a listener never acts on data the database did not commit. What interviewers ask next are the mechanisms behind that loop: when exactly a notification is delivered, what happens to notifications nobody was listening for, and what a full notification queue does to writers.

  • Why does LISTEN PriceChanged never receive pg_notify('PriceChanged', 'x') in PostgreSQL?
    The channel in `LISTEN` is an SQL identifier, so unquoted it is folded to lower case and the session listens on `pricechanged`. `pg_notify()` takes its channel as text and uses it verbatim, so it signals `PriceChanged`, a different channel. Use lower-case names, or write `LISTEN "PriceChanged"` with double quotes.
  • Can an application server LISTEN on a PostgreSQL hot standby to keep load off the primary?
    No. `LISTEN` and `NOTIFY` are refused during recovery with `cannot execute LISTEN during recovery` (or `NOTIFY`), and notifications are never written to WAL, so a standby has nothing to deliver anyway. Listeners connect to the primary and, after a failover, reconnect to the new primary and resynchronise.

  • NOTIFY reaches listeners connected to any database on the same server.
  • The payload can carry a whole JSON row of any size.
  • A session needs a GRANT on a channel before it may LISTEN to it.
  • pg_notify() and the NOTIFY statement have different delivery rules.
  • Unquoted channel names in LISTEN keep their case exactly as typed.