X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7C66A52835 for ; Tue, 21 Jun 2005 18:08:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06836-07 for ; Tue, 21 Jun 2005 21:08:46 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id ED5B9528BD for ; Tue, 21 Jun 2005 18:08:42 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j5LL8KqO075854 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 21 Jun 2005 15:08:24 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j5LL8KYd082923; Tue, 21 Jun 2005 15:08:20 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j5LL8JKE082922; Tue, 21 Jun 2005 15:08:19 -0600 (MDT) (envelope-from mfuhr) Date: Tue, 21 Jun 2005 15:08:19 -0600 From: Michael Fuhr To: Tobias Brox Cc: John A Meinel , Yves Vindevogel , pgsql-performance@postgresql.org Subject: Re: Limit clause not using index Message-ID: <20050621210819.GA82792@winnie.fuhr.org> References: <42B8275C.8040300@arbash-meinel.com> <7e514564b9a159480857de53cc1764fd@implements.be> <42B82ED0.1010009@arbash-meinel.com> <20050621194639.GG22746@oppetid.no> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050621194639.GG22746@oppetid.no> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/412 X-Sequence-Number: 13049 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/