In PostgreSQL, what do LISTEN, NOTIFY and pg_notify() do, and what does a listening client receive with each notification?
answer
- channel, payload, sender
- same database only
- function form for computed values
- identifier folding versus text argument
- send a key, not the row
basics
~20 sLISTEN 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-- 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
Recall the three statements, the pg_notify() function form, and what each notification carries: channel name, payload and the sender's PID.
Explain why triggers call pg_notify(), the 8000-byte payload limit, identifier case folding on channel names, and the same-database boundary.
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.
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.