| From: | Michael Fuhr <mike(at)fuhr(dot)org> |
|---|---|
| To: | Tobias Brox <tobias(at)nordicbet(dot)com> |
| Cc: | John A Meinel <john(at)arbash-meinel(dot)com>, Yves Vindevogel <yves(dot)vindevogel(at)implements(dot)be>, pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Limit clause not using index |
| Date: | 2005-06-21 21:08:19 |
| Message-ID: | 20050621210819.GA82792@winnie.fuhr.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Tue, Jun 21, 2005 at 09:46:39PM +0200, Tobias Brox wrote:
> [John A Meinel - Tue at 10:14:24AM -0500]
> > I believe if you drop the indexes inside a transaction, they will still
> > be there for other queries, and if you rollback instead of commit, you
> > won't lose anything.
>
> Has anyone tested this?
Observations from tests with 8.0.3:
DROP INDEX acquires an AccessExclusiveLock on the table and on the
index. This will cause the transaction executing the DROP INDEX
to block until no other transaction holds any kind of lock on either,
and once the locks are acquired, no other transaction will be able
to access the table or the index until the transaction doing the
DROP INDEX commits or rolls back. Rolling back leaves the index
in place.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Kevin Grittner | 2005-06-21 21:11:40 | Re: Limit clause not using index |
| Previous Message | Tobias Brox | 2005-06-21 20:40:57 | Re: Prepared statements vs. Stored Procedures |