Re: An archiving query - is it safe?

On 14/01/2014, at 14:55, Vik Fearing wrote:

> On 01/14/2014 12:06 PM, Herouth Maoz wrote:
>> I have regular archiving scripts which traditionally did something like this
>>
>> BEGIN TRANSACTION;
>> INSERT INTO a__archive
>> SELECT * FROM a
>> WHERE <condition>; -- date range condition
>>
>> DELETE FROM a
>> WHERE <condition>; -- same date range condition
>> COMMIT;
>>
>> This is "classic" SQL. I'm thinking of changing this into something like:
>>
>> WITH del AS ( DELETE FROM a WHERE <condition> RETURNING * )
>> INSERT INTO a__archive SELECT * FROM del;
>>
>> As this would only access table "a" once, deleting and returning the records in the same access, which I believe will be more efficient.
>>
>> Is this safe to do? Is there any danger of losing data? Is it atomic?
>
> Yes. No. Yes.

Thank you, I will proceed with this plan, then.

Herouth