Re: Support for REINDEX CONCURRENTLY

On Thursday, October 04, 2012 04:51:29 AM Tom Lane wrote:
> Greg Stark <stark(at)mit(dot)edu> writes:
> > I'm a bit puzzled why we're so afraid of swapping the relfilenodes
> > when that's what the current REINDEX does.
>
> Swapping the relfilenodes is fine *as long as you have exclusive lock*.
> The trick is to make it safe without that. It will definitely not work
> to do that without exclusive lock, because at the instant you would try
> it, people will be accessing the new index (by OID).
I can understand hesitation around that.. I would like to make sure I
understand the problem correctly. When we get to the point where we switch
indexes we should be in the following state:
- both indexes are indisready
- old should be invalid
- new index should be valid
- have the same indcheckxmin
- be locked by us preventing anybody else from making changes

Lets assume we have index a_old(relfilenode 1) as the old index and a rebuilt
index a_new (relfilenode 2) as the one we just built. If we do it properly
nobody will have 'a' open for querying, just for modifications (its indisready)
as we had waited for everyone that could have seen a as valid to finish.

As far as I understand the code a session using a_new will also have built a
relcache entry for a_old.
Two problems:
* relying on the relcache to be built for both indexes seems hinky
* As the relcache is built with SnapshotNow it could read the old definition
for a_new and the new one for a_old (or the reverse) and thus end up with both
pointing to the same relfilenode. Which would be ungood.

Greetings,

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