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) channel
NOTIFY
, 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