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 <j...@nasby.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.gurj...@{ 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@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > > -- > Jim C. Nasby, Database Architect j...@nasby.net > 512.569.9461 (cell) http://jim.nasby.net > > > -- gurjeet.singh @ EnterpriseDB - The Enterprise Postgres Company http://www.EnterpriseDB.com singh.gurj...@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device
