Re: does reindex need exclusive table access?

On 01/11/11 02:51, Gauthier, Dave wrote:
> v8.3.4 on linux.
>
>
>
> Does "reindex table foo" require no other users accessing the foo
> table? Trying to understand why this seems to be stalled when I attempt
> this on a live DB (if runs fine/fast on a copy of the DB that no one uses).

Yes, it requires an exclusive lock.

It doesn't matter whether or not you run it as part of an explicit
transaction.

There is not currently any 'REINDEX CONCURRENTLY' command - not unless
it's been added in a very recent version and I haven't noticed yet. You
can CREATE INDEX CONCURRENTLY then drop the old index, though.

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

--
Craig Ringer