Re: Bugs in CREATE/DROP INDEX CONCURRENTLY

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2012-11-28 18:41:39 -0500, Tom Lane wrote:
>> However, this is more complicated and harder to understand. So unless
>> somebody is really excited about being able to tell the difference
>> between create-in-progress and drop-in-progress, I'd rather leave the
>> patch as-is.

> The only real argument for doing this that I can see is a potential
> REINDEX CONCURRENTLY.

While I was working on this patch, I came to the conclusion that the
only way REINDEX CONCURRENTLY could possibly work is:

1. Do CREATE INDEX CONCURRENTLY with a temporary index name.

2. Swap index names and any dependencies (eg for unique/pkey
constraints), in a transaction of its own.

3. Do DROP INDEX CONCURRENTLY on the now-obsolete index.

If you try to do it with just one set of index catalog entries, you'll
find the pg_class row has to be in two states at once, since there
certainly have to be two underlying physical files while all this is
going on. That being the case, there'll be two different pg_index rows
as well, and thus my worries upthread about whether REINDEX CONCURRENTLY
would need to do something special with the pg_index row seem unfounded.

Of course, there's still plenty of magic required to make this happen
--- I don't see how to do step 2 safely without taking exclusive lock
for at least a short interval. But that's mostly about the SnapshotNow
scan problem, which we at least have some ideas about how to solve.

regards, tom lane