Re: Online index builds

On fim, 2006-12-07 at 13:57 -0800, Jeff Davis wrote:
> 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;
> > > > >
>
> 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.

my point was just that 'primary key' is really just
a property of a set of attributes, and it is just
incidental that postgres enforces this property
with an index.

so if if a ALTER TABLE SET PRIMARY KEY is implemented,
it should involve a set of attributes, but not an index.

in your use case, the ALTER should not really be needed.
lets say you have PRIMARY KEY (a,b) on some table.
you decide you want to rebuild the primary key concurrently. just build
a new index on (a,b).
if you then drop the old index, the primary key constraint can still be
enforced by the new index, so
the DROP should be allowed to proceed, without affecting
the constraint.

on the other hand, the PRIMARY KEY property is really
only there because the standards say so, but does not
have a great value in my opinion, so the ability to
alter it would not be high on my priority lists.

gnari