Re: table as log (multiple writers and readers)

Gurjeet Singh wrote:
> On Wed, Apr 23, 2008 at 12:29 AM, David Wilson <david.t.wilson@gmail.com
> <mailto:david.t.wilson@gmail.com>> wrote:
>
>     On Tue, Apr 22, 2008 at 2:48 PM, Joris Dobbelsteen
>     <joris@familiedobbelsteen.nl <mailto:joris@familiedobbelsteen.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.

Of course, point is, that is another way to define "visibility" in this
context: if present in log table. Point is, a suitable definition is needed.

> I think this plpgsql function would solve the problem of atomic
> read-and-delete operation...
>
> create or replace function log_rotate() returns void as $$
> declare
>   rec record;
> begin
>
>     for rec in delete from t1 returning * loop
>         insert into t2 values( rec.a, rec.b );
>     end loop;
>
> end;
> $$ language 'plpgsql';
>
> select log_rotate();

Don't forget ordering, this was important before...

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT ... INTO log FROM staging ORDER BY ...;
DELETE FROM staging;
COMMIT;

Don't know if that ORDER BY works. It should in this case.

- Joris