Re: table as log (multiple writers and readers)

On Tue, Apr 22, 2008 at 2:48 PM, Joris Dobbelsteen
<joris(at)familiedobbelsteen(dot)nl> wrote:
>
> Ah, yes, all visible rows...
> My point is that, unless you use a transaction with serializable isolation,
> this all visible rows for the second statement might be different from those
> that you copied into the log table.
>
> With the normal Read committed isolation level you suffer from a possible
> nonrepeatable read that might change tuple visibility between different
> statements.

That depends on implementation. A select into ... to do the initial
copy followed by a delete where... with the where clause referencing
the log table itself to ensure that we delete only things that now
exist in the log table, or a row by row insert/delete pair. Either
would provide the appropriate level of protection from accidental
deletion of more things than you intended without harming concurrency.
The delete referencing the log table might require that the log table
be indexed for performance, but it's likely that such indexing would
be done anyway for general log use.
--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com