Re: table as log (multiple writers and readers)

ajs(at)crankycanuck(dot)ca (Andrew Sullivan) writes:
> Oh, one other thing
>
> On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote:
>
>> > One way I can think of doing it is to write a seen_log that notes what the
>> > client has already seen with a timestamp of (say) 1 minute. Then you can
>> > say "go forward from this time excluding ids (ids here)".
>>
>> It won't work with multiple concurrent writers. There is no guarantee
>> that an INSERT with a timestamp older than the one you just saw isn't
>> waiting to commit.
>
> Yeah, I spoke imprecisely. The idea is, "Start at timestamp _t_, but don't
> re-process these ones, which I've seen." The trick is to set your start _t_
> far enough back in time that it's incompatible with your business logic that
> anything could still be pending from then. This is nasty and prone to bugs,
> but it can be coded up.

The alternative pattern is to put a trigger on the table that collects
the primary key ID in a "queue" table.

If some updates were "still in flight" when the queue processor runs,
then it can catch them next time.

Curiously enough, I have had *two* meetings today where this was germaine ;-).
--
select 'cbbrowne' || '@' || 'linuxfinances.info';
http://cbbrowne.com/info/wp.html
Do not worry about the bullet that has got your name on it. It will
hit you and it will kill you, no questions asked. The rounds to worry
about are the ones marked: TO WHOM IT MAY CONCERN.