On Wed, Jun 19, 2002 at 06:37:38PM -0400, terry@greatgulfhomes.com wrote:
> If he needs to REINDEX live without locking out selects, then simply DROP
> INDEX <indexname>; and then do a CREATE INDEX ... to recreate it. Drop is
> fast, and the create does not acquire the exclusive lock.
>
> The only down side of delete/recreate is that with REINDEX one did not need
> to know the statement to recreate the index, and the latter does.
A while ago I did post a script that did this, only it did the create first,
then the drop and then renamed the new index to the old one. All within a
transaction so other queries wouldn't be left without an index.
It used pg_dump to get the CREATE INDEX command.
Should be in the archive somewhere...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.