Re: Online index builds

wmoran@collaborativefusion.com (Bill Moran) writes:
> In response to Alvaro Herrera <alvherre@commandprompt.com>:
>
>> Bill Moran wrote:
>> > In response to Josh Berkus <josh@postgresql.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