4 ms·

Stop, Collaborate, and Listen/Notify

42 points by craigkerstiens 11y ago

merb 11y ago
It's great, unless you are using JDBC.
- jpitz 11y ago
  Maybe this would help you: https://wiki.postgresql.org/wiki/PgNotificationHelper https://wiki.postgresql.org/wiki/PgNotificationHelper
  - merb 11y ago
    That's still not as great as a second async connection on a notification thread. still lot's of work to make it useful.
- nierman 11y ago
  yeah, the current/official JDBC driver does not support async listen/notify so you have to do polling. note, the following jdbc driver can do async listen/notify: http://impossibl.github.io/pgjdbc-ng/ http://impossibl.github.io/pgjdbc-ng/
johne20 11y ago
I had no idea. Why isn't this well more well known? Is there some known quirks/limitations why this isn't more widely used for very simple needs? Passing basic messages around like this is a perfect job for LISTEN/NOTIFY because we can solve the job of message passing without the operational overhead of setting up an additional queuing system. I am a big fan of using as little pieces of tech as possible for a project to keep the overall complexity down and this could be very useful for simple needs.
- joevandyk 11y ago
  If there's no receiver/listener running, or if there's some error while processing the message, the message is forgotten. That's the biggest drawback.
  - rch 11y ago
    That's what the queue table is for.
brightball 11y ago
People really need to know more about LISTEN/NOTIFY. It's the primary reason that PG has ruined me for other databases.
nierman 11y ago
the way in which notify/listen interacts with transactions is interesting; from the docs: http://www.postgresql.org/docs/9.5/static/sql-notify.html http://www.postgresql.org/docs/9.5/static/sql-notify.html "... if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY.... Secondly, if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed (either committed or aborted). Again, the reasoning is that if a notification were delivered within a transaction that was later aborted, one would want the notification to be undone somehow — but the server cannot "take back" a notification once it has sent it to the client. So notification events are only delivered between transactions."
gcr 11y ago
Redis also has these operations. See http://redis.io/topics/pubsub http://redis.io/topics/pubsub Note that these simplistic operations (both in Redis and Postgres) don't handle many important parts of message passing (e.g. handling retransmission of failed messages, ensuring the application has processed the messages after receiving it, and so on).
rbranson 11y ago
There is also PgQ from the https://wiki.postgresql.org/wiki/SkyTools https://wiki.postgresql.org/wiki/SkyTools package, which is fantastic and mature. It's like Kafka inside your PostgreSQL. When I was at Instagram, we implemented a multi-region cache invalidation system using PgQ that took advantage of atomicity between PgQ and writes to the source-of-truth data table. http://engineering.instagram.com/posts/548723638608102/ http://engineering.instagram.com/posts/548723638608102/
tyingq 11y ago
On the off chance that someone reading wasn't subjected to 1990's song "Ice Ice Baby" by Vanilla Ice, that's where the title of the article comes from :)
ngrilly 11y ago
How do you clean the queue table? Do you use SKIP LOCKED introduced in PostgreSQL 9.5?

merb 11y ago

It's great, unless you are using JDBC.

jpitz 11y ago
Maybe this would help you: https://wiki.postgresql.org/wiki/PgNotificationHelper https://wiki.postgresql.org/wiki/PgNotificationHelper
- merb 11y ago
  That's still not as great as a second async connection on a notification thread. still lot's of work to make it useful.
nierman 11y ago
yeah, the current/official JDBC driver does not support async listen/notify so you have to do polling. note, the following jdbc driver can do async listen/notify: http://impossibl.github.io/pgjdbc-ng/ http://impossibl.github.io/pgjdbc-ng/

I had no idea. Why isn't this well more well known? Is there some known quirks/limitations why this isn't more widely used for very simple needs? Passing basic messages around like this is a perfect job for LISTEN/NOTIFY because we can solve the job of message passing without the operational overhead of setting up an additional queuing system. I am a big fan of using as little pieces of tech as possible for a project to keep the overall complexity down and this could be very useful for simple needs.

joevandyk 11y ago
If there's no receiver/listener running, or if there's some error while processing the message, the message is forgotten. That's the biggest drawback.
- rch 11y ago
  That's what the queue table is for.

brightball 11y ago

People really need to know more about LISTEN/NOTIFY. It's the primary reason that PG has ruined me for other databases.

nierman 11y ago

the way in which notify/listen interacts with transactions is interesting; from the docs: http://www.postgresql.org/docs/9.5/static/sql-notify.html http://www.postgresql.org/docs/9.5/static/sql-notify.html "... if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY.... Secondly, if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed (either committed or aborted). Again, the reasoning is that if a notification were delivered within a transaction that was later aborted, one would want the notification to be undone somehow — but the server cannot "take back" a notification once it has sent it to the client. So notification events are only delivered between transactions."

gcr 11y ago

Redis also has these operations. See http://redis.io/topics/pubsub http://redis.io/topics/pubsub Note that these simplistic operations (both in Redis and Postgres) don't handle many important parts of message passing (e.g. handling retransmission of failed messages, ensuring the application has processed the messages after receiving it, and so on).

rbranson 11y ago

There is also PgQ from the https://wiki.postgresql.org/wiki/SkyTools https://wiki.postgresql.org/wiki/SkyTools package, which is fantastic and mature. It's like Kafka inside your PostgreSQL. When I was at Instagram, we implemented a multi-region cache invalidation system using PgQ that took advantage of atomicity between PgQ and writes to the source-of-truth data table. http://engineering.instagram.com/posts/548723638608102/ http://engineering.instagram.com/posts/548723638608102/

tyingq 11y ago

On the off chance that someone reading wasn't subjected to 1990's song "Ice Ice Baby" by Vanilla Ice, that's where the title of the article comes from :)

ngrilly 11y ago

How do you clean the queue table? Do you use SKIP LOCKED introduced in PostgreSQL 9.5?