PostgreSQL listen and notify

Listen/notify

PostgreSQL supports publish/subscribe messaging pattern using NOTIFYLISTEN NOTIFY - 图1open in new window and LISTENLISTEN NOTIFY - 图2open in new window commands, for example, you can subscribe for notifications using LISTEN command:

  1. LISTEN channel_name;

And then send notifications with optional textual payload:

  1. 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:

  1. CREATE FUNCTION users_after_update_trigger()
  2. RETURNS TRIGGER AS $$
  3. BEGIN
  4.   PERFORM pg_notify('users:updated', NEW.id::text);
  5.   RETURN NULL;
  6. END;
  7. $$
  8. LANGUAGE plpgsql;
  10. CREATE TRIGGER users_after_update_trigger
  11. AFTER UPDATE ON users
  12. FOR EACH ROW EXECUTE PROCEDURE users_after_update_trigger();

pgdriver.Listener

pgdriver provides ListenerLISTEN NOTIFY - 图3open in new window which allows to listen for notifications and automatically re-subscribes to channels when the database connection is lost:

  1. ln := pgdriver.NewListener(db)
  2. if err := ln.Listen(ctx, "users:updated"); err != nil {
  3.     panic(err)
  4. }
  6. for notif := range ln.Channel() {
  7.     fmt.Println(notif.Channel, notif.Payload)
  8. }

You can send notifications using NotifyLISTEN NOTIFY - 图4open in new window method:

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

See exampleLISTEN NOTIFY - 图5open in new window for details.