From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | brian <brian(at)zijn-digital(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: table as log (multiple writers and readers) |
Date: | 2008-04-17 01:43:05 |
Message-ID: | 4806AB29.4080503@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dragan Zubac | 2008-04-17 02:57:12 | Binary bytea to literal strings |
Previous Message | Greg Smith | 2008-04-16 22:54:53 | Re: Master-master replication with PostgreSQL |