KDB knowledge

LISTEN and NOTIFY

Send a message to every client that listens on a channel when a transaction commits - over the PostgreSQL port (LISTEN) or HTTP (GET /listen, server-sent events).

NOTIFY sends a short message on a channel. Every client that listens on that channel gets it, the moment the sending transaction commits. It works as in PostgreSQL 14, and web pages can listen over HTTP too.

How
SendNOTIFY orders, 'order 42 paid' or SELECT pg_notify('orders', 'order 42 paid'), from any client, a trigger or a procedure
Listen (PostgreSQL port)LISTEN orders on a connection that stays open (below)
Listen (HTTP, web pages)GET /listen?channel=orders: server-sent events (below); ?pattern=price.* for every channel a pattern matches
Redis-stylePUBLISH, PUBSUB and keyspace events through POST /kv (key-value store): the same channels, sent at once

The rules, the same as PostgreSQL's:

NOTIFY and pg_notify()

Syntax:

NOTIFY channel [, 'payload']
pg_notify(channel_text, payload_text)
PartMeaning
channela name. Without quotes it is lower case (NOTIFY Orders goes to orders); "Orders" keeps its case
payloadtext of at most 7999 bytes; none = empty
pg_notify(...)the same as a function, for a value made in the query. It takes the channel as written (no lower case)

Example: Send inside a transaction: it goes out at COMMIT

BEGIN;
NOTIFY orders, 'order 42 paid';
SELECT pg_notify('orders', 'order ' || 43 || ' paid');
COMMIT;

Result:

BEGIN

transaction started

NOTIFY orders, 'order 42 paid'

notification on channel 'orders': sent to its listeners when the transaction commits

SELECT pg_notify('orders', 'order ' || 43 || ' paid')

pg_notify('orders', 'order ' || 43 || ' paid') (STRING)
NULL

(1 row)

COMMIT

transaction committed: 0 change(s) saved

Example: A payload that is too long (shows an error)

SELECT pg_notify('orders', repeat('x', 8000));

Result:

Error: line 1, column 8: pg_notify: payload string too long

From a trigger

A trigger can send a message for each new row. It is part of the statement's transaction: a failed insert sends nothing.

Example: Tell the listeners about every paid order

CREATE TABLE payments (id LONG PRIMARY KEY, amount DOUBLE);
CREATE TRIGGER tell_paid AFTER INSERT ON payments EXECUTE BEGIN
  SELECT pg_notify('payments', 'paid ' || id) FROM NEW;
END;
INSERT INTO payments VALUES (1, 250.0), (2, 99.5);

Result:

CREATE TABLE payments (

table 'payments' created

CREATE TRIGGER tell_paid AFTER INSERT ON payments EXECUTE BEGIN

trigger 'tell_paid' created on table 'payments'

INSERT INTO payments VALUES (1, 250.0), (2, 99.5)

2 row(s) inserted

SELECT pg_notify('payments', 'paid ' || id) FROM NEW

pg_notify('payments', 'paid ' || id) (STRING)
NULL
NULL

(2 rows)

LISTEN on the PostgreSQL port

Syntax:

LISTEN channel
UNLISTEN channel | *

GET /listen: server-sent events

Syntax:

GET /listen?channel=orders[&channel=payments][&pattern=price.*][&db=name]

The reply is text/event-stream and stays open. A browser reads it with EventSource:

const es = new EventSource('/listen?channel=orders');
es.onmessage = (e) => {
  const n = JSON.parse(e.data);  // {"channel":"orders","payload":"order 42 paid","pid":0}
  refreshOrders();
};
EventWhen
listenonce, when listening started: {"channels":[...],"patterns":[...],"database":"..."}
(message)one per notification: {"channel","payload","pid"}; pid is 0 for a sender that is not a PostgreSQL connection; "pattern" too when it came through a pattern
: ping (a comment)every 15 s, so a proxy keeps the stream open
errorthe stream ends after it (see limits)

Limits