NOTIFY
NOTIFY is a utility command for sending notifications to other sessions connected to the same database listening on a channel specified with the LISTEN command.
NOTIFY has always been present in PostgreSQL.
Usage
By executing NOTIFY , or alternatively (from PostgreSQL 9.0) channelNOTIFY , a notification will be passed to all sessions listening on that channel (using the channel, 'message'LISTEN command). The notification will be received by the listening sessions the next time they execute a query. The notification can optionally contain a message.
NOTIFY and transactions
NOTIFY operates in the context of a transaction. Notifications will only be transmitted once the transaction is committed, and will not be sent if the transaction is rolled back.
pg_notify()
The function pg_notify() provides the same functionality as NOTIFY, and makes it possible to execute the notification with values derived from a table or an expression
Statistics
From PostgreSQL 13, the Notify record returned by pg_stat_slru contains statistics about NOTIFY usage.
Change history
- PostgreSQL 9.0
- (commit d1e02722)
Examples
Basic NOTIFY usage example:
postgres=# NOTIFY foo; NOTIFY
This sends a notification to all sessions listening on channel foo, received when they execute a query:
postgres=# LISTEN foo; LISTEN postgres=# SELECT pg_backend_pid(); pg_backend_pid ---------------- 23711 (1 row) Asynchronous notification "foo" received from server process with PID 20679.
A session will receive its own notifications if it is listening on that channel:
postgres=# LISTEN foo; LISTEN postgres=# NOTIFY foo; NOTIFY Asynchronous notification "foo" received from server process with PID 20679. postgres=# NOTIFY foo;
To simplify subsequent examples, they will be shown with the session listening on its own channel.
An optional message of up to 7999 bytes can be passed with the notification:
postgres=# NOTIFY foo, 'hello world'; NOTIFY Asynchronous notification "foo" with payload "hello world" received from server process with PID 20679.
The message must be specified as a string (and cannot be NULL), but if the string is empty, NOTIFY behaves if no message was supplied:
postgres=# NOTIFY foo, NULL;
ERROR: syntax error at or near "NULL"
LINE 1: NOTIFY foo, NULL;
^
postgres=# NOTIFY foo, '';
NOTIFY
Asynchronous notification "foo" received from server process with PID 20679.
If the string exceeds the maximum length (by default 7999 bytes), an error is raised:
postgres=# NOTIFY 'foo', '... very long message ...';
ERROR: payload string too long
Message strings must be provided as literals; it is not possible to generate them from an expression (e.g. by concatenation or as the result of a function):
postgres=# NOTIFY foo, 'foo' || 'bar';
ERROR: syntax error at or near "||"
LINE 1: NOTIFY foo, 'foo' || 'bar';
^
postgres=# NOTIFY foo, REPEAT('bar', 2);
ERROR: syntax error at or near "REPEAT"
LINE 1: NOTIFY foo, REPEAT('foo', 2);
^
This can however be achieved with pg_notify().
Notifications will only be sent once a transaction is committed:
postgres=# BEGIN; BEGIN postgres=*# NOTIFY foo, 'hello world'; NOTIFY postgres=*# COMMIT; COMMIT Asynchronous notification "foo" with payload "hello world" received from server process with PID 20679.
Notifications will not be sent if the transaction is rolled back:
postgres=# BEGIN; BEGIN postgres=*# NOTIFY foo, 'hello world'; NOTIFY postgres=*# ROLLBACK ; ROLLBACK
References
- PostgreSQL documentation: NOTIFY