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