On Tue, Apr 22, 2008 at 2:48 PM, Joris Dobbelsteen <joris@xxxxxxxxxxxxxxxxxxxxx> 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.t.wilson@xxxxxxxxx
