Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E27C6633049 for ; Sun, 1 Mar 2009 21:57:42 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 22338-02 for ; Sun, 1 Mar 2009 21:57:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 18FA9632CE1 for ; Sun, 1 Mar 2009 21:57:38 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n221vYad018269; Sun, 1 Mar 2009 20:57:34 -0500 (EST) To: "Cox, Brian" cc: pgsql-performance@postgresql.org Subject: Re: "slow" queries In-reply-to: References: <49A9F834.8060100@ca.com> <12688.1235927151@sss.pgh.pa.us> Comments: In-reply-to "Cox, Brian" message dated "Sun, 01 Mar 2009 14:21:54 -0500" Date: Sun, 01 Mar 2009 20:57:34 -0500 Message-ID: <18268.1235959054@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/11 X-Sequence-Number: 32933 "Cox, Brian" writes: >> Probably because the DROP is trying to acquire exclusive lock on its >> target table, and some other transaction already has a read or write >> lock on that table, and everything else is queuing up behind the DROP. >> It's not a true deadlock that is visible to the database, or else >> Postgres would have failed enough of the transactions to remove the >> deadlock. Rather, what you've got is some very-long-running transaction >> that is still making progress, or else is sitting idle because its >> client is neglecting to close it; and everything else is blocked behind >> that. > This "deadlock" finished after 18h and 48m. As there is only 1 select > on a table with 400 rows and 10 inserts into a separate partition than > the one being dropped, what could possible take 18:48 to do? [ shrug... ] You tell us. To me it sounds a whole lot like some client program sitting on an open transaction that has a nonexclusive lock on the table to be dropped. That transaction wasn't necessarily doing any useful work; it might have just been waiting on the client. At this point I suppose arguing about it is moot because the evidence is all gone. If it happens again, capture the contents of pg_locks and pg_stat_activity while things are still stuck. regards, tom lane