Re: Concurrently option for reindexdb

On Mon, Sep 1, 2014 at 10:43 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 08/25/2014 02:36 PM, Sawada Masahiko wrote:
>> Hi all,
>>
>> Attached WIP patch adds "-C (--concurrently)" option for reindexdb
>> command for concurrently reindexing.
>> If we specify "-C" option with any table then reindexdb do reindexing
>> concurrently with minimum lock necessary.
>> Note that we cannot use '-s' option (for system catalog) and '-C'
>> option at the same time.
>> This patch use simple method as follows.
>>
>> 1. Do "CREATE INDEX CONCURRENTLY" new index which has same definition
>> as target index
>> 2. Aquire ACCESS EXCLUSIVE LOCK to target table( and transaction starts)
>> 3. Swap old and new index
>> 4. Drop old index
>> 5. COMMIT
>
> How do you handle indexes tied to constraints - PRIMARY KEY, UNIQUE, or
> EXCLUSION constraint indexes?
>
> My understanding was that this currently required some less than lovely
> catalog hacks.
>

The currently patch dose not hack catalog, just create new index
concurrently and
swap them.
So, It is supporting only UNIQUE index, I think.
This patch contains some limitation.
Also I'm thinking to implement to handle these cases.

Regards,

-------
Sawada Masahiko