Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6B40D9F9ED1 for ; Wed, 12 Sep 2007 17:47:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17661-02 for ; Wed, 12 Sep 2007 17:46:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from easy2.in-chemnitz.de (easy2.in-chemnitz.de [88.198.17.232]) by postgresql.org (Postfix) with ESMTP id C27D19F9930 for ; Wed, 12 Sep 2007 17:46:59 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by easy2.in-chemnitz.de (Postfix) with ESMTP id 7C82EB1E0F for ; Wed, 12 Sep 2007 22:46:57 +0200 (CEST) Received: from easy2.in-chemnitz.de ([127.0.0.1]) by localhost (easy.in-chemnitz.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id SbUVoq6yr8DK for ; Wed, 12 Sep 2007 22:46:57 +0200 (CEST) Received: by easy2.in-chemnitz.de (Postfix, from userid 1013) id 6A2BAB1DCD; Wed, 12 Sep 2007 22:46:57 +0200 (CEST) Date: Wed, 12 Sep 2007 22:46:57 +0200 From: Tino Schwarze To: pgsql-admin@postgresql.org Subject: automatically detecting long timed locks Message-ID: <20070912204657.GC29139@easy2.in-chemnitz.de> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/84 X-Sequence-Number: 26845 Hi there, is there an easy way to detect locks which are held for a very long time? We've got some problems with a database here where a lock is held for 1 or 2 hours though the operation should be very quick. A lot of other processes are then waiting for the lock to become available. We would like to query for "lock on table xyz being held for more than 60 seconds" or the other way around "query has been waiting for lock on table xyz for more than 60 seconds". Of course, we get log entries like "LOG: duration: 8544285.789 ms execute : lock table "xyz" in exclusive mode", but this is AFTER the lock got acquired. We'd like to notice if lock acquisition takes very long so we can look around and figure out what's wrong. How can we achieve this? Thanks, Tino. PS: Version is 8.2.4. -- www.spiritualdesign-chemnitz.de www.lebensraum11.de Tino Schwarze * Parkstraße 17h * 09120 Chemnitz