On Wed, Apr 23, 2008 at 12:29 AM, David Wilson <
david.t.wilson@gmail.com> wrote:
On Tue, Apr 22, 2008 at 2:48 PM, Joris Dobbelsteen
<
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.
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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB
http://www.enterprisedb.com Mail sent from my BlackLaptop device