Re: Foreign Key Deadlocking

From: Csaba Nagy
Subject: Re: Foreign Key Deadlocking
Date: ,
Msg-id: 1176992622.16187.97.camel@coppola.muc.ecircle.de
(view: Whole thread, Raw)
In response to: Re: Foreign Key Deadlocking  (Alvaro Herrera)
List: pgsql-performance

Tree view

Foreign Key Deadlocking  ("Steven Flatt", )
 Re: Foreign Key Deadlocking  (Csaba Nagy, )
  Re: Foreign Key Deadlocking  (Dave Cramer, )
   Re: Foreign Key Deadlocking  (Erik Jones, )
   Re: Foreign Key Deadlocking  (Alvaro Herrera, )
    Re: Foreign Key Deadlocking  (Csaba Nagy, )
 Re: Foreign Key Deadlocking  (Tom Lane, )
  Re: Foreign Key Deadlocking  ("Steven Flatt", )

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




pgsql-performance by date:

From: "Michael Dengler"
Date:
Subject: Re: how to output column names
From: Andrew Lazarus
Date:
Subject: index structure for 114-dimension vector