4 ms·
(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.
by crad 10y ago
(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 https://wiki.postgresql.org/wiki/SkyTools#PgQ) called Mikkoo (https://github.com/gmr/mikkoo#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.
- neilc 10y agoNOTIFY 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 https://www.postgresql.org/docs/9.6/static/sql-notify.html
- deleted 10y ago[deleted]
- crad 10y agoYup, 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.
- netghost 10y agoI 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.
- williamstein 10y agoMessages are limited to 8KB, so if the buffer is 8GB, that's a lot of messages that can be queued up.
- masklinn 10y agoThe 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.
- ruslan_talpa 10y agoWhy was the pgsql-listen-exchange abandoned? have you found some problems with that approach?
- deleted 10y ago[deleted]
- crad 10y agoMainly 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.
- ruslan_talpa 10y agoThat'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).
- lobster_johnson 10y agoThe 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.