PostgreSQL's Built-in Answer to Polling
Applications constantly checking the database for changes generate load without any guarantee of useful results. LISTEN / NOTIFY, one of PostgreSQL's oldest features, addresses this by letting clients subscribe to events and wake up only when something relevant happens.
Instead of repeatedly executing queries like this:
PgSQL
|
1 2 3 4 |
while true SELECT * FROM todo_list; sleep; end |
...a connection can simply wait for an event. This eliminates wasted round trips, especially when many users or services are involved.
Using LISTEN and NOTIFY
Two commands form the core of this mechanism:
LISTEN— registers the current session as interested in a named channel.NOTIFY— sends a message to every session listening on a given channel.
To start listening:
PgSQL
|
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 |
A channel is just a name; it doesn't have to be created beforehand. You can issue multiple LISTEN commands on the same connection to follow several channels at once. To send a message:
PgSQL
|
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 |
Messages consist of a channel name and an optional text payload. Here's a practical test:
PgSQL
|
1 2 |
test=# LISTEN x; LISTEN |
When the notification is fired, it goes to every connection that has registered via LISTEN for that channel.
Triggering Notifications from Table Changes
A common pattern is to notify clients whenever a row is added. A trigger on the table can handle this. The example below creates such a setup:
PgSQL
|
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(); |
Using pg_notify()
Inside trigger functions, the built-in pg_notify() function offers a cleaner alternative to dynamic SQL with EXECUTE:
PgSQL
|
1 |
SELECT pg_notify('mychannel', v_txt); |
Testing it with an INSERT shows the notification being prepared:
NOTICE: sending message for INSERT, (1,'2022-07-13 16:18:24.709008+02','sample text')
Notifications are only dispatched after the triggering transaction commits, not during it. This is critical: if the notification were sent mid-transaction, other sessions would receive it before the data changes were actually visible to them. Because uncommitted changes are private to the transaction, message delivery must wait. If the transaction rolls back, the notification is never sent.
