Re: Support for REINDEX CONCURRENTLY

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

> On Wed, Mar 6, 2013 at 8:59 PM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)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