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?
