Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 50DB61337B7D for ; Tue, 4 Jan 2011 09:52:38 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 44777-04 for ; Tue, 4 Jan 2011 13:52:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx01.bfk.de (mx01.bfk.de [193.227.124.2]) by mail.postgresql.org (Postfix) with ESMTP id 12F611337B77 for ; Tue, 4 Jan 2011 09:52:29 -0400 (AST) Received: from mx00.int.bfk.de ([10.119.110.2]) by mx01.bfk.de with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:32) id 1Pa7JI-0003Ev-Jy; Tue, 04 Jan 2011 13:52:24 +0000 Received: by bfk.de with local id 1Pa7JI-0003Z0-FR; Tue, 04 Jan 2011 13:52:24 +0000 To: Tom Lane Cc: kakarukeys , pgsql-performance@postgresql.org Subject: Re: adding foreign key constraint locks up table References: <75218696-61be-4730-89f6-dd6058fa9eda@a28g2000prb.googlegroups.com> <82ei926j3p.fsf@mid.bfk.de> <19238.1293548900@sss.pgh.pa.us> From: Florian Weimer Date: Tue, 04 Jan 2011 13:52:24 +0000 In-Reply-To: <19238.1293548900@sss.pgh.pa.us> (Tom Lane's message of "Tue\, 28 Dec 2010 10\:08\:20 -0500") Message-ID: <8239p8rd2f.fsf@mid.bfk.de> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/8 X-Sequence-Number: 41795 * Tom Lane: >> Do you mean that the ALTER query and subsequent queries are shown as >> "waiting" in pg_stat_activity? In this case, I'm also wondering why >> this is inecessary. > > ALTER ADD FOREIGN KEY must lock both tables to add triggers to them. But why is such a broad lock needed? If the table was created in the current transaction and is empty, the contents of the foreign key table should not matter. --=20 Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstra=DFe 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99