Re: does reindex need exclusive table access?

On Tuesday 01 November 2011 12:00:33 Craig Ringer wrote:
> A workaround for reindexing while live is to begin a transaction, create
> the new index with a new name, drop the old one, rename the new one to
> the old one, and commit. This only requires an exclusive lock for the
> period of the drop and rename. On more recent versions you can even use
> this for indexes that implement primary key or unique constrants by
> using the 'ADD CONSTRAINT ... USING INDEX ...' syntax; see:
>
>   http://www.postgresql.org/docs/current/static/sql-altertable.html

Been happily doing this (without worrying about transactions, as Tom
suggested), but couldn't quite figure out how to do it with my primary key
indexes on 8.3. Do I have to bite the bullet and take an exclusive lock (or
upgrade to 9.1) ?

The technique kinda works (with some changes) using unique indexes however. Is
there a functional difference between a unique index and a primary key index
(knowing that my column is not null) ? Or is it just for documentation and ORM
purposes ?


--
Vincent de Phily