An archiving query - is it safe?

From: Herouth Maoz <herouth(at)unicell(dot)co(dot)il>
To: pgsql-sql(at)postgresql(dot)org
Subject: An archiving query - is it safe?
Date: 2014-01-14 11:06:35
Message-ID: 671916D8-6897-4D4F-940C-3FFF61D67513@unicell.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Thank you,
Herouth

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Vik Fearing 2014-01-14 12:55:23 Re: An archiving query - is it safe?
Previous Message Brice André 2014-01-02 20:12:51 Re: Index on multiple columns VS multiple index