Re: disabling an index without deleting it?

>>> On Wed, Feb 27, 2008 at  5:00 PM, in message <10385.1204153217@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.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