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.
Often, code works like this:
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:
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
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
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.
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();
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);
pg_notify() function is a more elegant way to send out a message.
To test the function we issue an
INSERT INTO t_message (message) VALUES ('sample text');
The message will be sent:
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.
There is an application of
NOTIFY here: https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/
To learn more about how transactions work in PostgreSQL, see Laurenz Albe’s post on Transaction Anomalies with SELECT for UPDATE, or have a look at his post about WITH HOLD transactions and cursors. Our transaction archive holds all our posts on transactions.