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

On Tue, Nov 27, 2012 at 12:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.
>
For 9.2 as you say the best choice is to ignore in RelationGetIndexList the
indexes that are valid and not ready when fetching the index list of a
relation.

For the fix in master, just thinking but...
Having 3 boolean flags to manage the state of an index might easily lead to
the developer to confusion.
I was thinking about the possibility of using instead of that a list of
states that are defined with a character.
We already know 3 possible states which are:
- indisvalid = false, indisready = false, let's say INDEX_IS_INITIAL 'i'
- indisvalid = false, indisready = true, with INDEX_IS_READY 'r'
- indisvalid = true, indisready = true, with INDEX_IS_VALID 'v'

In case an index needs to be ignored as you mention with the combination
indisvalid = true and indisready = false, it could be possible to add a new
state like INDEX_IS_IGNORE 'g'.

This would avoid the addition of a new column in pg_index and control the
status of an index easily.
This is not that much backward-compatible though...
--
Michael Paquier
http://michael.otacoo.com