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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
