The following bug has been logged online:
Bug reference: 5326
Logged by: Sadao Hiratsuka
Email address: sh2@pop01.odn.ne.jp
PostgreSQL version: 8.4.2
Operating system: Linux x86
Description: The 2nd update of a table which has foreign keys is
blocked.
Details:
The 2nd update of a table which has foreign keys is blocked.
<test case 1>
create table parent (k int primary key, d varchar(10));
create table child (k int primary key, f int, d varchar(10),
constraint child_fk1 foreign key (f) references parent (k));
insert into parent values (1, 'a');
insert into parent values (2, 'b');
insert into child values (11, 1, 'aa');
insert into child values (12, 2, 'bb');
client1> begin;
client1> update parent set d = 'a2' where k = 1;
client2> begin;
client2> update child set d = 'aa2' where k = 11; -- ok
client2> update child set d = 'aa3' where k = 11; -- blocked
Especially, a deadlock occurs by the following sequence.
<test case 2>
client1> begin;
client1> update parent set d = 'a2' where k = 1;
client2> begin;
client2> update parent set d = 'b2' where k = 2;
client2> update parent set d = 'a3' where k = 1; -- blocked
client1> update child set d = 'bb2' where k = 12; -- ok
client1> update child set d = 'bb3' where k = 12; -- deadlock
Is this PostgreSQL's bug? or right behavior?
Thanks,
