There's another poster complaining about referential integrity checks causing
deadlocks. Unfortunately I've deleted the message so this response (and the
archives aren't responding) isn't going to show up on the right thread.
The reason the deadlock is happening is because of a known deficiency in
Postgres that postgres has to take an exclusive lock on the records to ensure
they aren't deleted before your insert/update commits. Unfortunately this
isn't likely to be fixed soon, certainly not in 8.0.
However I'm a bit surprised it causes deadlocks. It seems like you should be
able to avoid deadlocks by making sure all the referential integrity checks
are performed in a consistent order.
At least some versions of postgres ensure that constraint triggers will be
executed in alphabetical order. This means if all your foreign key constraints
are named in a consistent order the row locks should be taken in a consistent
order. If that's true then deadlocks shouldn't happen.
So you would have to take a schema dump, grep out all the foriegn key
constraints, sort them and uniquefy them, decide on an order, and then go
through every table renaming them to enforce that order.
That could be a lot of work so hopefully someone more knowledgeable will be
able to confirm that this should work (and which versions it would work in)
before you go about trying it. I'm sure other people would like to hear if
it's successful since it's a pretty frequently asked question.
--
greg
