Patch to reindex primary keys

    Attached is a patch that implements replacing a primary key with another
index. This would help overcome the limitation that primary keys cannot be reindexed
without taking exclusive locks.

    The use case is to create an identical index, concurrenlty, with the same
structure as the primary key, and then use this feature to atomically replace
the primary key's underlying index.

    Before I dive into the internals, here's what this patch enables Postgres to do:

</snip>
postgres=# create table mytable( a int primary key );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytable_pkey" for table "mytable"
CREATE TABLE
postgres=# insert into mytable select s from generate_series( 1, 100 ) as s;
INSERT 0 100
postgres=# create unique index concurrently mysecond_key on mytable( a );
CREATE INDEX
postgres=#
postgres=# \d mytable
    Table "public.mytable"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (a)
    "mysecond_key" UNIQUE, btree (a)

postgres=#
postgres=# begin;
BEGIN
postgres=# alter table mytable drop constraint mytable_pkey;
ALTER TABLE
postgres=# alter table mytable add primary key (a) with (index = 'mysecond_key' );
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "mysecond_key" for table "mytable"
ALTER TABLE
postgres=# commit
postgres-# ;
COMMIT
postgres=# \d mytable
    Table "public.mytable"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
Indexes:
    "mysecond_key" PRIMARY KEY, btree (a)

</snip>

    Internally, this patch this patch drops current primary key constraintm, if any,
(currently not working, but rest of the feature is still usable), and then
creates a new constraint with the given index.

    Here's the pseudocode I started with:

    Check if cxt->pkey->options has a 'WITH INDEX' element
        take an exclusive lock on that index

        Does this table have a primary key
            check if index mentioned in cxt->pkey matches that PKEY definition,
                Does column list match
                Do the opclasses match
                Does index type match (BTree for now)
                Do they have the same owner

        Append a new command to newcmds to drop the PKey constraint
            use 'rel' variable to get primary key's OID ( modify and reuse relationHasPrimaryKey() )
            use relation_open() to get pkey's relation
            use the returned Relation->rd_rel->relname to build DROP CONSTRAINT command
            set missingok member of the command so that this would work even if there was already a DROP CONSTRAINT for the PKey.
            push this command to newcmds

        Chenge the 'WITH INDEX' element, and replace index name in Value* to have decimal representation of index's OID.
            This will be used by ATExecAddIndex().

           
    The patch is based on REl9_0_STABLE from a few days ago. It is a bit hackish,
and modifies the couple of internal APIs to get the work done. I still have a few
TODO items in there, but wanted to throw this patch out there to get a few
eyeballs on it while I traveled.

PS: I am (going to bed and then traveling) for the next 20 hours or so, so will
    not be able to respond to emails until then.

I dedicate this work to

my dear brother-in-law, Sandeep Singh
and my dear friend, Mandeep Singh Sethi

Good men... you will be always in our hearts. RIP.
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device