Re: Online index builds

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);
> ?
>
> We have a number of cases where there isn't a true primary key on
> tables. It would be very attractive to have a non-blocking way of
> getting one, perhaps to be combined with letting Slony-I know about
> it...
>
> Or is it a better answer to look more deeply into the index
> configuration, creating a suitably named UNIQUE index on NOT NULL
> fields, and fiddling it into being the primary key?

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, or if it's not unique). 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).

It makes CREATE INDEX CONCURRENTLY more useful for reindexing a primary
key on a live database: you could just create the new index, switch it
to be the primary key, and drop the old index.

Regards,
Jeff Davis