Tom Lane wrote:> You are probably getting deadlocks on the rows referenced by the foreign> keys, then. As of the
currentrelease, each UPDATEd row will cause the> foreign-key triggers to grab a row lock (SELECT FOR UPDATE lock) on
the>referenced row in the other table. If you have concurrent updates> touching rows that reference the same
other-tablerows, it's possible to> get a deadlock depending on the order in which the updates happen to> occur.
> There is a patch in CVS tip that alleviates the problem substantially> by not taking a foreign-table lock when the
foreignkey value isn't> being changed by the UPDATE, which is commonly the case (although I'm> not entirely sure that
itwill help you, since you do seem to be> updating the referencing column). If you want to try it, I believe Jan>
Wieckposted a 7.3 version of the patch a month or so ago.
I see. One item I forgot to mention ... Both update statements have a
where clause containing txn.terminalid=38. Txn.terminalid is a foreign
key column in that table (txn.terminalid->terminal.terminalid). Could
this be the source of the trouble?
If so, I can work around this for now, but the patch you mention sounds
like it would work in almost all situations where this might adversely
(and unexpectedly) affect us.
Thanks!
--
Matt Mello