Re: Patch to add a primary key using an existing index - Mailing list pgsql-hackers

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