Re: Patch to add a primary key using an existing index
От | Gurjeet Singh |
---|---|
Тема | Re: Patch to add a primary key using an existing index |
Дата | |
Msg-id | AANLkTi=s=SQy_HODUttfMm3nBc8gGT=gwFLYR9UhKffT@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Patch to add a primary key using an existing index (Gurjeet Singh <singh.gurjeet@gmail.com>) |
Ответы |
Re: Patch to add a primary key using an existing index
(Steve Singer <ssinger_pg@sympatico.ca>)
|
Список | pgsql-hackers |
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,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
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.gurjeet@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 <jim@nasby.net> wrote:UNIQUE constraints suffer from the same behavior; feel like fixing that too? :)> <add_pkey_with_index.patch><add_pkey_with_index.ignore_ws.patch>
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.gurjeet@{ gmail | yahoo }.com
> Twitter/Skype: singh_gurjeet
>
> Mail sent from my BlackLaptop device
> --
> 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 jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurjeet@{ 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.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Вложения
В списке pgsql-hackers по дате отправления: