5 ms·

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

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?

jarym 7y ago
I use logical replication with a modified version of Debezium (https://debezium.io https://debezium.io) - I prefer it to NOTIFY/LISTEN as it means I can have my consumers completely decoupled.
- hashhar 7y ago
  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.
sly010 7y ago
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 https://www.postgresql.org/docs/9.4/sql-listen.html
DrJokepu 7y ago
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.
- exabrial 7y ago
  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.
  - DrJokepu 7y ago
    Or tell the web server to push out the updated version of the record to browsers via web sockets. Very useful feature.
pselbert 7y ago
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.
- Something1234 7y ago
  Why not just setup multiple test machines and have each machine run a test or write the tests such that you used multiple transactions.
chungleong 7y ago
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.

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?

jarym 7y ago

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

hashhar 7y ago
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.

sly010 7y ago

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 https://www.postgresql.org/docs/9.4/sql-listen.html

DrJokepu 7y ago

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.

exabrial 7y ago
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.
- DrJokepu 7y ago
  Or tell the web server to push out the updated version of the record to browsers via web sockets. Very useful feature.

pselbert 7y ago

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.

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

chungleong 7y ago

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.