Re: Re: Problem Observed in behavior of Create Index Concurrently and Hot Update

On 2012-11-26 22:31:50 -0500, Tom Lane wrote:
> I wrote:
> > I'm a bit inclined to think that DROP INDEX CONCURRENTLY should have an
> > additional step that somehow marks the pg_index row in a new way that
> > makes RelationGetIndexList ignore it, and then wait out existing
> > transactions before taking the final step of dropping the index. The
> > Right Way to do this would likely have been to add another bool column,
> > but we don't have that option anymore in 9.2. Maybe we could abuse
> > indcheckxmin somehow, ie use a state that can't otherwise occur (in
> > combination with the values of indisvalid/indisready) to denote an index
> > being dropped.
>
> I looked into this some more. There are currently three possible states
> of the indisvalid/indisready flags:
>
> indisvalid = false, indisready = false
>
> initial state during CREATE INDEX CONCURRENTLY; this should
> result in sessions honoring the index for HOT-safety decisions,
> but not using it for searches or insertions
>
> indisvalid = false, indisready = true
>
> sessions should now insert into the index, but not use it
> for searches
>
> indisvalid = true, indisready = true
>
> normal, fully valid index
>
> Either state of indcheckxmin is valid with all three of these
> combinations, so the specific kluge I was contemplating above doesn't
> work. But there is no valid reason for an index to be in this state:
>
> indisvalid = true, indisready = false
>
> I suggest that to fix this for 9.2, we could abuse these flags by
> defining that combination as meaning "ignore this index completely",
> and having DROP INDEX CONCURRENTLY set this state during its final
> wait before removing the index.
>
> Obviously, an additional flag column would be a much cleaner fix,
> and I think we should add one in HEAD. But it's too late to do
> that in 9.2.

While I aggree that more states would make some stuff cleaner, as long
as were still locking entries in RelationGetIndexAttrBitmap that have
indisvalid = false, indisready = false we still have broken DROP INDEX
CONCURRENTLY due to the exlusive lock acquired during actually dropping
the index. Which can take quite a while on a big index.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services