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

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