Re: Online index builds

On mið, 2006-12-06 at 18:22 -0800, Jeff Davis wrote:
> On Wed, 2006-12-06 at 15:00 -0500, Chris Browne wrote:
> > Let me add another question to this; this might possibly be worthy of
> > a TODO for 8.3 or so...
> >
> > What if I wanted to:
> > ALTER TABLE distributors ADD PRIMARY KEY CONCURRENTLY (dist_id);

> Interesting, I was just thinking about this today as well. I am thinking
> it would be nice if we could:
>
> ALTER TABLE SET PRIMARY KEY INDEX foo_pkey;
>
> If it's already got a primary key we switch the primary key to be the
> new primary key
>
> (throwing an error if the columns don't match up to the
> existing primary key,

not sure what you mean by this

> or if it's not unique).

must also be NOT NULL

> If not, the primary key
> attribute is added to the existing index and the columns in the index
> now make up the primary key (throwing an error if the index is not
> unique).

What about existing foreign key constraints ?
as the only function of the PRIMARY key property of an
index is making it the default target of a foreign key
reference, you would have to decide what implications
this has. Possibly none, as I am not sure the foreign
key constraint remembers if the target was a primary key
or not.

also, your proposed syntax muddies the relationship
between the PRIMARY KEY constraint and the existence
of an INDEX. There is no such relationship in the SQL
standards.

possibly more appropriate would be

ALTER TABLE SET PRIMARY KEY (columns)
and an error issued if no UNIQUE NOT NULL index
is found on the relevant columns

one other question is what shuld happen to the original index that was
implicitly created. should it be dropped
automatically ?

gnari