Craig Ringer wrote:
> brian wrote:
>
>> Use a timestamp column also.
>
> That's subject to the same issues, because a transaction's
> current_timestamp() is determined at transaction start. So, in a
> situation like this:
>
>
> WRITER 1 WRITER 2 READER 1
> --------------------------------------------
> BEGIN
> BEGIN
> INSERT
> INSERT
> COMMIT
> BEGIN
> SELECT
> COMMIT
>
> then READER 1 will see the most recent timestamp as that inserted by
> WRITER 2, but it won't see the row inserted by WRITER 1 with an earlier
> timestamp.
>
> I don't think it's even OK in the case of a single-statement INSERT
> (where the transaction is implicit) and/or with the use of
> clock_timestamp() ... though I'm less sure about that.
>
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. The database
"knows" that WRITER 1 was there first. If it's important to the
application then the reader will need to take some action to re-order
things based on what it has already read, which it could do if it if it
compared timestamps and ID order. It needn't keep the complete set of
IDs in memory.
Wait--would WRITER 1 have the higher ID?