Re: do postgresql this job for me ? (firebird user)

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