Re: Online index builds

> Yeah, we could add defenses one by one for the cases we could think of,
> but I'd never feel very secure that we'd covered them all.

What you all forget in this discussion is that reindexing concurrently
would have to be a highly administrative task, controlled by the DB
admin... so whoever has a big index to be reindexed can schedule it so
that no other schema changes occur to the table until the reindex is
finished.

So an implementation which optimistically builds the new index
concurrently while holding no lock, and then hopes for the 3rd
transaction to be able to get the exclusive lock and be able to swap the
new index in the place of the old index, and error out if it can't - it
is perfectly acceptable. The waisted effort when dropping the newly
created index on error is easily avoidable by not doing anything which
would cause an error in that phase... and it is easily controlled by the
DBA. The only thing needed is documentation to point it out.

I didn't understand completely the discussion here, and if there are
some problems detecting the error conditions in the index swap phase,
that's a problem... but if it is possible to reliably detect cases where
the swap is not possible because something changed in between, erroring
out will be acceptable for the purpose of this command...

Cheers,
Csaba.