Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id EAAEE2E0058 for ; Wed, 27 Feb 2008 19:01:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 61218-01-5 for ; Wed, 27 Feb 2008 19:01:10 -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 4CBBF2E008A for ; Wed, 27 Feb 2008 19:01:03 -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 m1RN0HrK010386; Wed, 27 Feb 2008 18:00:17 -0500 (EST) To: "Kevin Grittner" cc: "Markus Bertheau" , "Joshua D. Drake" , "Peter Koczan" , "Scott Marlowe" , "pgsql-performance" Subject: Re: disabling an index without deleting it? In-reply-to: <47C578A1.EE98.0025.0@wicourts.gov> References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com> <20080226130339.64bd4ec3@commandprompt.com> <9050.1204064560@sss.pgh.pa.us> <684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com><684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com> <13604.1204087729@sss.pgh.pa.us> <47C578A1.EE98.0025.0@wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Wed, 27 Feb 2008 14:50:10 -0600" Date: Wed, 27 Feb 2008 18:00:17 -0500 Message-ID: <10385.1204153217@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/385 X-Sequence-Number: 28927 "Kevin Grittner" writes: > Out of curiosity, couldn't any transaction using a snapshot prior to > the commit of the DROP continue to use it (just like an uncommited > DELETE of a row)? The transaction doing the DROP wouldn't maintain > it for modifications, which is fine whether it is committed or > rolled back. There would just be the matter of "vacuuming" the > index out of physical existence once all transactions which could > see it are gone. You can't just lazily remove the index after the last xact stops using it; there has to be an agreed synchronization point among all the transactions. Otherwise you could have xact A expecting the index to contain entries from the already-committed xact B, but B thought the index was dead and didn't bother updating it. We might be able to do something that would shorten the length of time that the exclusive lock is held, but AFAICS we couldn't eliminate it altogether; and I'm unconvinced that merely shortening the interval is worth much extra complexity. In the particular case at hand, a planner hook to make it ignore the index is a far better solution anyway... regards, tom lane