Thread: An archiving query - is it safe?

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

Is this safe to do? Is there any danger of losing data? Is it atomic?



Thank you,
Herouth

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
bemore efficient.
 
>
> Is this safe to do? Is there any danger of losing data? Is it atomic?

Yes.  No.  Yes.

-- 
Vik

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