X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E49ED9FB1B9 for ; Wed, 26 Jul 2006 20:14:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 48542-05 for ; Wed, 26 Jul 2006 20:13:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id E73E89FA69A for ; Wed, 26 Jul 2006 20:13:25 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.7/8.13.7) with ESMTP id k6QNDHxZ018033 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 26 Jul 2006 17:13:19 -0600 (MDT) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.6/8.13.6) with ESMTP id k6QNDHpo062100; Wed, 26 Jul 2006 17:13:17 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.6/8.13.6/Submit) id k6QNDGak062099; Wed, 26 Jul 2006 17:13:16 -0600 (MDT) (envelope-from mfuhr) Date: Wed, 26 Jul 2006 17:13:16 -0600 From: Michael Fuhr To: Chris Hoover Cc: "pgsql-admin@postgresql.org" Subject: Re: create table problems Message-ID: <20060726231316.GA61952@winnie.fuhr.org> References: <1d219a6f0607261351w2a743022t65e9d026b89dffdf@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1d219a6f0607261351w2a743022t65e9d026b89dffdf@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.153 tagged_above=0 required=5 tests=AWL, SPF_PASS X-Spam-Level: X-Archive-Number: 200607/226 X-Sequence-Number: 22472 On Wed, Jul 26, 2006 at 04:51:34PM -0400, Chris Hoover wrote: > I am trying to create a table in my production system that has a foreign > key into an existing table. However, this action is being blocked by > transactions on that table, and is blocking any additional selects on that > table. > > Why is this? The table is empty (being a create), so I don't understand > what Postgresql is trying to do. The transaction that's creating the new table wants to acquire an AccessExclusiveLock on the referenced table, presumably so it can alter that table to add referential integrity triggers. The referenced table needs to have such triggers so it knows what integrity checks to make when somebody tries to update or delete its rows. Maybe one of the developers will confirm or refute. -- Michael Fuhr