Re: Support for REINDEX CONCURRENTLY

On 2012-12-10 15:03:59 +0900, Michael Paquier wrote:
> I have updated the patch (v4) to take care of updating reltoastidxid for
> toast parent relations at the swap step by using index_update_stats. In
> prior versions of the patch this was done when concurrent index was built,
> leading to toast relations using invalid indexes if there was a failure
> before the swap phase. The update of reltoastidxids of toast relation is
> done with RowExclusiveLock.
> I also added a couple of tests in src/test/isolation. Btw, as for the time
> being the swap step uses AccessExclusiveLock to switch old and new
> relnames, it does not have any meaning to run them...

Btw, as an example of the problems caused by renaming:

postgres=# CREATE TABLE a (id serial primary key); CREATE TABLE b(id
serial primary key, a_id int REFERENCES a);
CREATE TABLE
Time: 137.840 ms
CREATE TABLE
Time: 143.500 ms
postgres=# \d b                        Table "public.b"Column |  Type   |                   Modifiers
--------+---------+------------------------------------------------id     | integer | not null default
nextval('b_id_seq'::regclass)a_id  | integer |
 
Indexes:   "b_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:   "b_a_id_fkey" FOREIGN KEY (a_id) REFERENCES a(id)

postgres=# REINDEX TABLE a CONCURRENTLY;
NOTICE:  drop cascades to constraint b_a_id_fkey on table b
REINDEX
Time: 248.992 ms
postgres=# \d b                        Table "public.b"Column |  Type   |                   Modifiers
--------+---------+------------------------------------------------id     | integer | not null default
nextval('b_id_seq'::regclass)a_id  | integer |
 
Indexes:   "b_pkey" PRIMARY KEY, btree (id)


Looking at the patch for a bit now.

Regards,

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