NOTIFY

A command for sending notifications to other sessions

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 channel , or alternatively (from PostgreSQL 9.0) NOTIFY channel, 'message', a notification will be passed to all sessions listening on that channel (using the 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

Change history

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)

Time: 0.377 ms
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
  • PostgreSQL documentation: NOTIFY

Categories

Asynchronous notifications, Utility command

See also

pg_notify(), LISTEN, UNLISTEN, pg_notification_queue_usage()