Re: Tracking row updates - race condition

Thanks for the input everyone. I think Harald's approach will work
well, so I'm planning on doing what he suggested, with a few
modifications. I think I can still use a sequence-backed INTEGER rather
than TIMESTAMP, and have the trigger that sets the revision to NULL also
NOTIFY the daemon that will update the revision to
nextval('revision_sequence_name') of any updates, rather than running it
from a cron job every X minutes. The updates sent to the client would
also include rows with revision = NULL so that the client would not lag
behind, at the expense of sometimes having updates sent twice to the client.

Thanks again,

Alex

Harald Fuchs wrote:

>In article <423F09CF(dot)2030008(at)alexandcarmen(dot)com>,
>Alex Adriaanse <alex(at)alexandcarmen(dot)com> writes:
>
>
>>I think that would greatly decrease the chances of a race condition
>>occurring, but I don't think it'd solve it. What if 150 other
>>revisions occur between a row update and its corresponding commit?
>>
>>
>
>How about the following:
>
>* Use a TIMESTAMP rather than a SERIAL
>* Set this timestamp to NULL in your INSERT/UPDATE trigger
>* Use a cron job to set the timestamp to current_timestamp when it's NULL
>
>This way the client would lag behind somewhat, depending on the cron
>job frequency, but it should not miss a change.
>
>