Re: Support for REINDEX CONCURRENTLY

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


+    The recommended recovery method in such cases is to drop the concurrent
+    index and try again to perform 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
informataion should be
documented.

Regards,

-- 
Fujii Masao