Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37608=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1W33Xl-0007I8-JI
 for pgsql-sql@arkaria.postgresql.org; Tue, 14 Jan 2014 12:56:34 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37608=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1W33Xk-0000qg-Ua
 for pgsql-sql@arkaria.postgresql.org; Tue, 14 Jan 2014 12:56:32 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <vik.fearing@dalibo.com>) id 1W33Xj-0000or-Om
 for pgsql-sql@postgresql.org; Tue, 14 Jan 2014 12:56:31 +0000
Received: from mimolette.dalibo.net ([212.85.154.222])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <vik.fearing@dalibo.com>) id 1W33Xh-0003JM-SY
 for pgsql-sql@postgresql.org; Tue, 14 Jan 2014 12:56:31 +0000
Received: from [192.168.0.38] (ip-68.net-81-220-133.standre.rev.numericable.fr
 [81.220.133.68])
 by mimolette.dalibo.net (Postfix) with ESMTPSA id 4FF8C6820061;
 Tue, 14 Jan 2014 13:55:57 +0100 (CET)
Message-ID: <52D533BB.5030602@dalibo.com>
Date: Tue, 14 Jan 2014 13:55:23 +0100
From: Vik Fearing <vik.fearing@dalibo.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: Herouth Maoz <herouth@unicell.co.il>, pgsql-sql@postgresql.org
Subject: Re: An archiving query - is it safe?
References: <671916D8-6897-4D4F-940C-3FFF61D67513@unicell.co.il>
In-Reply-To: <671916D8-6897-4D4F-940C-3FFF61D67513@unicell.co.il>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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