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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
