Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A3295B620D2 for ; Wed, 1 Jun 2011 09:45:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 48526-01 for ; Wed, 1 Jun 2011 12:45:09 +0000 (UTC) X-Greylist: delayed 00:06:45.432632 by SQLgrey-1.7.6 Received: from nm6-vm1.bullet.mail.ukl.yahoo.com (nm6-vm1.bullet.mail.ukl.yahoo.com [217.146.183.235]) by mail.postgresql.org (Postfix) with SMTP id 06DE9B5F70A for ; Wed, 1 Jun 2011 09:45:08 -0300 (ADT) Received: from [217.146.183.211] by nm6.bullet.mail.ukl.yahoo.com with NNFMP; 01 Jun 2011 12:38:23 -0000 Received: from [217.146.183.166] by tm4.bullet.mail.ukl.yahoo.com with NNFMP; 01 Jun 2011 12:38:23 -0000 Received: from [127.0.0.1] by omp1007.mail.ukl.yahoo.com with NNFMP; 01 Jun 2011 12:38:23 -0000 X-Yahoo-Newman-Property: ymail-5 X-Yahoo-Newman-Id: 141641.77069.bm@omp1007.mail.ukl.yahoo.com Received: (qmail 9965 invoked by uid 60001); 1 Jun 2011 12:38:23 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.uk; s=s1024; t=1306931903; bh=wIwU5/ctOHz7qZFJlNuMgx3V2O0gPLQFU6o4/+dNUKU=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=5Nx9FCatoqayiF0k5l4suhTif7NfmeR75UjzSALatIVWmJMo0KUjvTg8iYQ6kaKtDi9i9SVQJAmBDnTfVTHYXMtJqBKaunpGHzNWzRYG+RaJzuSwuNZTxK/9R0egiKJ+ipJIu3DEA9xClILNmrer7qeMpmioPBnNRYPsHSxI44Q= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=MHrGm0+Gl3LXe1ZzzXKnl4BHBGuCjnyi7LGd+tKUMDY/TyX1CQDXArB1GLGvUqqOnHLnhfjy8AEFHpyXEPNJpNCF07E56OGz9j9n7De/z0MPfXRWRVhDwFpOa45p81o06u8vMfMJuPKVeCqkEXmgbFIu7sNrGi4s7mCOg3tf0vE=; Message-ID: <35505.8950.qm@web26008.mail.ukl.yahoo.com> X-YMail-OSG: Oli9EA8VM1lOhR6_h4HfI2bSR5IVCmUvx14W3oxRiJrq5a9 teQjX9pOYvl0PAsuB2xr0A4QGuCNgwu2DqsG1.FREPtB7S8Cw8D_NOPHAwWX UeMRP8qJSiH7MEq3eJHjKrO7W1ihvvLs471byEshEV7.QYreUYOXPZodtBfY 4EcUqpOAI197bkKkGwXC.2rrXnIlHXS.QlXcpp65dunoLIso4Ko9GpPkKoCA K0VZJn04pC8qrPtOzDPZ.yDcfneUd.Iz.GyjSfCpPEqqMNjcE2jeXvmJQ1RM fBnuTeuwMKw_gAqjYmKo1DDH_Bki96kC48XvXuTM9qnHkAR9tbnOAKmaXAIN QmTxrcg9_vkTPE248sbqCJ7NzI8TFZcFe3R3ZOsY0zFZim9vrELffgdwvyNx KSCeQmvJZCmITyErYP_DXyvFllHe7E.IBIPmVRII9Ig17GMhpKS44vFebXZ0 wmwMk3IJjnzxJJzLQ1XPmNguECwBJmQWkqkNVR4A10ad4D3PvQXo6sOKuwQE VtguVIf6IVpdZxOUJrjjxqiw- Received: from [83.100.146.195] by web26008.mail.ukl.yahoo.com via HTTP; Wed, 01 Jun 2011 13:38:22 BST X-Mailer: YahooMailWebService/0.8.111.303096 Date: Wed, 1 Jun 2011 13:38:22 +0100 (BST) From: Glyn Astill Reply-To: Glyn Astill Subject: Reindexing primary keys without locking To: "pgsql-admin@postgresql.org" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1216094147-1306931902=:8950" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.897 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201106/4 X-Sequence-Number: 37630 --0-1216094147-1306931902=:8950 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi Guys,=0A=0ADoes anyone know of any tricks to replace primary key indexes= without using reindex? Or any other method that will not lock the table fo= r an extended amount of time on 8.4?=0A=0AI've got a bloated primary key in= dex on a table and I'd like to try and clean it up without downtime.=0A=0AA= quick search turned up the following thread that mentioned a possible patc= h, but I couldn't find much else.=0A=0Ahttp://archives.postgresql.org/messa= ge-id/AANLkTinoaVd9RGgOyyudbLWFQFvJKWGdiwyKxOcxtfxV@mail.gmail.com=0A=0ATha= nks=0AGlyn=0A --0-1216094147-1306931902=:8950 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi Guys,

Does anyone know of any tricks to replace primary key ind= exes without using reindex? Or any other method that will not lock the tabl= e for an extended amount of time on 8.4?

I've got = a bloated primary key index on a table and I'd like to try and clean it up = without downtime.

A quick search turned up the fol= lowing thread that mentioned a possible patch, but I couldn't find much els= e.

http://archives.postgresql.org/message-id/AANLk= TinoaVd9RGgOyyudbLWFQFvJKWGdiwyKxOcxtfxV@mail.gmail.com

Thanks
Glyn
--0-1216094147-1306931902=:8950--