Skip site navigation (1) Skip section navigation (2)

Patch to add a primary key using an existing index

From: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
To: PGSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Patch to add a primary key using an existing index
Date: 2010-10-09 18:07:15
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
This is a continuation from this thread:

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

@ EnterpriseDB - The Enterprise Postgres Company

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

Mail sent from my BlackLaptop device

Attachment: add_pkey_with_index.patch
Description: text/x-diff (25.9 KB)
Attachment: add_pkey_with_index.ignore_ws.patch
Description: text/x-diff (20.4 KB)


pgsql-hackers by date

Next:From: Gurjeet SinghDate: 2010-10-09 18:19:26
Subject: Re: Patch to add a primary key using an existing index
Previous:From: Joe ConwayDate: 2010-10-09 15:26:25
Subject: Re: getting set up on git (finally)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group