| 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: | Whole Thread | Raw Message | 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
| 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 |