locking bug?
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
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:
Show quoted text
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 transactionif we insert different values no locking occur.
this happens on both postgresql 7.03 and 7.1-beta1/fjoe