Re: Support for REINDEX CONCURRENTLY

On Thu, Mar 7, 2013 at 2:09 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

> On Wed, Mar 6, 2013 at 8:59 PM, Michael Paquier
> <michael.paquier@gmail.com> wrote:
> > OK. Patches updated... Please see attached.
>
> I found odd behavior. After I made REINDEX CONCURRENTLY fail twice,
> I found that the index which was not marked as INVALID remained
> unexpectedly.
>
> =# CREATE TABLE hoge (i int primary key);
> CREATE TABLE
> =# INSERT INTO hoge VALUES (generate_series(1,10));
> INSERT 0 10
> =# SET statement_timeout TO '1s';
> SET
> =# REINDEX TABLE CONCURRENTLY hoge;
> ERROR:  canceling statement due to statement timeout
> =# \d hoge
>      Table "public.hoge"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  i      | integer | not null
> Indexes:
>     "hoge_pkey" PRIMARY KEY, btree (i)
>     "hoge_pkey_cct" PRIMARY KEY, btree (i) INVALID
>
> =# REINDEX TABLE CONCURRENTLY hoge;
> ERROR:  canceling statement due to statement timeout
> =# \d hoge
>      Table "public.hoge"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  i      | integer | not null
> Indexes:
>     "hoge_pkey" PRIMARY KEY, btree (i)
>     "hoge_pkey_cct" PRIMARY KEY, btree (i) INVALID
>     "hoge_pkey_cct1" PRIMARY KEY, btree (i) INVALID
>     "hoge_pkey_cct_cct" PRIMARY KEY, btree (i)
>
Invalid indexes cannot be reindexed concurrently and are simply bypassed
during process, so _cct_cct has no reason to exist. For example here is
what I get with a relation having an invalid index:
ioltas=# \d aa
      Table "public.aa"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Indexes:
    "aap" btree (a)
    "aap_cct" btree (a) INVALID

ioltas=# reindex table concurrently aa;
WARNING:  cannot reindex concurrently invalid index "public.aap_cct",
skipping
REINDEX


> +    The recommended recovery method in such cases is to drop the
> concurrent
> +    index and try again to perform <command>REINDEX CONCURRENTLY</>.
>
> If an invalid index depends on the constraint like primary key, "drop
> the concurrent
> index" cannot actually drop the index. In this case, you need to issue
> "alter table
> ... drop constraint ..." to recover the situation. I think this
> information should be
> documented.
>
You are right. I'll add a note in the documentation about that. Personally
I find it more instinctive to use DROP CONSTRAINT for a primary key as the
image I have of a concurrent index is a twin of the index it rebuilds.
-- 
Michael