Hacker News Reader

Comment by ed_blackburn | Hacker News Reader

NOTIFY and LISTEN appeals a lot but my understanding is LISTEN needs to be on the same connection, which isn’t realistic with connection pooling etc? Anyone got any experience with this feature?

View on HN

On the same connection with the NOTIFY-er? I probably misunderstand somethings. Can you point to the part in the documentation that states this? That would defeat the purpose of it entirely.

Edit: In fact the second sentence in the documentation is "[...] NOTIFY channel is invoked, either by this session or another one connected to the same database, all the sessions currently listening on that notification channel are notified, and each will in turn notify its connected client application." [0]

[0] https://www.postgresql.org/docs/9.4/sql-listen.html

The LISTEN and NOTIFY don’t shouldn’t be on the same connection. In fact, many libraries require a separate dedicated connection to LISTEN on.

One feature of PubSub that is also a caveat is that it is transactional. That is great for production code, but makes transactional tests impossible.

Why not just setup multiple test machines and have each machine run a test or write the tests such that you used multiple transactions.

I use logical replication with a modified version of Debezium (https://debezium.io) - I prefer it to NOTIFY/LISTEN as it means I can have my consumers completely decoupled.

Debezium is an awesome project. I use it to maintain an audit log and use it as the event source for downstream microservices. It's also handy for building data analytics or warehousing pipelines.

The best thing is that it can also track deletes and updates unlike simple polling of the database using select queries.

They do not need to be on the same connection, but LISTEN will need its own dedicated connection that is kept alive, it shouldn’t be drawn from the connection pool. Very useful if you want to send messages only and exactly once a transaction is committed.

Let's say you have apps that cache rows with the pk as the cache key. You could notify your app servers that you changed a row and they should drop it from their cache... for instance.

Or tell the web server to push out the updated version of the record to browsers via web sockets. Very useful feature.

It works pretty well. I use NOTIFY in triggers and have various Node.js processes listen for changes. It's a lot easier to leverage the feature when you use plv8.

Reply on news.ycombinator.com