LISTEN / NOTIFY: Pub/Sub Messaging

Replace a polling loop with a real push model — and see exactly when a listening client actually notices a message

NOTIFY sends a lightweight message on a named channel to every session currently LISTENing on it, over the same protocol connection a client already has open — no separate message broker, no extra infrastructure. pg_notify(channel, payload) does the identical thing as a plain SQL function call, which is what makes it usable directly inside a trigger: a row transitioning to paid can announce itself the instant the transaction commits, with zero polling anywhere.\n\nThere is a real, easy-to-miss detail about how a listening client actually notices a notification: it does not arrive as some kind of interrupt. It sits on the connection until the client's library code next processes a message from the server — which for psql specifically means the notification prints right before or after the result of whatever command the client sends next, not the instant it arrives. This lab makes that timing completely explicit rather than leaving it as a surprise.

Subscribing and Publishing

-- Session A:
LISTEN orders_paid;

-- Session B:
NOTIFY orders_paid, '{"order_id":"abc","amount":129.00}';
-- or, identically, as a function call (usable inside a trigger):
SELECT pg_notify('orders_paid', '{"order_id":"abc","amount":129.00}');

What "Notice It" Actually Means

A listening session does not receive a push in the sense of an interrupt — the notification sits on the wire until the client next reads from the connection, which for psql means: right before it prints the result of whatever command runs next. A session that only ran LISTEN and then sat completely idle can have a notification waiting for a long time before anything makes it visible.

Firing pg_notify From a Trigger

CREATE OR REPLACE FUNCTION notify_order_paid() RETURNS trigger AS $
BEGIN
  IF NEW.status = 'paid' AND (OLD.status IS DISTINCT FROM 'paid') THEN
    PERFORM pg_notify('orders_paid', json_build_object('order_id', NEW.id)::text);
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER orders_paid_trigger
AFTER UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_paid();

The IS DISTINCT FROM guard matters: without it, every update that merely re-saves an already-paid row would fire another notification.

Real Delivery Guarantees — Not a Message Queue

| Guarantee | What it actually means | |-----------|-------------------------| | At-most-once | A notification sent while nobody is listening is simply gone — there is no backlog to replay | | No persistence | Notifications are never written to disk or WAL; a crash mid-delivery loses anything in flight | | 8KB payload cap | The payload string has a hard size limit — large data belongs in a table row the notification merely points at |

This is the honest tradeoff: LISTEN/NOTIFY is dramatically simpler than a message broker, and is not a replacement for one when guaranteed delivery matters.

psql -U postgres -d beer_db -c "LISTEN orders_paid;" -c "SELECT pg_sleep(4);" -c "SELECT 1;" > /tmp/listener.log 2>&1 &

sleep 1

psql -U postgres -d beer_db -c "SELECT pg_notify('orders_paid', '{\"order_id\":\"abc\",\"amount\":129.00}');"

sleep 4

cat /tmp/listener.log

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION notify_order_paid() RETURNS trigger AS \$\$ BEGIN IF NEW.status = 'paid' AND (OLD.status IS DISTINCT FROM 'paid') THEN PERFORM pg_notify('orders_paid', NEW.id::text); END IF; RETURN NEW; END; \$\$ LANGUAGE plpgsql;" -c "CREATE TRIGGER orders_paid_trigger AFTER UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION notify_order_paid();"

psql -U postgres -d beer_db -c "INSERT INTO orders (customer, status) VALUES ('alice', 'placed');" -c "LISTEN orders_paid;" -c "UPDATE orders SET status = 'paid' WHERE customer = 'alice';" -c "SELECT 1;"

Lab 2.7.1 complete. You replaced a polling loop with a real push model, and made its exact timing explicit rather than assumed:\n\n\n LISTEN / NOTIFY : ✅ subscribed and published across two sessions\n Notification timing : ✅ shown to require a client round-trip, not passive idle\n pg_notify() from a trigger : ✅ fired automatically on a real status transition\n

Enable JavaScript to run the live terminal and track your progress.