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.
"... 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."
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).
There is also PgQ from the 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/
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 :)
13 comments
[ 3.3 ms ] story [ 35.0 ms ] threadnote, the following jdbc driver can do async listen/notify: http://impossibl.github.io/pgjdbc-ng/
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.
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."
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).
Do you use SKIP LOCKED introduced in PostgreSQL 9.5?