From: | terry(at)greatgulfhomes(dot)com |
---|---|
To: | "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Andrew Sullivan'" <andrew(at)libertyrms(dot)info> |
Cc: | "'PostgreSQL general list'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: db grows and grows |
Date: | 2002-06-19 22:37:38 |
Message-ID: | 001301c217e1$ebf0c320$2766f30a@development.greatgulfhomes.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)greatgulfhomes(dot)com
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)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(at)libertyrms(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2002-06-19 23:38:41 | Re: db grows and grows |
Previous Message | Tom Lane | 2002-06-19 22:16:03 | Re: Quoted identifiers in queries in plpgsql functions. |