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

On Sun, Dec 5, 2010 at 2:09 PM, Peter Eisentraut <peter_e(at)gmx(dot)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(at)xzilla(dot)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(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device