Re: Bugs in CREATE/DROP INDEX CONCURRENTLY

On 2012/11/29, at 9:23, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> On 2012-11-28 19:11:46 -0500, Tom Lane wrote:
>> 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.
>
> That's actually pretty similar to the way Michael has implemented it in
> his submitted patch and what has been discussed in a recent thread. His
> patch doesn't claim to solve the concurrency issues around 2) though...
Correct, that is the same approach.
The patch took as approach to create a completely separate and new index entry which is a clone of the former index. This way all the entries are in catalogs are doubled, and the switch of the names is made while the two indexes are valid, but yes, I am myself wondering about the necessary lock that needs to be taken when switching the 2 index names. By the way, just by knowing that, I would agree to first rework the SnapshotNow mechanisms that would make a far better base for concurrent DDLs, and this is not limited to REINDEX only, but other things like CLUSTER, ALTER TABLE and perhaps others.

Then once this is done PG will have better prospectives with features using CONCURRENTLY, and we could envisage a clean implementation for REINDEX CONCURRENTLY,

Regards,

Michael Paquier