Comment by hombre_fatal
2 days ago
Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?
2 days ago
Interesting. What if you just execute `NOTIFY` in its own connection outside of / after the transaction?
My 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?
This 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.
Wouldn't you need to then commit to remove the entries from the temp table?
No, 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.
You lose transactional guarantees if you notify outside of the transaction though
Yeah, 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).
What happens when transaction succeeds but the execution of NOTIFY fails if it's outside of transaction, in it's own separate connection?
6 replies →
... And working outside of the guarantee is harder, especially if you're in a "move fast and break things because we can fix it later" mode.
Anyway, the article indicates that the fix was very simple and primarily in the application layer. Makes me wonder if someone was getting "creative" when they used LISTEN/NOTIFY.
Isn’t it standard practice to have a separate TCP stream for NOTIFY or am I mistaken
You mean for LISTEN?
That would make the locked time shorter, but it would still contend on the global lock, right?