Re: Online index builds

On Tue, 2006-12-12 at 18:40 -0500, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > My point was that, because we can run it in multiple transactions, can't
> > we drop the nonexclusive lock before acquiring the exclusive lock,
>
> No. What happens if someone renames the table out from under you, to
> mention just one possibility? If you've been holding nonexclusive lock
> for a long time (as you would've been) there's a nontrivial chance that
> someone is already queued up for an exclusive lock and will get in
> before you do.
>

I'm trying to understand what would actually happen. I assume you mean
change the name of the index, because after we create the index
concurrently, it doesn't matter what the table name is.

(1) We create the new index concurrently
(2) someone gets an exclusive lock before we do, and they rename the old
index (foo_pkey is now known as bar_pkey).
(3) We don't find the index, throw an error, and have an extra index
hanging around. Same for any other situation that makes us unable to
continue in a well-defined way.

Even if we deleted the extra index on step 3, we could consider that
reasonable behavior because the user went out of their way to rename an
index with a concurrent REINDEX. They could then try again, albeit with
some wasted effort.

Even thinking about strange edge cases, like if they decide to use their
exclusive lock to swap the names of two indexes in step 2, we could
probably detect whether it was the same old index or not; perhaps by
remembering the relfilenode of the index we're REINDEXing.

Regards,
Jeff Davis