Skip site navigation (1) Skip section navigation (2)

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

From: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
To: PGSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Robert Haas <robertmhaas(at)gmail(dot)com>, r t <pgsql(at)xzilla(dot)net>, Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>, Steve Singer <ssinger(at)ca(dot)afilias(dot)info>, Steve Singer <ssinger_pg(at)sympatico(dot)ca>
Subject: Re: Patch to add a primary key using an existing index
Date: 2011-01-06 21:28:19
Message-ID: AANLkTi=LAeRBnGgyy5UZmrHtxscuqmwhSeUY7wHLfwb8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Dec 9, 2010 at 2:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
> > But I still hold a bias towards renaming the index to match constraint
> name
> > (with a NOTICE), rather than require that the constraint name match the
> > index name, because the constraint name is optional and when it is not
> > provided system has to generate a name and we have to rename the index
> > anyway to maintain consistency.
>
> No.  If the constraint name is not specified, we should certainly use
> the existing index name, not randomly rename it.
>
>
Attached is the updated patch with doc changes and test cases. An overview
of the patch is in order:

The new command syntax is

ALTER TABLE table_name
  ADD [CONSTRAINT constraint_name]
    PRIMARY KEY USING INDEX index_name
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE
];

ALTER TABLE table_name
  ADD [CONSTRAINT constraint_name]
    UNIQUE      USING INDEX index_name
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE
];

The index should be a unique index, and it should not be an expressional or
partial index. The included test cases exercise a few other cases.

If the constraint name is provided, then index is renamed to that with a
NOTICE, else the index name is used as the constraint name.

I have consciously disallowed the ability to specify storage_parameters
using the WITH clause, if somebody thinks it is wise to allow that and is
needed, I can do that.

Git branch: https://github.com/gurjeet/postgres/tree/constraint_with_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

Attachment: constraint_using_index.patch.gz
Description: application/x-gzip (8.9 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2011-01-06 21:30:50
Subject: Re: DISCARD ALL ; stored procedures
Previous:From: Merlin MoncureDate: 2011-01-06 21:21:36
Subject: Re: DISCARD ALL ; stored procedures

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group