X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id AABF49DCC80 for ; Wed, 1 Mar 2006 15:21:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72037-02-2 for ; Wed, 1 Mar 2006 15:21:35 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 73D719DCC07 for ; Wed, 1 Mar 2006 15:21:33 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 4FEBD56459; Wed, 1 Mar 2006 13:21:34 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Wed, 1 Mar 2006 13:21:33 -0600 Date: Wed, 1 Mar 2006 13:21:33 -0600 From: "Jim C. Nasby" To: Tom Lane Cc: Ashley Moran , pgsql-admin@postgresql.org Subject: Re: REINDEX during a transaction Message-ID: <20060301192133.GL82012@pervasive.com> References: <200602211250.44749.ashley.moran@codeweavers.net> <10725.1141226621@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <10725.1141226621@sss.pgh.pa.us> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060301:tgl@sss.pgh.pa.us::X6LyEBwW10OqArT6:00000000000000000000 0000000000000000000000000JY8 X-Hashcash: 1:20:060301:ashley.moran@codeweavers.net::SsUZGZbwqRvbpRc/:000000000 0000000000000000000000004un4 X-Hashcash: 1:20:060301:pgsql-admin@postgresql.org::TJiYwaWP4PTJswjs:00000000000 0000000000000000000000000vez X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.109 required=5 tests=[AWL=0.109] X-Spam-Score: 0.109 X-Spam-Level: X-Archive-Number: 200603/12 X-Sequence-Number: 20769 On Wed, Mar 01, 2006 at 10:23:41AM -0500, Tom Lane wrote: > Ashley Moran writes: > > BEGIN WORK; > > DELETE FROM X; COPY X ...; REINDEX TABLE X; > > DELETE FROM Y; COPY Y ...; REINDEX TABLE Y; > > DELETE FROM Z; COPY Z ...; REINDEX TABLE Z; > > COMMIT; > > Why don't you use TRUNCATE? Why do you think you need REINDEX at all? > > If you do need it, you'd be best off to drop the indexes, truncate, > copy, re-create the indexes. See > http://www.postgresql.org/docs/8.1/static/populate.html#POPULATE-RM-INDEXES And to answer some of your original question, I don't believe there's any advantage to wrapping the REINDEXes into the transaction. If can't use Tom's suggestion of dropping the indexes before the COPY, you might want to instead create new indexes with the same definition of existing ones, and then drop the old ones; readers of the table will react differently to the two. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461