This is because the first transaction has a lock on the
row in foo due to the references constrain, it currently
grabs the lock to prevent the pk row from being
removed after we tested its existance but before the transaction
closed. There's been talk about using some kind of dirty reads to drop
the need for the lock, but that hasn't been done yet.
On Sat, 16 Dec 2000, Max Khon wrote:
> hi, there!
>
> test=> create table foo(id int primary key);
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey'
> for table 'foo'
> CREATE
> test=> insert into foo values(1);
> INSERT 88959 1
> test=> create table bar(id int references foo);
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> test=> begin;
> BEGIN
> test=> insert into bar values(1);
> INSERT 88976 1
> test=>
>
> after that (we did not issued commit or rollback on the connection)
> in another psql:
>
> test=> begin;
> BEGIN
> test=> insert into bar values(1);
> ...and this transaction locks up until we finish first transaction
>
> if we insert different values no locking occur.
> this happens on both postgresql 7.03 and 7.1-beta1
>
> /fjoe
>
