Re: Online index builds

On Thu, 2006-12-07 at 20:07 +0000, Ragnar wrote:
> On fim, 2006-12-07 at 09:27 -0800, Jeff Davis wrote:
> > On Thu, 2006-12-07 at 12:26 +0000, Ragnar wrote:
> > > On mið, 2006-12-06 at 18:22 -0800, Jeff Davis wrote:
> > >
> > > > 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
> >
> > In my suggestion, if the table already has a primary key, then you can
> > only set the primary key index to be an index with exactly the same
> > columns as the existing primary key index.
>
> Why would you do that?
>
> I saw the use-case of when you have a primary key and a
> surrogate key , and decided you wanted the surrogate key to be the
> primary key after all, maybe because the
> natural key you had used turned out not to be a good
> candidate.
>

You've got a valid use-case, but it's completely different from the one
I suggested. I wanted to be able to build an index concurrently (with
the new functionality in 8.2) and then switch the primary key to use
that new index, and then drop the old index.

The reason is because that allows a 0-downtime index rebuild on a
primary key's index without losing it's primary key status.

I think all you need to do what you want is something like:
ALTER TABLE foo DROP CONSTRAINT foo_pkey KEEP INDEX;

Because then you could drop the primary key status on a column without
affecting the column or the index, then use my suggested syntax to
switch the primary key status to a different index like so:
ALTER TABLE foo SET PRIMARY KEY INDEX foo_othercolumn_index;

Regards,
Jeff Davis