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