> 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.
