Re: do postgresql this job for me ? (firebird user) - Mailing list pgsql-general

Christophe <xof@thebuild.com> writes:
> On May 24, 2009, at 8:58 AM, Craig Ringer wrote:
>> There isn't currently any REINDEX CONCURRENTLY
>> option, but people seem to have a few workarounds that do the job if you
>> really do need to rebuild an index on a live, active table.

> It's pretty straight-forward to do:

> CREATE INDEX CONCURRENTLY idx_new ON table ... ;
> DROP INDEX idx;
> ALTER INDEX idx_new RENAME TO idx;

However, this doesn't work for an index that's underlying a UNIQUE or
PRIMARY KEY constraint, nor an index that's depended on by a FOREIGN KEY
reference --- the system won't let you drop such indexes.  Plus there
are obvious opportunities for human error.  So we oughta do REINDEX
CONCURRENTLY someday ...

            regards, tom lane