I am going to restate my earlier proposal, to clarify it and in the hope
of stimulating more discussion.
One of the causes of deadlocks in Postgres is that its referential
integrity triggers can take locks in inconsistent orders. Generally a
child record will be locked before its parent, but not in all cases.
My proposal modifies the order in which locks are taken by referential
integrity triggers, so that parents are always locked before their
children.
The proposal is, that referential integrity triggers should fire before
locking the tuple from which they are triggered. I guess a new sort of
trigger is required for this, a before-lock trigger.
If this is a dumb idea, please tell me why. If it would cause more
problems than it solves, ditto. If it would be difficult to implement,
let's discuss and try to find solutions.
__
Marc