Using Listen and Notify
1. Understanding LISTEN/NOTIFY
| Property | Detail |
|---|---|
| Scope | Per-database |
| Delivery | Async, at-least-once after COMMIT |
| Payload | Up to 8000 bytes (text) |
| Duplicates | Identical (channel,payload) within TX coalesced |
2. Subscribing to Channel
LISTEN orders_changed;3. Sending Notification
NOTIFY orders_changed;4. Sending Notification with Payload
NOTIFY orders_changed, 'order_id=1001';5. Using pg_notify Function
SELECT pg_notify('orders_changed', jsonb_build_object('id', NEW.id, 'op', TG_OP)::text);6. Receiving Notifications
// node-postgres example
client.on('notification', msg => handle(msg.channel, msg.payload));
await client.query('LISTEN orders_changed');7. Unsubscribing from Channel
UNLISTEN orders_changed;8. Unsubscribing from All Channels
UNLISTEN *;9. Listing Active Listeners
SELECT pid, channel FROM pg_listening_channels(); -- in current session
SELECT * FROM pg_stat_activity WHERE wait_event = 'Notify';10. Using NOTIFY in Triggers
CREATE FUNCTION on_order_change() RETURNS trigger LANGUAGE plpgsql AS $
BEGIN
PERFORM pg_notify('orders_changed', row_to_json(NEW)::text);
RETURN NEW;
END $;
CREATE TRIGGER orders_notify AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION on_order_change();11. Understanding Payload Size Limit
Warning: Payload > ~8000 bytes throws error. Send IDs and let client re-fetch large rows.