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:
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.
To make use of PostgreSQL notifications two commands are relevant: LISTEN
and NOTIFY
:
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:
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:
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.
1 2 3 4 |
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.
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:
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(); |
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:
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.
There is an application of LISTEN
/NOTIFY
here: /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.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply