PostgreSQL listen and notify

Listen/notify

PostgreSQL supports publish/subscribe messaging pattern using NOTIFY and LISTEN commands, for example, you can subscribe for notifications using LISTEN command:

LISTEN channel_name;

And then send notifications with optional textual payload:

NOTIFY channel_name, 'optional payload';

Together with table triggers, you can send notifications whenever rows are updated/deleted to invalidate a cache or reindex the table:

CREATE FUNCTION users_after_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('users:updated', NEW.id::text);
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER users_after_update_trigger
AFTER UPDATE ON users
FOR EACH ROW EXECUTE PROCEDURE users_after_update_trigger();

pgdriver.Listener

pgdriver provides Listener which allows to listen for notifications and automatically re-subscribes to channels when the database connection is lost:

ln := pgdriver.NewListener(db)
if err := ln.Listen(ctx, "users:updated"); err != nil {
	panic(err)
}

for notif := range ln.Channel() {
	fmt.Println(notif.Channel, notif.Payload)
}

You can send notifications using Notify method:

if err := pgdriver.Notify(ctx, db, "channel_name", "optional payload"); err != nil {
	panic(err)
}

See example for details.