LISTEN / NOTIFY: Automatic client notification in PostgreSQL

What is LISTEN and NOTIFY?

LISTEN / NOTIFY is a feature that enables users to listen to what goes on in the database. It is one of the oldest functionalities in PostgreSQL and is still widely used. The main question is: What is the purpose of the asynchronous query interface (LISTEN / NOTIFY), and what is it good for? The basic idea is to avoid polling.

Table of Contents

Often, code works like this:

while true
  SELECT * FROM todo_list;
  sleep;
end

1

2

3

4

while true

SELECT * FROM todo_list;

sleep;

end

If thousands of users keep constantly polling the database, a lot of load is caused for no benefit. Most polling requests won’t yield anything - there has to be a better way. LISTEN / NOTIFY is exactly that: A better way that helps us to avoid polling the database over and over again. Instead, we hook up to the database and wait until some relevant event wakes us up.

Set up PostgreSQL notifications

To make use of PostgreSQL notifications two commands are relevant: LISTEN and NOTIFY:

test=# h LISTEN
Command:     LISTEN
Description: listen for a notification
Syntax:
LISTEN channel
URL: https://www.postgresql.org/docs/15/sql-listen.html

1

2

3

4

5

6

test=# h LISTEN

Command: LISTEN

Description: listen for a notification

Syntax:

LISTEN channel

URL: https://www.postgresql.org/docs/15/sql-listen.html

LISTEN will make sure that your database connection listens on a “channel”. Basically a channel is just a name. There is no need to create one - no need to ensure that it actually exists. We listen on this name and wait until a notification drops in.

Send a notification to waiting database connections: use the NOTIFY command:

test=# h NOTIFY 
Command:     NOTIFY
Description: generate a notification
Syntax:
NOTIFY channel [ , payload ]
URL: https://www.postgresql.org/docs/15/sql-notify.html

1

2

3

4

5

6

test=# h NOTIFY

Command: NOTIFY

Description: generate a notification

Syntax:

NOTIFY channel [ , payload ]

URL: https://www.postgresql.org/docs/15/sql-notify.html

All we need in order to send a notification is: a channel and an optional payload (that's a normal string which is transmitted to the receiver).

Let's try it out and see how it works:

test=# LISTEN x;
LISTEN

1 2	test=# LISTEN x; LISTEN

LISTEN makes the backend aware of the fact that we want to know about messages coming via channel “x”. Usually an application listens to one channel. However, nothing stops us from issuing multiple LISTEN commands to listen to more than just one channel at a time. This is perfectly feasible and sometimes even highly desirable.

What happens when a notification is issued?

test=# NOTIFY x, 'some message';
NOTIFY
Asynchronous notification 'x' with payload 'some message' received from server process with PID 62451.

1

2

3

test=# NOTIFY x, 'some message';

NOTIFY

Asynchronous notification 'x' with payload 'some message' received from server process with PID 62451.

The notification will be delivered to all connections that issued a LISTEN command to attach to the same channel.

Use triggers with LISTEN / NOTIFY

Often we want to notify a client when a row is added to a table. To do that, use a trigger installed for a table. Here's an example which shows how this works:

CREATE TABLE t_message (
  id        serial, 
  t         timestamptz DEFAULT now(), 
  message   text
);

CREATE FUNCTION capture_func()
RETURNS trigger AS
$$
DECLARE
  v_txt text;
BEGIN
  v_txt := format('sending message for %s, %s', TG_OP, NEW);
  RAISE NOTICE '%', v_txt;
    EXECUTE FORMAT('NOTIFY mychannel, ''%s''', v_txt);
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER mytrigger BEFORE INSERT OR UPDATE
       ON t_message
       FOR EACH ROW EXECUTE PROCEDURE capture_func();

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

CREATE TABLE t_message (

id serial,

t timestamptz DEFAULT now(),

message text

);

CREATE FUNCTION capture_func()

RETURNS trigger AS

$$

DECLARE

v_txt text;

BEGIN

v_txt := format('sending message for %s, %s', TG_OP, NEW);

RAISE NOTICE '%', v_txt;

EXECUTE FORMAT('NOTIFY mychannel, ''%s''', v_txt);

RETURN NEW;

END;

$$ LANGUAGE 'plpgsql';

CREATE TRIGGER mytrigger BEFORE INSERT OR UPDATE

ON t_message

FOR EACH ROW EXECUTE PROCEDURE capture_func();

Another way: pg_notify()

The code shows how to create a table and how to create the trigger to send out notifications. In my example, I used EXECUTE to run dynamic SQL. However, there is also a second method:

SELECT pg_notify('mychannel', v_txt);

1	SELECT pg_notify('mychannel', v_txt);

The pg_notify() function is a more elegant way to send out a message.

To test the function we issue an INSERT statement:

[sql gutter="false"]
INSERT INTO t_message (message) VALUES ('sample text');

The message will be sent:

[sql gutter="false"]
NOTICE: sending message for INSERT, (1,'2022-07-13 16:18:24.709008+02','sample text')

The notification is NOT sent out immediately but only after the transaction is actually committed. Why is that an important detail? If the transaction was sent out immediately, the changes would not be visible to other transactions. Note that changes are only seen by your own transaction - unless you commit. The second transaction receives the message as soon as the first transaction ends successfully. Keep in mind: Notifications are also a transactional thing - they are only delivered on commit and not in case of a rollback.