5 ms·
Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?
by hombre_fatal 1y ago
Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?
- soursoup 1y agoIsn’t it standard practice to have a separate TCP stream for NOTIFY or am I mistaken
- remram 1y agoYou mean for LISTEN?
- nick_ 1y agoMy thought as well. You could add notify commands to a temp table during the transaction, then run NOTIFY on each row in that temp table after the transaction commits successfully?
- foota 1y agoWouldn't you need to then commit to remove the entries from the temp table?
- zbentley 1y agoNo, so long as the rows in there are transactionally guaranteed to be present or not, a sweeper script can handle removing failed “publishes” (notifys that didn’t delete their row) later. This does sacrifice ordering and increases the risk of duplicates in the message stream, though.
- zbentley 1y agoThis is roughly the “transactional outbox” pattern—and an elegant use of it, since the only service invoked during the “publish” RPC is also the database, reducing distributed reliability concerns. …of course, you need dedup/support for duplicate messages on the notify stream if you do this, but that’s table stakes in a lot of messaging scenarios anyway.
- parthdesai 1y agoYou lose transactional guarantees if you notify outside of the transaction though
- hombre_fatal 1y agoYeah, but pub/sub systems already need to be robust to missed messages. And, sending the notify after the transaction succeeds usually accomplishes everything you really care about (no false positives).
- parthdesai 1y agoWhat happens when transaction succeeds but the execution of NOTIFY fails if it's outside of transaction, in it's own separate connection?
- saltcured 1y agoFor reliability, you can make the recipient poll the table(s) of record for relevant state and use the out-of-band notification channel as a latency-reducer. So, the poller is eventually consistent at some configured polling interval, but opportunistically can respond much sooner when told to check again ahead of the next scheduled poll time. In my experience, this means you make sure the polling solution is complete and correct, and the notifier gets reduced to a wake-up signal. This signal doesn't even need to carry the actionable change content, if the poller can already pose efficient queries for whatever "new stuff" it needs. This approach also allows the poller to keep its own persistent cursor state if there is some stateful sequence to how it consumes the DB content. It automatically resynchronizes and the notification channel does not need to be kept in lock-step with the consumption.
- parthdesai 1y agofwiw - that's what Oban did for the most part. It sent a signal to a worker that there was a new job to pick up and work on. At scale, even that was an issue.
- 1y ago
- zerd 1y agoThat would make the locked time shorter, but it would still contend on the global lock, right?