Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id EB3B69FB949 for ; Thu, 19 Apr 2007 11:16:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 16305-02 for ; Thu, 19 Apr 2007 11:16:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id BA1319FB934 for ; Thu, 19 Apr 2007 11:16:14 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-221-161.bk12-dsl.surnet.cl [201.221.221.161]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l3JEEiM7025442; Thu, 19 Apr 2007 07:14:45 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 344AC32A0C; Thu, 19 Apr 2007 10:14:42 -0400 (CLT) Date: Thu, 19 Apr 2007 10:14:42 -0400 From: Alvaro Herrera To: Dave Cramer Cc: Csaba Nagy , Steven Flatt , postgres performance list Subject: Re: Foreign Key Deadlocking Message-ID: <20070419141442.GE4572@alvh.no-ip.org> Mail-Followup-To: Dave Cramer , Csaba Nagy , Steven Flatt , postgres performance list References: <357fa7590704180807j1906d42u1f0f539ac561dc3e@mail.gmail.com> <1176910597.16187.75.camel@coppola.muc.ecircle.de> <049552E8-8EBB-4B44-8D65-6108456A2421@fastcrypt.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <049552E8-8EBB-4B44-8D65-6108456A2421@fastcrypt.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Thu, 19 Apr 2007 07:14:45 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/345 X-Sequence-Number: 24222 Dave Cramer escribió: > Hi Csaba, > > I have a similar problem. > > In an attempt to avoid the overhead of select count(*) from mailbox > where uid = somuid I've implemented triggers on insert and delete. > > So there is a > > user table which refers to to an inbox table, > > so when people insert into the inbox there is an RI trigger grabbing > the shared lock, then the count triggers try to grab an exclusive > lock resulting in a deadlock. > > Can we safely remove the shared locks ? > > Is there a right way to implement the count triggers. I've tried > before triggers, and after triggers, both result in different kinds > of deadlocks. Would it be possible for the triggers to lock the records, before starting the actual operation, in well known orders, to avoid the deadlocks? A frequently mentioned approach to avoid the point of contention is to have a "totals" record and have the triggers insert "deltas" records; to get the sum, add them all. Periodically, take the deltas and apply them to the totals. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support