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

On Sat, Dec 4, 2010 at 6:48 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Dec 4, 2010, at 1:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Ross J. Reedstrom" <reedstrm(at)rice(dot)edu> writes:
> >> If you consider that an index basically is, in some sense, a pre-canned
> >> column list, then:
> >
> >> ALTER TABLE table_name ADD PRIMARY KEY (column_list);
> >> ALTER TABLE table_name ADD PRIMARY KEY USING index_name;
> >
> >> are parallel constructions. And it avoids the error case of the user
> >> providing a column list that doesn't match the index.
> >
> > +1 for that approach. One other thought is that ordinarily, the
> > add-constraint syntax ensures that the constraint is named the same as
> > its underlying index; in fact we go so far as to keep them in sync if
> > you rename the index later. But after
> >
> > ALTER TABLE table_name ADD CONSTRAINT con_name PRIMARY KEY USING
> index_name;
> >
> > they'd be named differently, unless we (a) throw an error or (b)
> > forcibly rename the index. Neither of those ideas seems to satisfy the
> > principle of least surprise, but leaving it alone seems like it will
> > also lead to confusion later.
>
> I think that might be the best way though.
>

Haas, are you promoting to leave them different? I'd be comfortable with
that.

I'd also be comfortable with B (renaming with notice, similar to the notice
when creating a constraint). Given we rename the constraint when we rename
the index, I would not find the reverse behavior terribly surprising.

Actually I think I'd even be comfortable with A, either you must name the
constraint after the index, or you can leave the constraint name out, and
we'll use the index name.

>
> > I wonder whether, in the same spirit as not letting the user write a
> > column name list that might not match, we should pick a syntax that
> > doesn't allow specifying a constraint name different from the index
> > name. In the case where you say CONSTRAINT it'd be a bit plausible
> > to write something like
> >
> > ALTER TABLE table_name ADD CONSTRAINT con_name PRIMARY KEY USING EXISTING
> INDEX;
> >
> > (implying that the index to use is named con_name) but I don't know
> > what to do if you want to leave off the "CONSTRAINT name" clause.
>
> Because this seems plain weird.
>
>
+1

Robert Treat
play: http://www.xzilla.net
work: http://www.omniti.com/is/hiring