Re: table as log (multiple writers and readers)

On Wed, Apr 23, 2008 at 12:29 AM, David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com>
wrote:

> 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.

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();

Best regards,
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com

singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device