Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 6949B9FB391 for ; Wed, 11 Oct 2006 09:56:19 -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 10081-02 for ; Wed, 11 Oct 2006 12:56:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchsrvr.winemantech.com (unknown [68.188.255.45]) by postgresql.org (Postfix) with SMTP id DFC429FB251 for ; Wed, 11 Oct 2006 09:56:09 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6ED34.9E3EF080" Subject: Re: [ADMIN] Copying data from table to table (cloned tables) Date: Wed, 11 Oct 2006 08:56:09 -0400 Message-ID: In-Reply-To: <621eda8a0610110430y5be06e22t27d6c99991e86dc8@mail.gmail.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [NOVICE] [ADMIN] Copying data from table to table (cloned tables) Thread-Index: AcbtKLCgmdHmgqeGQ6KBssxtEi6KSQAC8m5A From: "Brandon Aiken" To: "Jim C. Nasby" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200610/69 X-Sequence-Number: 17836 This is a multi-part message in MIME format. ------_=_NextPart_001_01C6ED34.9E3EF080 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable It makes it so that a transaction is highly isolated. =20 http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html =20 =20 -- Brandon Aiken CS/IT Systems Engineer ________________________________ From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Fourat Zouari Sent: Wednesday, October 11, 2006 7:30 AM To: Jim C. Nasby Cc: Bricklen Anderson; pgsql-novice@postgresql.org; pgsql-admin@postgresql.org Subject: Re: [NOVICE] [ADMIN] Copying data from table to table (cloned tables) =20 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 ;=20 > > commit; > > > > if you also want to remove that same data from table1: > > begin; > > insert into table2 select * from table1 where ; > > delete from table1 where ;=20 > > 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=20 serialize' error. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) =20 ------_=_NextPart_001_01C6ED34.9E3EF080 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

It makes it so that a transaction = is highly isolated.

 

http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html

 

 

--

Brandon Aiken

CS/IT Systems = Engineer


From: = pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Fourat Zouari
Sent: Wednesday, October = 11, 2006 7:30 AM
To: Jim C. Nasby
Cc: Bricklen Anderson; pgsql-novice@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [NOVICE] = [ADMIN] Copying data from table to table (cloned = tables)

 

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           &n= bsp;           &nb= sp;           &nbs= p;        jim@nasby.net
EnterpriseDB      http://enterprisedb.com  &= nbsp;    512.569.9461 (cell)

 

------_=_NextPart_001_01C6ED34.9E3EF080--