Re: table as log (multiple writers and readers)

Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:
> brian wrote:
>
> > Use a timestamp column also.
>
> That's subject to the same issues.
[...]
> 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.

No, you're right. The problem is that the timestamp is chosen some time
before the commit succeeds. So if there are concurrent writers
committing at the same time, the order of commit is determined by a
race, one that takes place after the timestamp values are set.

Another approach would be to queue the log entries in a "staging" table,
so that a single process could move them into the log. This is fairly
heavyweight, but it would guarantee the consistent sequencing of the log
as seen by a reader (even if the order of entries in the log didn't
always reflect the true commit sequence in the staging table). I'm
hoping someone knows a cleverer trick.

Vance