Re: FKs and deadlocks

On Fri, 22 Oct 2004, Philippe Lang wrote:

> I have tried to correct that by adding a "SET CONSTRAINTS ALL DEFERRED"
> in every trigger and function, hoping it would solve my problem. Maybe
> it helped, but it did not solve anything.

Note that set constraints all deferred does nothing unless you made the
constraint deferrable which is not the default. If your constraints
aren't then you won't see any effect, and you'll probably want to change
them and see if that does help (and possibly making them initially
deferred at the same time).

> I don't know if anyone has a better idea, but I would like to try taking
> away some FKs in my schema. My problem is that I really don't know which
> one to delete. There are over 40 tables. Are there rules to do that? Or
> maybe can I simply wait on the next deadlock, and try understanding who
> got locked by who? OK, but how can I do that?

I think you may be able to do this if you turn on statement locking and
try to resurrect the state from the logs. If you want to send a possibly
slightly sanitized typical sequence of events, we might be able to help
with that part.