Re: Online index builds

On Thu, 2006-12-07 at 12:26 +0000, Ragnar wrote:
> 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

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.

> > or if it's not unique).
>
> must also be NOT NULL

Indexes can't be NOT NULL; NOT NULL is a constraint. You're right
though, if it was a new primary key, the column must already have the
NOT NULL constraint on it.

> > 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.

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.

>
> 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.

The index is an important implementation detail of a primary key,
because it is necessary to implement the UNIQUE constraint. Many PG DBAs
need to reindex the primary key on a large table as part of regular
maintenance. I am trying to provide a way to do this without locking our
reads or writes, using the already-existing CREATE INDEX CONCURRENTLY.

> 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

That doesn't solve the problem, because that doesn't allow you to choose
the index that the primary key will use, which was the whole point of my
suggestion.

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

Good question. Either way should be fine, as long as it is documented.
It should probably not be automatically dropped, but maybe issue a
NOTICE, like when the index is implicitly created.

Regards,
Jeff Davis