Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 978EC2E0052 for ; Wed, 27 Feb 2008 19:19:03 -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 70479-06 for ; Wed, 27 Feb 2008 19:18:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 7C6DA2E005C for ; Wed, 27 Feb 2008 19:18:57 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 27 Feb 2008 17:18:56 -0600 Message-Id: <47C59B79.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Wed, 27 Feb 2008 17:18:50 -0600 From: "Kevin Grittner" To: "Tom Lane" Cc: "Joshua D. Drake" , "Peter Koczan" , "Scott Marlowe" , "Markus Bertheau" , "pgsql-performance" Subject: Re: disabling an index without deleting it? 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><47C578A1.EE98.0025.0@wicourts.gov> <10385.1204153217@sss.pgh.pa.us> In-Reply-To: <10385.1204153217@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/386 X-Sequence-Number: 28928 >>> On Wed, Feb 27, 2008 at 5:00 PM, in message <10385.1204153217@sss.pgh.= pa.us>, Tom Lane wrote:=20 > "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. >=20 > 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. =20 If xact A is using a snapshot from before the commit of the index DROP, it shouldn't see anything done after the drop anyway. If it's using a snapshot from after the DROP, it won't see the index. xact B would only fail to update the index if it was using a snapshot after the drop, so I'm having trouble grasping the sequence of events where this is a problem. Could you outline the series of events where the problem occurs? =20 > In the particular case at hand, a planner hook to make it ignore the > index is a far better solution anyway... =20 Agreed -- I was just curious whether we could eliminate a source of blocking raised in the discussion; and failing that, improve my grasp of the PostgreSQL MVCC implementation. =20 -Kevin =20