Re: table as log (multiple writers and readers)

David Wilson wrote:
> On Mon, Apr 21, 2008 at 7:55 PM, Joris Dobbelsteen
> <joris(at)familiedobbelsteen(dot)nl> wrote:
>> 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.
>
> Either works, really. An advisory lock is really just a lock over
> which you have control of the meaning, as long as you're using it in
> the appropriate places. Also, an advisory lock on just the processes
> doing staging-to-log moves would allow writes into the staging table
> to continue concurrently with the staging-to-log transaction (whereas
> an exclusive lock would unnecessarily prevent them).

Describe the mechanism, because I don't really believe it yet. I think
you need to do a advisory lock around every commit of every transaction
that writes to the log table.

If you are only using the advisory lock in the staging-to-log
transaction, how would this prevent newly committed tuples to not show
up during this process? (You can't both delete and insert in a single
statement, I believe, in which case you won't have a problem anyways).

> Also, while Vance appears to have chosen to have a dedicated
> staging-to-log process, even that isn't necessary- each reader can
> simply do the lock/clear staging/unlock before any attempt to read-
> unless you're polling that log table at truly crazy rates, the
> overhead should be negligible and will ensure that the staging table
> is simply cleared out "whenever necessary" while removing the
> complexity of a separate process.

Using serialization mode for the staging-to-log process seems to be the
most efficient methods, as it won't even block writers.

- Joris