X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A57DCD1B2A6 for ; Thu, 22 Jul 2004 19:37:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88636-05 for ; Thu, 22 Jul 2004 22:37:23 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3CF1FD1B1BE for ; Thu, 22 Jul 2004 19:37:19 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i6MMbLvJ013324; Thu, 22 Jul 2004 18:37:21 -0400 (EDT) To: schen@graciousstyle.com Cc: pgsql-general@postgresql.org Subject: Re: [ADMIN] how to find transaction associated with a lock In-reply-to: <4100353D.9080202@graciousstyle.com> References: <41001296.3090609@graciousstyle.com> <12341.1090529103@sss.pgh.pa.us> <4100353D.9080202@graciousstyle.com> Comments: In-reply-to Si Chen message dated "Thu, 22 Jul 2004 14:44:29 -0700" Date: Thu, 22 Jul 2004 18:37:21 -0400 Message-ID: <13323.1090535841@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/685 X-Sequence-Number: 63284 Si Chen writes: > Do you think I'm looking down the wrong path? I'd bet that the problem is contention for a row referenced by a foreign key. When you insert a row that has a foreign key reference to another table, we need to lock the referenced row to ensure it doesn't get deleted until the inserting transaction commits. (Once you commit, your inserted row is visible and it's then the responsibility of a deleting transaction to notice the foreign-key violation. But until then, a deleting transaction can't even *see* your row so we need another way.) The real problem here is that we only have exclusive locks at the row level, so the only way to do this is to take an exclusive lock, and that means that transactions inserting references to the same referenced row block each other. So I think your INSERT is waiting on commit of some other transaction's INSERT that references the same foreign key row. Fixing this is on the TODO list but I don't expect any solution in the near future :-( regards, tom lane