do foreign key checks lock parent table ?

From: <swalker(at)iglou(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: do foreign key checks lock parent table ?
Date: 2002-04-03 00:14:18
Message-ID: Pine.LNX.4.33.0204021913321.23842-100000@12-220-136-82.client.insightBB.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message James Leigh 2002-04-03 00:45:03 Re: dropping large objects [ in 6.5 ]
Previous Message Stephan Szabo 2002-04-02 23:48:29 Re: Problem with referential integrity within functions