
Stop, Collaborate, and Listen/Notify - craigkerstiens
https://omniti.com/seeds/stop-collaborate-and-listen-notify
======
johne20
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
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
That's what the queue table is for.

------
nierman
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."

------
rbranson
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
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 :)

------
gcr
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).

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

------
merb
It's great, unless you are using JDBC.

~~~
jpitz
Maybe this would help you:
[https://wiki.postgresql.org/wiki/PgNotificationHelper](https://wiki.postgresql.org/wiki/PgNotificationHelper)

~~~
merb
That's still not as great as a second async connection on a notification
thread. still lot's of work to make it useful.

------
ngrilly
How do you clean the queue table?

Do you use SKIP LOCKED introduced in PostgreSQL 9.5?

