After reading this mind-blowing answer by Craig Ringer to my previous question Achieving "sequence number", rather than dateTime, for updates; postgres specifically I've become interested in the "logical decoding / sequence" racetrack problem in Postgres.
As I understand it (I may have stuffed-up to begin with), here is the racetrack problem in question:
- The write for "A" gets a time of 100, and is written eventually at 300.
- "B" will get a time of 120, and is written at 200.
- A syncer reads at 250, as shown.
It gets the B, with a value of 120.
The A (100) is lost forever. (The syncer thinks that it has everything up to 120.)
Assuming I even grasp the full depth of the problem. I see two simple-minded solutions:
Anything that writes has to have a lock. So, actually B would not be allowed to start until A is finished. (It does not matter if a read happens in the middle of one.)
The reads have to wait on a write-lock. So, the read can't happen in the example until B is finished. (It does not matter if two writes are overlapping.)
- Am I so dense I don't even know why my solutions are bad?
- Is there perhaps some obvious, everyday setting for Postgres I don't know about (like: "Only allow one complete write at a time!" or something, which would implement (1) without bothering about locking.
- Indeed do I even fully grasp the problem?
Regarding the logical-decoding system in Postgres. (Doco) In my limited understanding, it works on a "channel based" approach. So you could make, say, 1, 2, 3 or more "channels" of your logical decoding, which will each individually be at a different "points" in the stream. With logical decoding you can not (as I understand it) just have "arbitrary streaming" where you can say "give me everything from point X".