BUG #5326: The 2nd update of a table which has foreign keys is blocked. - Mailing list pgsql-bugs

From Sadao Hiratsuka
Subject BUG #5326: The 2nd update of a table which has foreign keys is blocked.
Date
Msg-id 201002150441.o1F4fMms013573@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #5326: The 2nd update of a table which has foreign keys is blocked.
List pgsql-bugs
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,

pgsql-bugs by date:

Previous
From: Gregory Kotsaftis
Date:
Subject: Re: Possible bug with BYTEA and JDBC
Next
From: Zdenek Kotala
Date:
Subject: Re: BUG #5318: Inconsistent PHP pg_result_error_field PGSQL_DIAG_SOURCE_FUNCTION across instalations