| From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
|---|---|
| To: | terry(at)greatgulfhomes(dot)com |
| Cc: | 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 'Andrew Sullivan' <andrew(at)libertyrms(dot)info>, 'PostgreSQL general list' <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: db grows and grows |
| Date: | 2002-06-19 23:38:41 |
| Message-ID: | 20020620093841.A11342@svana.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Wed, Jun 19, 2002 at 06:37:38PM -0400, terry(at)greatgulfhomes(dot)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(at)svana(dot)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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Gregory Seidman | 2002-06-20 01:06:11 | Re: circular REFERENCES |
| Previous Message | terry | 2002-06-19 22:37:38 | Re: db grows and grows |