On Aug 15, 2007, at 1:27 PM, Dmitry Koterov wrote:
I have tested all cases, the code I quoted is complete and minimal. All operations are non-blocking (count incrementation is non- blocking, insertion with a foreign key is non-blocking too), but it still generates a deadlock time to time. Deletion of the foreign key constraint completely solves the problem.
Code? Got a reproducible test case?
You said "I'm pretty sure that recent versions check to see if the key actually changed", but how could it be if Postgres uses a row- level locking, not field-level locking? Seems it cannot check what fields are changed, it locks the whole row.
You already have the child row that's being updated; both versions of it. So you don't have to lock anything to see if the FK field has changed or not.
But... taking a quick look at RI_FKey_check in backend/utils/adt/ ri_triggers.c, I don't see it checking to see if the FK has changed, which seems odd. I would think that if the FK fields haven't changed that there's no need to perform the check.
-- Decibel!, aka Jim Nasby decibel@xxxxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
