FK locking still too strong

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.