Hi all, While postgres 8.x improved a lot the locking issues related to foreign keys, the locking used is still stronger than needed. The following test case deadlocks on postgres but goes through on oracle: preparation of tables: create table test1(col_fk integer primary key, col_1 integer); create table test2(col_2 integer primary key, col_fk integer references test1(col_fk)); insert into test1 (col_fk, col_1) values (1, 1); insert into test1 (col_fk, col_1) values (2, 2); session_1: begin; update test1 set col_1 = 10 where col_fk = 1; session_2: begin; insert into test2 (col_2, col_fk) values (1, 2); session_1: -- this locks on postgres, does not on oracle update test1 set col_1 = 20 where col_fk = 2; session_2: -- deadlock on postgres, goes through on oracle insert into test2 (col_2, col_fk) values (2, 1); The problem is that this deadlock cannot be solved by predictable ordering of any of the 2 sets of statements, because the foreign key is not sure to keep the same ordering relationship between the primary keys of the tables (i.e. there can be a smaller col_fk associated to a bigger col_2 as well as the other way around). This kind of deadlock is causing us quite some trouble as we have some operations like this where circumventing it would cause unacceptable contention (practically serializing all our operation which we carefully distribute to multiple processing boxes), even if the deadlock wouldn't be per se a problem (but it is, there are complex updates/inserts on thousands of rows in those transactions). Our solution is to patch the postgres sources to omit the shared lock of the parent row altogether. This leaves the possibility that some orphan child records slip in if the parent row is deleted while the child row is updated/inserted, but this is causing us less trouble than the deadlock, as the delete rate is quite low in our application compared to the update rate, and the orphan rows will be ignored anyway (of course this would be unacceptable in many applications, it just happens to be OK for us). In fact, the postgres regression suite is happily going through with the patch applied (no locking on FK parent rows). And the patched version will also not lock/deadlock on the above test case... Now the real question: is it possible to further relax the lock needed by the foreign key relationship ? I guess this has something to do with some form of predicate locking, and I wonder if there is a simple way to achieve this for supporting the FK locking. The fact that Oracle doesn't lock/deadlock tells me that it must be possible to do it, although it is likely not easy... Cheers, Csaba.
