brian wrote:
I don't mean to rely on *only* the timestamp, but for the reader to remember both the last ID and the timestamp for that particular transaction. When the next read occurs it should check to see if there's an earlier timestamp with a higher ID than that remembered.
[snip]
Wait--would WRITER 1 have the higher ID?
No, it'll have a lower id in this case because it calls nextval('sequence_name') first. Writer 1 would have a lower id, and a lower timestamp (because its transaction began first) even if it committed later. Using clock_timestamp() in the insert will not help, because the first transaction to insert (as in this case) is not necessarily the first to commit.
If a reader sees a given id and timestamp, that doesn't meant there aren't transactions with lower ids, and lower timestamps, still uncomitted or in the process of committing.
What you want is a timestamp that's generated at commit time with a guarantee that no later commits will have equal or lower timestamps . As far as I know (I'm *VERY* far from the authority here) there's no way to achieve that, so you have to serialize your commits to the table somehow.
-- Craig Ringer
