Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2D1472E0045 for ; Wed, 27 Feb 2008 01:57:06 -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 41421-03-5 for ; Wed, 27 Feb 2008 01:57:02 -0400 (AST) X-Greylist: delayed 00:20:30.221546 by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.228]) by postgresql.org (Postfix) with ESMTP id 0B3F12E006B for ; Wed, 27 Feb 2008 01:57:00 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id r21so2444669wxc.28 for ; Tue, 26 Feb 2008 21:57:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=OP+JILaDTwwSvWM/EcBK/SScJ9t7pGo6A0tn0cF/AKc=; b=K4AmgmgSbYER0mhmZS3dgXmW3yiX5qTwmk2tNOgynGe+i9ujoq2naOZ2Ronne3EoL7lOE3ubmbwl1/09LKtf4vlyFsGgqZ/JPVRbO+J0DFg4HAJr0lqRQlhKgh7YwYvfTjW3VegQVjZJpXevu7BJrPeqQTNjpPVe4Kc+Ip59q9c= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=kLSzqheNCflhFeNNukA2Qx8zR0je730Ztz7t5XRJCvGH3xY3uslhpGCSHhjwj17N2WBBb1NarlP92AyP3vIKMPSHtqfTZHJsTiSRtA9hN+mFcecu9iAeR6zI1rUqJEmXTKwBVhx0LBhvIsQZtvxlohiu0zYUYNNX12xjjJjjsi4= Received: by 10.141.142.15 with SMTP id u15mr4101843rvn.66.1204090195411; Tue, 26 Feb 2008 21:29:55 -0800 (PST) Received: by 10.141.128.7 with HTTP; Tue, 26 Feb 2008 21:29:55 -0800 (PST) Message-ID: <684362e10802262129w4fe41264p1eb75cadd5f4d259@mail.gmail.com> Date: Wed, 27 Feb 2008 11:29:55 +0600 From: "Markus Bertheau" To: "Scott Marlowe" Subject: Re: disabling an index without deleting it? Cc: "Tom Lane" , "Joshua D. Drake" , "Peter Koczan" , pgsql-performance In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com> <20080226130339.64bd4ec3@commandprompt.com> <9050.1204064560@sss.pgh.pa.us> <684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com> <13604.1204087729@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/367 X-Sequence-Number: 28909 2008/2/27, Scott Marlowe : > On Tue, Feb 26, 2008 at 10:48 PM, Tom Lane wrote: > > "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? > > > I see what you're saying. Sadly, my dreams of drop index concurrently > appear dashed. Maybe a different syntax: DROP INDEX DEFERRED, which will make the current transaction behave as if the index was dropped but not actually drop it until the end of the transaction. Inserts and updates of this and other transactions behave as if the index existed. On the other hand, if the only reason to have that feature is to plan and execute queries pretending that one index doesn't exist, then DROP INDEX DEFERRED is not the most straightforward syntax. Markus -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/