Problem with foreign keys (performance and deadlocks) - Mailing list pgsql-bugs

Sorry for this being so long but I want to describe this as thoroughly as p=
ossible.

I am having two problems with foreign keys.  One is a performance problem a=
nd the other is a deadlock problem but they  are related to the same root c=
ause.  I am running PostgreSQL 7.3 (the released version).

I have isolated it down to a simple test:

Given the following database:

create table names (
    id integer not null,
    name text not null,
    primary key (id)
);

create table orders (
    id integer not null,
    nameid integer,
    description text,
    primary key (id)
);
alter table orders add constraint oname foreign key(nameid) references name=
s(id);

insert into names values (1,'bob');
insert into names values (2,'fred');
insert into names values (3,'sam');

insert into orders values (1,1,'bob1');
insert into orders values (2,1,'bob2');
insert into orders values (3,1,'bob3');
insert into orders values (4,2,'fred1');
insert into orders values (5,3,'sam1');

To reproduce the bug, start psql on the database in two different shells.

In shell A:

begin;
update orders set description=3D'bob1-1' where id=3D1;


In shell B:

begin;
update orders set description=3D'bob2-1' where id=3D2;


The update in shell B will blocuntilll you do a "commit;" or "rollback;" in=
 shell A.  This blocking should not occur.

The problem is that the update in shell A causes a

SELECT 1 FROM ONLY "public"."names" x WHERE "id" =3D $1 FOR UPDATE OF x

statement to be generated internally as part of the foreign key checking.  =
For shell A this works fine but when shell B executes this line it blocks u=
ntil the transaction in shell A does a commit or rollback.

The purpose of this SELECT seems to be two-fold:
 1. To make sure that row in the target table exists.
2. To make sure that the row does not get deleted or that column in that ro=
w does not get changed until the commit  happens because other transactions=
 cannot see the changes until the commit happens.

As a test I went into "backend/utils/adt/ri_triggers.c" and removed the "FO=
R UPDATE OF X" from the foreign key checks  and the concurrency issues disa=
ppeared.  This still make check 1 happen but removed the safety net of chec=
k 2.

The "FOR UPDATE OF X" seems to grab a lock that cannot be shared so the sec=
ond foreign key select must wait until the  first one releases.  Is there a=
 weaker lock that can applied to the foreign key check in ri_triggers.c?  I=
s a new type  of lock "FOR FKEY OF X" required?

This really drags down our system when we get alot of traffic.  It also als=
o causes deadlocks.

DEADLOCK
--------

The example is a very simple case but in my application where I have more t=
ables and multiple foreign keys I run into  deadlocks.

In the simplest case I have multiple "information" tables that are the targ=
ets of foreign keys.  I have 2 "data"  tables that have foreign keys into t=
he information tables.  If I am inserting/updating rows in tables "data1" a=
nd  "data2".

Here is an example I made up to (hopefully) make this clear:

create table names (
    id integer not null,
    name text not null,
    primary key (id)
);

create table billaddr (
    id integer not null,
    address text not null,
    primary key (id)
);

create table shipaddr (
    id integer not null,
    address text not null,
    primary key (id)
);

create table phone_orders (
    id integer not null,
    nameid integer,
    billid integer,
    shipid integer,
    description text,
    primary key (id)
);
alter table phone_orders add constraint poname  foreign key(nameid) referen=
ces names(id);
alter table phone_orders add constraint pobaddr foreign key(billid) referen=
ces billaddr(id);
alter table phone_orders add constraint posaddr foreign key(shipid) referen=
ces shipaddr(id);

create table web_orders (
    id integer not null,
    nameid integer,
    billid integer,
    shipid integer,
    description text,
    primary key (id)
);
alter table web_orders add constraint woname  foreign key(nameid) reference=
s names(id);
alter table web_orders add constraint wobaddr foreign key(billid) reference=
s billaddr(id);
alter table web_orders add constraint wosaddr foreign key(shipid) reference=
s shipaddr(id);

insert into names values (1,'bob');
insert into names values (2,'fred');
insert into names values (3,'sam');

insert into billaddr values (1,'123 main st');
insert into billaddr values (2,'456 minor ave');

insert into shipaddr values (1,'789 major ct');
insert into shipaddr values (2,'912 losers lane');

insert into phone_orders values (1,1,1,1,'phone order 1');
insert into phone_orders values (2,2,2,2,'phone order 2');

insert into web_orders values (1,1,1,1,'web order 1');
insert into web_orders values (2,2,2,2,'web order 2');


Once again start psql on the database in two different shells.

In shell A:

begin;
update phone_orders set description=3D'phone order 1-1' where id=3D1;


In shell B:

begin;
update web_orders set description=3D'web order 1-1' where id=3D1;

If the PostgreSQL server acquires the foreign key locks in a different orde=
r on the web-orders and phone_orders tables  then you will get a deadlock. =
 When I ran this exact case I did not see this but I have seen it when runn=
ing my  application.  I reorganized by schema to try to get the locks to be=
 acquired in the same order all the time but I  could not get it to work.  =
Even if I could get this to work it would not solve the performance issue.


I saw a discussion on this from March:

http://archives.postgresql.org/pgsql-hackers/2002-03/msg01156.php

These does not seem to be a resolution to this yet.

Are there any plans to fix this soon?  This is a serious problem for us.

Thanks

Brian Walker