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

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(dot)gurjeet(at)gmail(dot)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 <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
>

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