Skip site navigation (1) Skip section navigation (2)

Re: Foreign Key Deadlocking

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Dave Cramer <pg(at)fastcrypt(dot)com>, Steven Flatt <steven(dot)flatt(at)gmail(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Foreign Key Deadlocking
Date: 2007-04-19 14:23:42
Message-ID: 1176992622.16187.97.camel@coppola.muc.ecircle.de (view raw or flat)
Thread:
Lists: pgsql-performance
> 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.



In response to

pgsql-performance by date

Next:From: Michael DenglerDate: 2007-04-19 17:06:25
Subject: how to output column names
Previous:From: Erik JonesDate: 2007-04-19 14:15:45
Subject: Re: Foreign Key Deadlocking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group