From: | Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>, "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com> |
Subject: | Re: does reindex need exclusive table access? |
Date: | 2011-11-02 13:59:38 |
Message-ID: | 9804850.BUHEhE4VcT@moltowork |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-11-02 14:11:54 | Re: does reindex need exclusive table access? |
Previous Message | Merlin Moncure | 2011-11-02 13:49:15 | Re: select where not exists returning multiple rows? |