Patch to add a primary key using an existing index - Mailing list pgsql-hackers

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.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device