Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A7A956326C5 for ; Wed, 7 Oct 2009 02:25:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 92400-08 for ; Wed, 7 Oct 2009 05:25:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp116.dfw.emailsrvr.com (smtp116.dfw.emailsrvr.com [67.192.241.116]) by mail.postgresql.org (Postfix) with ESMTP id DF07C632475 for ; Wed, 7 Oct 2009 02:25:47 -0300 (ADT) Received: from relay1.relay.dfw.mlsrvr.com (localhost [127.0.0.1]) by relay1.relay.dfw.mlsrvr.com (SMTP Server) with ESMTP id 5C1F412781C7; Wed, 7 Oct 2009 01:25:46 -0400 (EDT) Received: by relay1.relay.dfw.mlsrvr.com (Authenticated sender: ejones-AT-engineyard.com) with ESMTPSA id DF4B01278147; Wed, 7 Oct 2009 01:25:45 -0400 (EDT) Subject: Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) Mime-Version: 1.0 (Apple Message framework v1075.2) Content-Type: text/plain; charset=us-ascii; format=flowed; delsp=yes From: Erik Jones In-Reply-To: <4AB90198.2010109@obviously.com> Date: Tue, 6 Oct 2009 22:25:44 -0700 Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit Message-Id: <1AE076FD-0189-4DE3-B26C-C6B433061706@engineyard.com> References: <4AB90198.2010109@obviously.com> To: Bryce Nesbitt X-Mailer: Apple Mail (2.1075.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.299 tagged_above=-10 required=5 tests=AWL=-1.300, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200910/25 X-Sequence-Number: 33224 On Sep 22, 2009, at 9:55 AM, Bryce Nesbitt wrote: > # SELECT l.locktype,c.relname,l.pid,l.mode,granted from pg_locks > l,pg_class c where l.relation=c.oid order by relname,granted; > locktype | relname | pid | > mode | granted > relation | article_key_idx | 18891 | > AccessShareLock | t > relation | articles | 18891 | > AccessShareLock | t > relation | articles_editorid_idx | 18891 | > AccessShareLock | t > relation | articles_pkey | 18891 | > AccessShareLock | t > relation | articles_response_to_key_idx | 18891 | > AccessShareLock | t > relation | articles_state_idx | 18891 | > AccessShareLock | t > relation | article_words | 18891 | > AccessShareLock | t > relation | article_words_cw | 18891 | > AccessShareLock | t > relation | article_words_wc | 18891 | > AccessShareLock | t > relation | collection_context_key_idx | 18891 | > AccessShareLock | t > relation | collection_owner_key_idx | 18891 | > AccessShareLock | t > relation | collections | 18891 | > AccessShareLock | t > relation | context_publication_key_idx | 18891 | > AccessShareLock | t > relation | contexts | 18891 | > AccessShareLock | t > relation | contexts | 3879 | > AccessExclusiveLock | f > relation | contexts | 5477 | > AccessShareLock | f > relation | contexts | 5484 | > AccessShareLock | f > relation | contexts | 5485 | > AccessShareLock | f > relation | contexts | 5486 | > AccessShareLock | f > relation | contexts | 5487 | > AccessShareLock | f > relation | contexts | 5489 | > AccessShareLock | f > relation | contexts | 5493 | > AccessShareLock | f > relation | contexts | 5494 | > AccessShareLock | f > relation | contexts | 5496 | > AccessShareLock | f > relation | contexts | 5497 | > AccessShareLock | f > relation | contexts | 5498 | > AccessShareLock | f > relation | contexts | 5499 | > AccessShareLock | f > relation | contexts | 5500 | > AccessShareLock | f > relation | contexts | 5502 | > AccessShareLock | f > relation | contexts | 5503 | > AccessShareLock | f > relation | contexts | 5504 | > AccessShareLock | f > relation | contexts | 5505 | > AccessShareLock | f > relation | contexts | 5506 | > AccessShareLock | f > relation | contexts | 5507 | > AccessShareLock | f > relation | contexts | 5508 | > AccessShareLock | f > relation | contexts | 5509 | > AccessShareLock | f > relation | contexts | 5510 | > AccessShareLock | f > relation | contexts | 5511 | > AccessShareLock | f > relation | contexts | 5512 | > AccessShareLock | f > relation | contexts | 5515 | > AccessShareLock | f > relation | contexts | 5516 | > AccessShareLock | f > relation | contexts | 5517 | > AccessShareLock | f > relation | contexts | 5518 | > AccessShareLock | f > relation | contexts | 5519 | > AccessShareLock | f > relation | contexts | 5520 | > AccessShareLock | f > relation | contexts | 5521 | > AccessShareLock | f > relation | contexts | 5523 | > AccessShareLock | f > relation | contexts | 5524 | > AccessShareLock | f > relation | contexts_id_key | 18891 | > AccessShareLock | t > relation | contexts_pkey | 18891 | > AccessShareLock | t > relation | contexts_publication_date_idx | 18891 | > AccessShareLock | t > relation | contexts_site_key_ct_id_journal_key | 18891 | > AccessShareLock | t > relation | contexts_site_key_ct_type_idx | 18891 | > AccessShareLock | t > relation | group_key_idx | 18891 | > AccessShareLock | t > relation | parent_key_idx | 18891 | > AccessShareLock | t > relation | pg_class | 3911 | > AccessShareLock | t > relation | pg_class_oid_index | 3911 | > AccessShareLock | t > relation | pg_class_relname_nsp_index | 3911 | > AccessShareLock | t > relation | pg_locks | 3911 | > AccessShareLock | t > relation | site_key_idx | 18891 | > AccessShareLock | t > relation | virtual_ancestor_key_idx | 18891 | > AccessShareLock | t > relation | virtual_ancestors | 18891 | > AccessShareLock | t > relation | virtual_ancestors_pkey | 18891 | > AccessShareLock | t > relation | virtual_context_key_idx | 18891 | > AccessShareLock | t > relation | words | 18891 | > AccessShareLock | t > relation | words_pkey | 18891 | > AccessShareLock | t > relation | words_word | 18891 | > AccessShareLock | t Note that pid 3879 can't get the AccessExclusiveLock because pid 18891 is already holding an AccessShareLock. pid 18891 also had a lot of AccessShareLocks on a bunch of other tables. What is it doing? Figure out/get rid of that and you're problem will go away. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k