Re: disabling an index without deleting it?

["Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>]

>>> On Wed, Feb 27, 2008 at  5:00 PM, in message <10385(dot)1204153217(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: 
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> 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.
 
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?
 
> In the particular case at hand, a planner hook to make it ignore the
> index is a far better solution anyway...
 
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.
 
-Kevin