Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3C8CE2E0052 for ; Wed, 27 Feb 2008 00:48:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 19543-04 for ; Wed, 27 Feb 2008 00:48:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id F3AC52E0057 for ; Wed, 27 Feb 2008 00:48:52 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1R4mnK4013605; Tue, 26 Feb 2008 23:48:49 -0500 (EST) To: "Markus Bertheau" cc: "Joshua D. Drake" , "Scott Marlowe" , "Peter Koczan" , pgsql-performance Subject: Re: disabling an index without deleting it? In-reply-to: <684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com> References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com> <20080226130339.64bd4ec3@commandprompt.com> <9050.1204064560@sss.pgh.pa.us> <684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com> Comments: In-reply-to "Markus Bertheau" message dated "Wed, 27 Feb 2008 08:48:06 +0600" Date: Tue, 26 Feb 2008 23:48:49 -0500 Message-ID: <13604.1204087729@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/363 X-Sequence-Number: 28905 "Markus Bertheau" writes: > 2008/2/27, Tom Lane : >> No, what makes you think that? The index won't change at all in the >> above example. The major problem is, as Scott says, that DROP INDEX >> takes exclusive lock on the table so any other sessions will be locked >> out of it for the duration of your test query. > Why is the exclusive lock not taken later, so that this method can be > used reasonably risk-free on production systems? Er, later than what? Once the DROP is pending, other transactions can hardly safely use the index for lookups, and what should they do about insertions? regards, tom lane