The following bug has been logged online:

Bug reference: 1998
Logged by: Dmitry Panov
Email address: dmitry (AT) tula (DOT) ru
PostgreSQL version: 8.0.4
Operating system: Linux
Description: transaction locks parent record when it shouldn't
Details:

Hi,

I discovered that inserting a child record locks parent record so that if
another translation tries to insert another child record which references
the same parent. This can be illustrated by a simple test case:

create table testparent (id integer, constraint testparent_pk primary key
(id));

create table testchild (parent_id integer, a varchar, constraint
testchild_fk foreign key (parent_id) references testparent(id) on delete
cascade);

insert into testparent values (1);
insert into testparent values (2);

then run 2 transactions in parallel:
TRANSATION 1:
begin;
insert into testchild values (1, '1');

TRANSACTION 2:
begin;
insert into testchild values (2, '22');

TRANSACTION 1:
insert into testchild values (2, '2'); <it hanges here>

TRANSACTION 2:
insert into testchild values (2, '22'); <deadlock, transaction aborted>

I believe it's not necessary to lock the parent record to maintain the read
commited isolation level. This test case works fine in Oracle and Mysql
4.1/InnoDB.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dmitry Panov wrote:

This is fixed in 8.1.

--
Alvaro Herrera Architect, http://www.EnterpriseDB.com
"Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq