A (simple?) solution to the "logical decoding / sequence" problem in Postgres?

Question

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:

1. 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.)

2. 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.)

Questions

1. Am I so dense I don't even know why my solutions are bad?
2. 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.
3. 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".

Answer 1

Your proposed solution is fine so long as you only need to track inserts and you don't need to be scalable. You can use an id-generator table you query with UPDATE ... RETURNING to implement it very simply; search for "gapless sequence".

Since it destroys write concurrency, it'll only be able to grow to a limited point.

It also won't help you track updates or deletes. Unless you start recording them with triggers that insert into a queue table. At which point you might as well use logical decoding. Or Londiste.

Logical decoding is indeed channel based. You can't step in at some arbitrary point. It's designed to ensure that you receive every change exactly once. You can skip over changes, though, or filter them at the output plugin level. The only restrictions really are that:

• you cannot "rewind" once you confirm receipt of changes (but you can intentionally replay the same changes multiple times before you confirm; see the peek functions)
• the initial slot position is always the current server's insert position or slightly after it. You can't create a slot that starts in the past.

Personally I think you should just be using one of the json based logical decoding plugins for this, but I don't know the full picture of what you're trying to achieve.