Re: Patch to add a primary key using an existing index

Depesz brought that to my attention a few days after the initial submission,
and adding support for UNIQUE was not much pain. I implemented it almost
immediately, but didn't announce it as I was hoping I could submit some doc
changes too with that.

If you are the adventurous kind, you can follow the Git branch here:
https://github.com/gurjeet/postgres/tree/replace_pkey_index

Regards,

On Mon, Nov 1, 2010 at 10:29 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:

> UNIQUE constraints suffer from the same behavior; feel like fixing that
> too? :)
>
> On Oct 9, 2010, at 1:07 PM, Gurjeet Singh wrote:
>
> > 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
> > <add_pkey_with_index.patch><add_pkey_with_index.ignore_ws.patch>
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
>
> --
> Jim C. Nasby, Database Architect jim(at)nasby(dot)net
> 512.569.9461 (cell) http://jim.nasby.net
>
>
>

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