Thomas Kellerer wrote: > Craig Ringer wrote on 24.05.2009 17:58: >> There isn't currently any REINDEX CONCURRENTLY option > > But them manual does list this option: > > http://www.postgresql.org/docs/8.3/static/sql-createindex.html > > "When this option is used, PostgreSQL will build the index without > taking any locks that prevent concurrent inserts, updates, or deletes on > the table" Correct - PostgreSQL supports CREATE INDEX CONCURRENTLY. Just not REINDEX CONCURRENTLY. See: http://www.postgresql.org/docs/8.3/static/sql-reindex.html "To build the index without interfering with production you should drop the index and reissue the CREATE INDEX CONCURRENTLY command." The locking docs: http://www.postgresql.org/docs/8.3/static/explicit-locking.html don't say anything about which lock DROP INDEX takes on a relation. A quick test shows it takes an AccessExclusiveLock on the affected index, and appears to take no lock at all on the table: -[ RECORD 6 ]------+-------------------- locktype | relation database | 46220 relation | 71963 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 1/8976 pid | 19189 mode | AccessExclusiveLock granted | t where in this case: bs=# select relkind from pg_class where oid = 71963; relkind --------- i (1 row) we can see that the lock target is an index - specifically, the index I just DROPped. So, you can indeed just: BEGIN; DROP INDEX ... CREATE INDEX ... CONCURRENTLY COMMIT; without interrupting client work. I think. That's really what I was referring to with "workarounds exist" though, anyway. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
