Hacker News Reader

Comment by crad | Hacker News Reader

(Also posted to the comments section of the blog)

Great writeup! One of the issues I've run into with LISTEN/NOTIFY is the fact that it's not transaction safe. ie if you call NOTIFY and then encounter an error causing a rollback, you can't undo the NOTIFY.

I ended up building a system on top of PgQ (https://wiki.postgresql.org/wiki/SkyTools#PgQ) called Mikkoo (https://github.com/gmr/mikkoo#mikkoo) that uses RabbitMQ to talk with the distributed apps that needed to know about the transaction log. Might be helpful if you end up running into transactional issues with your use of LISTEN/NOTIFY.

View on HN

NOTIFY is transaction-safe: if the NOTIFY-ing transaction rolls back, the NOTIFY will not be delivered. See the discussion here ("NOTIFY interacts with SQL transactions in some important ways..."):

https://www.postgresql.org/docs/9.6/static/sql-notify.html

Yup, my bad, thanks. I confused the two problems I worked through in working through my use case. The transaction safety one was not the NOTIFY version of the project. That had different issues, which I need to go back and look at my notes for. My memory is a bit hazy, but IIRC we were seeing notifications dropped under high velocities and needed delivery guarantees.

I seem to remember that when it was first implemented there was a fairly small buffer for queued messages. That might have contributed to your problem. The docs say it's 8GB by default now, which is probably sufficient.

The original NOTIFY also didn't have a payload (that was added in PG9) which made it much harder to know what you were notified about.

That one simple addition made pre-9.0 NOTIFY and post-9.0 NOTIFY completely different beasts.

Messages are limited to 8KB, so if the buffer is 8GB, that's a lot of messages that can be queued up.

The whole point of LISTEN/NOTIFY is that it's transaction safe! Not sure where you got that idea.

Not only is NOTIFY transactional, so is LISTEN.

The only serious limitation I can think of: There was some talk, long ago, about propagating notifications via the WAL, so that LISTEN could work on a read-only slave. I don't know if this was implemented.

Why was the pgsql-listen-exchange abandoned? have you found some problems with that approach?

Mainly because I've had way too much on my plate and have not had time to work on any of my open source projects if they're not directly related to something I'm doing at work.

That's good to hear, the fact that the solution itself does not have a problem, i got it compiling for 3.6.x and working. I will try to look into stability (not crashing everything on disconnect) so any advice on how to go about that would be appreciated (email in profile).

Reply on news.ycombinator.com