So, I'm being confused about foreign key constraint handling in Postgresql. (version 8.4.4, for what it's worth).
We've got a couple of tables, mildly anonymized below:
device:
(id, blah, blah, blah, blah, blah x 50)…
primary key on id
whooooole bunch of other junk
device_foo:
(id, device_id, left, right)
Foreign key (device_id) references device(id) on delete cascade;
primary key on id
btree index on 'left' and 'right'
So I set out with two database windows to run some queries.
db1> begin; lock table device in exclusive mode;
db2> begin; update device_foo set left = left + 1;
The db2 connection blocks.
It seems odd to me that an update of the 'left' column on device_stuff should be affected by activity on the device table. But it is. In fact, if I go back to db1:
db1> select * from device_stuff for update;
*** deadlock occurs ***
The pgsql log has the following:
blah blah blah deadlock blah.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."device" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF X: update device_foo set left = left + 1;
I suppose I've got two issues: the first is that I don't understand the precise mechanism by which this sort of locking occurs. I have got a couple of useful queries to query pg_locks to see what sort of locks a statement invokes, but I haven't been able to observe this particular sort of locking when I run the update device_foo command in isolation. (Perhaps I'm doing something wrong, though.) I also can't find any documentation on the lock acquisition behavior of foreign-key constraint checks. All I have is a log message. Am I to infer from this that any change to a row will acquire an update lock on all the tables which it's foreign-keyed against?
The second issue is that I'd like to find some way to make it not happen like that. I'm ending up with occasional deadlocks in the actual application. I'd like to be able to run big update statements that impact all rows on device_foo without acquiring a big lock on the device table. (There's a lot of access going on in the device table, and it's kind of an expensive lock to get.)
