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

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
> On Tue, Jan 25, 2011 at 9:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> One other issue that might be worthy of discussion is that as things
>> stand, execution of the ADD CONSTRAINT USING INDEX syntax will cause
>> the constraint to absorb the index as an INTERNAL dependency. That
>> means dropping the constraint would make the index go away silently ---
>> it no longer has any separate life.

> Since we rename the index automatically to match the constraint name,
> implying that the index now belongs to the system, I think the user should
> expect the index to go away with the constraint; else we have to remember
> index's original name and restore that name on DROP CONSTRAINT, which IMHO
> will be even more unintuitive.

Yeah, that's a good point. Also, the documented example usage of this
feature is

To recreate a primary key constraint, without blocking updates while the
index is rebuilt:
<programlisting>
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx on distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
</programlisting>

with the implication that after you do that, the installed index is
exactly like you would have gotten from straight ADD PRIMARY KEY.
If there's something funny about it, then it's not just a replacement.

In the end I think this is mainly an issue of setting appropriate
expectations in the documentation. I've added the following text to
the ALTER TABLE manual page:

<para>
After this command is executed, the index is <quote>owned</> by the
constraint, in the same way as if the index had been built by
a regular <literal>ADD PRIMARY KEY</> or <literal>ADD UNIQUE</>
command. In particular, dropping the constraint will make the index
disappear too.
</para>

regards, tom lane