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 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