Re: db grows and grows

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.

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Wednesday, June 19, 2002 5:32 PM
> To: Andrew Sullivan
> Cc: PostgreSQL general list
> Subject: Re: [GENERAL] db grows and grows
>
>
> Andrew Sullivan <andrew@libertyrms.info> writes:
> > On Wed, Jun 19, 2002 at 09:09:32PM +0200, Bjoern Metzdorf wrote:
> >> During the REINDEX-run I see plenty "SELECT (waiting)"
> with ps. So I can
> >> confirm this.
>
> > But why would a ShareLock cause SELECT to wait?
>
> He was doing REINDEX, which grabs an exclusive lock.
>
> The reason CREATE INDEX can run concurrently with SELECTs is that the
> SELECTs won't see (and perhaps try to use) the new index, because its
> catalog entries aren't committed yet.  REINDEX needs an exclusive lock
> to prevent other transactions from trying to use the
> index-under-reconstruction, since they certainly would see it as
> an available index.
>
> While one can think of hacks that might be able to work around that,
> it's not clear to me that we should expend development time on making
> REINDEX slicker --- fixing the underlying space-management problem in
> btree indexes would be a better expenditure of time IMHO.  REINDEX
> is really intended for disaster recovery, not routine space
> management.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>