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


Sat, 16 Dec 2000 23:03:50 +0600 (NS)
Max Khon <fjoe@iclub.nsu.ru>
locking bug?

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
> 


Sat, 16 Dec 2000 18:05:50 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: locking bug?