Re: Support for REINDEX CONCURRENTLY

On 2013-03-07 02:09:49 +0900, Fujii Masao 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.

Thats to be expected. Indexes need to be valid *before* we can drop the
old one. So if you abort in the right moment you will see those and
thats imo fine.
 
> =# 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)

Huh, why did that go through? It should have errored out?
 
> +    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
> informataion should be
> documented.

I think we just shouldn't set ->isprimary on the temporary indexes. Now
we switch only the relfilenodes and not the whole index, that should be
perfectly fine.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services