If you try the example I have below - the second transaction will block
waiting for the first one to complete. This doesn't seem correct to me.
Is this a known problem / feature? If I create the table w/ a deferrable
intially deferred foreign key - I don't get the problem. But this is a
very unexpected default behavior ( At least to me :) ).
create table parent
(key integer primary key,
description varchar(50) not null);
create table child
(key integer primary key,
child_key integer not null references parent(key),
description varchar(50) not null);
insert into parent values (1,'Parent Record #1');
insert into child values (101,1,'Child Record #1');
insert into child values (102,1,'Child Record #2');
// From one psql
begin;
update child set child_key = 1 where key = 101;
//second session
begin;
update child set child_key = 1 where key = 102;