Re: Online index builds

wmoran(at)collaborativefusion(dot)com (Bill Moran) writes:
> In response to Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
>
>> Bill Moran wrote:
>> > In response to Josh Berkus <josh(at)postgresql(dot)org>:
>> >
>> > > -- Online index builds
>> >
>> > I'm particularly curious about this feature. Does this mean that
>> > PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
>> > table from writes?
>> >
>> > If so, the 8.2 docs are a bit out of date:
>> > http://www.postgresql.org/docs/8.2/static/sql-reindex.html
>>
>> No, it means you can do CREATE INDEX CONCURRENTLY.
>>
>> http://www.postgresql.org/docs/8.2/static/sql-createindex.html
>
> Ahh ... and the text there specifically states that REINDEX does
> _not_ work concurrently.
>
> Thanks.

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?
--
let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/advocacy.html
"Marketing Division, Sirius Cybernetics Corp: A bunch of mindless
jerks who'll be the first against the wall when the revolution comes."
-- The Hitchhiker's Guide to the Galaxy