agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedAn archiving query - is it safe?
3+ messages / 2 participants
[nested] [flat]
* An archiving query - is it safe?
@ 2014-01-14 11:06 Herouth Maoz <herouth@unicell.co.il>
0 siblings, 1 reply; 3+ messages in thread
From: Herouth Maoz @ 2014-01-14 11:06 UTC (permalink / raw)
To: 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: An archiving query - is it safe?
@ 2014-01-14 12:55 Vik Fearing <vik.fearing@dalibo.com>
parent: Herouth Maoz <herouth@unicell.co.il>
0 siblings, 1 reply; 3+ messages in thread
From: Vik Fearing @ 2014-01-14 12:55 UTC (permalink / raw)
To: Herouth Maoz <herouth@unicell.co.il>; pgsql-sql
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.
--
Vik
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: An archiving query - is it safe?
@ 2014-01-14 14:34 Herouth Maoz <herouth@unicell.co.il>
parent: Vik Fearing <vik.fearing@dalibo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Herouth Maoz @ 2014-01-14 14:34 UTC (permalink / raw)
To: Vik Fearing <vik.fearing@dalibo.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-01-14 14:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-14 11:06 An archiving query - is it safe? Herouth Maoz <herouth@unicell.co.il>
2014-01-14 12:55 ` Vik Fearing <vik.fearing@dalibo.com>
2014-01-14 14:34 ` Herouth Maoz <herouth@unicell.co.il>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox