Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA49853 for ; Sun, 21 Nov 1999 20:49:21 -0500 (EST) (envelope-from Inoue@tpf.co.jp) Received: from cadzone ([126.0.1.40] (may be forged)) by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP id KAA02154; Mon, 22 Nov 1999 10:48:53 +0900 From: "Hiroshi Inoue" To: "Tom Lane" , "Oleg Bartunov" Cc: "Matthew Hagerty" , Subject: RE: [SQL] Deleting indexes before vacuum? Date: Mon, 22 Nov 1999 10:53:28 +0900 Message-ID: <000001bf348c$5f35f260$2801007e@cadzone.tpf.co.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-2022-jp" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 Importance: Normal In-Reply-To: <7175.943117272@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4 > -----Original Message----- > From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL. > org]On Behalf Of Tom Lane > Sent: Sunday, November 21, 1999 2:01 AM > To: Oleg Bartunov > Cc: Matthew Hagerty; pgsql-sql@postgreSQL.org > Subject: Re: [SQL] Deleting indexes before vacuum? > > > Oleg Bartunov writes: > > The question is: what's the right way to do drop index/vacuum/create ? > > Do I need transaction ? > > VACUUM should not be run inside a transaction block. (I am planning to > make the code enforce this for 7.0, but it doesn't yet.) So you can't > protect the whole sequence with a transaction. AFAICS the only real > problem is that if you are depending on UNIQUE indexes to catch > attempts to insert duplicate data, an insertion that got in between > the drop and recreate wouldn't get checked. > > I have been toying with the notion of ripping out the existing VACUUM > index handling code and putting in fresh code that would simply do an > index drop and rebuild ;-). This could happen inside VACUUM's exclusive > lock on the table, so it'd be perfectly safe whereas doing it the manual > way is not. But I do not want to do this unless Vadim approves it as a > good idea --- perhaps the existing index-vacuuming code can be fixed to > be an even better solution than this. I haven't looked at the code to > understand why it's so slow or whether there's a way to make it better. > Shouldn't vacuum preserve consistency even in case of abort ? Currently PostgreSQL doesn't do little in case of abort(even commit also ?). So I think it's a strong limitation. It's well known that rebuilding indexes is faster than insert/deleting many index tuples. I have thought rebuilding indexes in vacuum for half a year. But I don't have a reasonable solution yet. Rebuiding indexes in vacuum has a big flaw that index may vanish in case of abort and we may have to recreate index manually. Regards. Hiroshi Inoue Inoue@tpf.co.jp