Re: table as log (multiple writers and readers)

David Wilson wrote:
> (I originally missed replying to all here; sorry about the duplicate,
> Vance, but figured others might be interested.
>
> On Wed, Apr 16, 2008 at 1:55 PM, Vance Maverick <vmaverick@pgp.com> wrote:
>>  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.
>
>
> Consider a loop like the following
>
> advisory lock staging table
> if (entries in table)
>    copy entries to main log table as a single transaction
> release advisory lock on staging table
> read out and handle most recent log entries from main table
>
> The advisory lock is automatically released on client disconnect, and
> doing the whole thing within one transaction should prevent any
> partial-copies on failures.
>
> It doesn't matter that there are concurrent inserts to the staging
> table because the staging table is always wiped all at once and
> transferred in a synchronous fashion to the main table. You also can't
> lose data, because it's always in one of the two tables.

If you want to clean up the the staging table I have some concerns about
the advisory lock. I think you mean exclusive table lock.

There are other two options as well:

* Track which data is copies and remove those from the staging table
that are in the new table.

* Use a serializable mode for the staging-to-log-copying transactions.
In this way you can just copy the table and trow away everything
(without checking). This seems rather cheap and allows for concurrent
processing.

- Joris