LISTEN
LISTEN is a utility command which registers the current session as a listener on the named channel.
LISTEN has always been present in PostgreSQL.
Usage
The named channel must be a valid SQL identifier.
Once LISTEN is executed, any pending notifications from another session will be received the next time a command is executed.
The function pg_listening_channels() returns a list of all channels on which the session is currently listening.
Execute UNLISTEN to stop listening on the named channel.channelname
LISTEN and transactions
LISTEN operates in the context of a transaction and will only become effective once the transaction is committed. If the transaction is rolled back, the command will have no effect.
Change history
- PostgreSQL 9.0
- conversion of the interprocess messaging system to a memory-based queue (commit d1e02722)
Examples
Basic usage example for LISTEN:
postgres=# LISTEN foo; LISTEN
If NOTIFY is executed from another session, the notification will be received after the next command is processed:
postgres=# CHECKPOINT; CHECKPOINT Asynchronous notification "foo" received from server process with PID 14161.
From PostgreSQL 9.0, the notification can contain an optional payload:
postgres=# SELECT 1;
?column?
----------
1
(1 row)
Asynchronous notification "foo" with payload "bar" received from server process with PID 14161.
Multiple messages may be received:
postgres=# CHECKPOINT ; CHECKPOINT Asynchronous notification "foo" with payload "bar" received from server process with PID 14161. Asynchronous notification "bar" with payload "foo" received from server process with PID 14161.
Usage of LISTEN with transactions:
postgres=# BEGIN; BEGIN postgres=*# LISTEN foo; LISTEN postgres=*# SELECT * FROM pg_listening_channels(); pg_listening_channels ----------------------- (0 rows) postgres=*# commit; COMMIT postgres=# SELECT * FROM pg_listening_channels(); pg_listening_channels ----------------------- foo (1 row)
If the transaction is rolled back, the session will not listen on the named channel (unless it was already registered before the transaction started):
postgres=# SELECT * FROM pg_listening_channels(); pg_listening_channels ----------------------- foo (1 row) postgres=# BEGIN; BEGIN postgres=*# LISTEN bar; LISTEN postgres=*# LISTEN foo; LISTEN postgres=*# ROLLBACK ; ROLLBACK postgres=# SELECT * FROM pg_listening_channels(); pg_listening_channels ----------------------- foo (1 row)
The channel name must be a valid SQL identifier of maximum 63 (NAMEDATALEN) characters (excess characters will be truncated):
postgres=# LISTEN 123; ERROR: syntax error at or near "123" LINE 1: LISTEN 123; postgres=# LISTEN ABCD012345678901234567890123456789012345678901234567890123456789; NOTICE: identifier "abcd012345678901234567890123456789012345678901234567890123456789" will be truncated to "abcd01234567890123456789012345678901234567890123456789012345678" LISTEN
References
- PostgreSQL documentation: LISTEN