Re: table as log (multiple writers and readers)

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