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