PostgreSQL listen and notify

Listen/notify

PostgreSQL supports publish/subscribe messaging pattern using NOTIFYopen in new window and LISTENopen 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 Listeneropen 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 Notifyopen in new window method:

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

See exampleopen in new window for details.