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

On Sun, Dec 5, 2010 at 2:09 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

> On fre, 2010-12-03 at 15:27 -0500, Robert Haas wrote:
> > On Fri, Dec 3, 2010 at 2:56 PM, r t <pgsql@xzilla.net> wrote:
> > > What exactly was the objection to the following -->
> > > ALTER TABLE table_name ADD PRIMARY KEY (column_list) USING index_name;
> > > Is the objection that you might have been trying to specify a
> constraint
> > > named "using" ? I'm willing to make that option more difficult. :-)
> >
> > I think it's that someone might expect the word after USING to be the
> > name of an index AM.
>
> That could be avoided by writing
>
> USING INDEX <name>
>
>
Allowing USING INDEX along with USING INDEX TABLESPACE is causing
shift/reduce conflicts.

I liked the proposal upthread of providing alternate syntax where user does
not have to specify column-list and system picks up that list from the
index.

ALTER TABLE table_name ADD [CONSTRAINT cons_name] PRIMARY KEY (column_list)
[WITH (...)] [USING INDEX TABLESPACE tblspcname];
ALTER TABLE table_name ADD [CONSTRAINT cons_name] PRIMARY KEY [WITH (...)]
[USING INDEX index_name];

This would also help avoid the bug that Itagaki found, where the user wants
to use an existing index, and also specifies USING INDEX TABLESPACE.

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.

Following are the gram.y changes that I am going to start with:

 %type <boolean> constraints_set_mode
-%type <str>        OptTableSpace OptConsTableSpace OptTableSpaceOwner
+%type <str>        OptTableSpace OptConsTableSpace OptConsIndex
OptTableSpaceOwner
 %type <list>   opt_check_option

[...]
            | UNIQUE '(' columnList ')' opt_definition OptConsTableSpace
                ConstraintAttributeSpec
                {
                    Constraint *n = makeNode(Constraint);
                    n->contype = CONSTR_UNIQUE;
                    n->location = @1;
                    n->keys = $3;
                    n->options = $5;
                    n->indexspace = $6;
                    n->deferrable = ($7 & 1) != 0;
                    n->initdeferred = ($7 & 2) != 0;
                    $$ = (Node *)n;
                }
+           | UNIQUE opt_definition OptConsIndex ConstraintAttributeSpec
+               {
+                   Constraint *n = makeNode(Constraint);
+                   n->contype = CONSTR_UNIQUE;
+                   n->location = @1;
+                   n->options = $2;
+                   n->indexname = $3;
+                   n->deferrable = ($4 & 1) != 0;
+                   n->initdeferred = ($4 & 2) != 0;
+                   $$ = (Node *)n;
+               }
            | PRIMARY KEY '(' columnList ')' opt_definition
OptConsTableSpace
                ConstraintAttributeSpec
                {
                    Constraint *n = makeNode(Constraint);
                    n->contype = CONSTR_PRIMARY;
                    n->location = @1;
                    n->keys = $4;
                    n->options = $6;
                    n->indexspace = $7;
                    n->deferrable = ($8 & 1) != 0;
                    n->initdeferred = ($8 & 2) != 0;
                    $$ = (Node *)n;
                }
+           | PRIMARY KEY opt_definition OptConsIndex
ConstraintAttributeSpec
+               {
+                   Constraint *n = makeNode(Constraint);
+                   n->contype = CONSTR_PRIMARY;
+                   n->location = @1;
+                   n->options = $3;
+                   n->indexname = $4;
+                   n->deferrable = ($5 & 1) != 0;
+                   n->initdeferred = ($5 & 2) != 0;
+                   $$ = (Node *)n;
+               }
            | EXCLUDE access_method_clause '(' ExclusionConstraintList ')'

[...]
 OptConsTableSpace:   USING INDEX TABLESPACE name   { $$ = $4; }
            | /*EMPTY*/                             { $$ = NULL; }
        ;

+OptConsIndex:   USING INDEX name   { $$ = $3; }
+           | /*EMPTY*/             { $$ = NULL; }
+       ;
+

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