Re: Online index builds

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.

>
> > > or if it's not unique).
> >
> > must also be NOT NULL
>
> Indexes can't be NOT NULL; NOT NULL is a constraint.

Sorry, I got confused by the UNIQUE in the create index syntax:

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

> > ...
> > 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.
>
> Doesn't matter. Foreign keys don't reference an index, they reference a
> set of attributes. I am just trying to provide an ability to change the
> underlying unique index that is used to implement the unique constraint
> that is necessary for all primary keys.

I was still imagining here that you would want a
different set of attributes froyour primary key.

gnari