Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 043A29FB322 for ; Wed, 11 Oct 2006 08:30:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 73543-10 for ; Wed, 11 Oct 2006 11:30:25 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id CF4859FB32F for ; Wed, 11 Oct 2006 08:30:24 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id k26so132707nfc for ; Wed, 11 Oct 2006 04:30:23 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Q3yDQNeAKq65KV2ruen2OmDNJMXEqhcm/Z9IlyYZSIdEegSumHbk1RCQd/rBHKTI7imB3gHq8HbnQ52RkgCC6uRYflZ1QguPXD31W9DE3blDEYwPolvgjrjWaJ9m82l/MurbFeCccrgW0rY4GosNMcqza3Dk9li8B7hd/KzTbHo= Received: by 10.82.127.15 with SMTP id z15mr54972buc; Wed, 11 Oct 2006 04:30:22 -0700 (PDT) Received: by 10.82.159.10 with HTTP; Wed, 11 Oct 2006 04:30:22 -0700 (PDT) Message-ID: <621eda8a0610110430y5be06e22t27d6c99991e86dc8@mail.gmail.com> Date: Wed, 11 Oct 2006 13:30:22 +0200 From: "Fourat Zouari" To: "Jim C. Nasby" Subject: Re: Copying data from table to table (cloned tables) Cc: "Bricklen Anderson" , pgsql-novice@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <20061010153724.GI72517@nasby.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_401_2420727.1160566222543" References: <621eda8a0610091651oc78994fy2b358260f5c9b58a@mail.gmail.com> <452AE2FF.1020409@presinet.com> <20061010152457.GG72517@nasby.net> <20061010153724.GI72517@nasby.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.496 tagged_above=0 required=5 tests=HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/78 X-Sequence-Number: 23381 ------=_Part_401_2420727.1160566222543 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline what's the benefits using a serialized trans ? On 10/10/06, Jim C. Nasby wrote: > > On Tue, Oct 10, 2006 at 10:24:57AM -0500, Jim C. Nasby wrote: > > > If you just want to copy the data across to the other table: > > > begin; > > > insert into table2 select * from table1 where ; > > > commit; > > > > > > if you also want to remove that same data from table1: > > > begin; > > > insert into table2 select * from table1 where ; > > > delete from table1 where ; > > > commit; > > I forgot to mention you could also use a serializable transaction with > your method... > > BEGIN ISOLATION LEVEL SERIALIZABLE; > INSERT ... > DELETE ... > COMMIT; > > Just remember you'll need to deal with the possibility of a 'could not > serialize' error. > -- > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > ------=_Part_401_2420727.1160566222543 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline what's the benefits using a serialized trans ?

On 10/10/06, Jim C. Nasby <jim@nasby.net> wrote:
On Tue, Oct 10, 2006 at 10:24:57AM -0500, Jim C. Nasby wrote:
> > If you just want to copy the data across to the other table:
> > begin;
> > insert into table2 select * from table1 where <some criteria>;
> > commit;
> >
> > if you also want to remove that same data from table1:
> > begin;
> > insert into table2 select * from table1 where <some criteria>;
> > delete from table1 where <same criteria as above>;
> > commit;

I forgot to mention you could also use a serializable transaction with
your method...

BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT ...
DELETE ...
COMMIT;

Just remember you'll need to deal with the possibility of a 'could not
serialize' error.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com       512.569.9461 (cell)

------=_Part_401_2420727.1160566222543--