Attached is the patch that extends the same feature for UNIQUE indexes. It also includes some doc changes for the ALTER TABLE command, but I could not verify the resulting changes since I don't have the doc-building infrastructure installed.
Regards, On Mon, Nov 8, 2010 at 1:39 AM, Gurjeet Singh <singh.gurj...@gmail.com>wrote: > 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 > -- 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
replace_pkey_index.uniq+doc.patch.gz
Description: GNU Zip compressed data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
