Patch to add a primary key using an existing index

This is a continuation from this thread:
http://archives.postgresql.org/pgsql-hackers/2010-09/msg02153.php

The attached patch allows creating a primary key using an existing index.

This capability would be helpful in situations where one wishes to
rebuild/reindex the primary key, but associated downtime is not desirable.
It also allows one to create a table and start using it, while creating a
unique index 'concurrently' and later adding the primary key using the
concurrently built index. Maybe pg_dump can also use it.

The command syntax is:

ALTER TABLE sometable ADD PRIMARY KEY( col1, col2 ) WITH ( INDEX =
'indexname' );

A typical use case:

CREATE INDEX CONCURRENTLY new_pkey_idx ON sometable( a, b );

ALTER TABLE sometable ADD PRIMARY KEY ( a, b ) WITH (INDEX = 'new_pkey_idx'
);

- OR -

ALTER TABLE sometable DROP CONSTRAINT sometable_pkey,
ADD PRIMARY KEY ( a, b ) WITH (INDEX = 'new_pkey_idx' );

Notes for the reviewers:
------------------------

Don't be scared by the size of changes to index.c :) These are mostly
indentation diffs. I have attached two versions of the patch: one is context
diff, and the other is the same except ignoring whitespace changes.

The pseudocode is as follows:

In ATExecAddIndex()
If this ALTER command specifies a PRIMARY KEY
Call get_pkey_index_oid() to perform checks.

In get_pkey_index_oid()
Look for the WITH INDEX option
Reject
if more than one WITH INDEX clause specified
if the index doesn't exist or not found in table's schema
if the index is associated with any CONSTRAINT
if index is not ready or not valid (CONCURRENT buiild? Canceled
CONCURRENT?)
if index is on some other table
if index is not unique
if index is an expression index
if index is a partial index
if index columns do not match the PRIMARY KEY clause in the command
if index is not B-tree
If PRIMARY KEY clause doesn't have a constraint name, assign it one.
(code comments explain why)
Rename the index to match constraint name in the PRIMARY KEY clause

Back in ATExecAddIndex()
Use the index OID returned by get_pkey_index_oid() to tell DefineIndex()
to not create index.
Now mark the index as having 'indisprimary' flag.

In DefineIndex() and index_create() APIs
pass an additional flag: index_exists
Skip various actions based on this flag.

The patch contains a few tests, and doesn't yet have a docs patch.

The development branch is at
http://github.com/gurjeet/postgres/tree/replace_pkey_index

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device