The following bug has been logged online:
Bug reference: 3708
Logged by: Maksym Sobolyev
Email address: sobomax@sippysoft.com
PostgreSQL version: 8.0.14
Operating system: FreeBSD 6.2-RELEASE
Description: Ongoing transactin that involves the table containing
foreign key blocks updates of referenced table
Details:
For some reason ongoing INSERT transaction that involves the table
containing foreign key blocks updates of the table being referenced even in
the case when those updates on referenced table don't touch the key value
itself. There is no obvious reason for such behavior.
Let's consider we have the following simple schema:
create table a (a_idx bigint, data int);
alter table a add constraint a_pkey primary key(a_idx);
create table b (b_idx bigint, a_idx bigint);
alter table b add constraint "$1" foreign key (a_idx) references a(a_idx);
insert into a values (1, NULL);
Now, let's start two parallel connections to the DB, say in two shell
windows:
---- Session 1 begins ----
BEGIN;
insert into b values (1, 1);
---- Session 1 stays idle ----
---- Session 2 begins ----
update a set data=5;
---- Session 2 blocks ----
As you can see at the end the UPDATE in session 2 is blocked until
transaction in session 1 either commits or rolls back. However, as I said
there is no reason for this blockage since update in the session 2 doesn't
involve foreign key a_idx, so that whatever outcome of the transaction in
session 1 would be there is no interdependency and update in session 2
should be allowed to proceed.
-Maxim