Subject: Re: Foreign Key Deadlocking
From: Csaba Nagy <nagy@ecircle-ag.com>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Dave Cramer <pg@fastcrypt.com>, Steven Flatt <steven.flatt@gmail.com>,
	postgres performance list <pgsql-performance@postgresql.org>
In-Reply-To: <20070419141442.GE4572@alvh.no-ip.org>
References: <357fa7590704180807j1906d42u1f0f539ac561dc3e@mail.gmail.com>
	<1176910597.16187.75.camel@coppola.muc.ecircle.de>
	<049552E8-8EBB-4B44-8D65-6108456A2421@fastcrypt.com>
	<20070419141442.GE4572@alvh.no-ip.org>
Content-Type: text/plain
Message-Id: <1176992622.16187.97.camel@coppola.muc.ecircle.de>
Mime-Version: 1.0
Date: Thu, 19 Apr 2007 16:23:42 +0200
Content-Transfer-Encoding: 7bit

> 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.

This is what we do here too. There is only one exception to this rule,
in one case we actually need to have the inserted records and the
updated parent in one transaction for data consistency, in that case the
delta approach won't work... we didn't find any other solution to that
except patching postgres not to lock the parent keys at all, which has
it's own problems too (occasional breakage of the foreign key
relationship when the parent is deleted and a child still slips in, but
this is very rare in our case not to cause problems which cannot be
cleaned up with relative ease - not to mention that there could be other
problems we didn't discover yet or our usage patterns are avoiding).

Cheers,
Csaba.