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
