BUG #5940: Creating a foreign key on an empty table locks table - Mailing list pgsql-bugs

From Adriaan
Subject BUG #5940: Creating a foreign key on an empty table locks table
Date
Msg-id 201103220950.p2M9oTLW088719@wwwmaster.postgresql.org
Whole thread Raw
List pgsql-bugs
The following bug has been logged online:

Bug reference:      5940
Logged by:          Adriaan
Email address:      adriaanvk@gmail.com
PostgreSQL version: 8.4 / 9.0
Operating system:   Ubuntu
Description:        Creating a foreign key on an empty table locks table
Details:

I am using creation of table during the runtime of my application. This
gives some locking issues which are actually not needed. Let me describe
what i am doing.

1. Create a table A
2. Create a table B
3. Insert a record in A

4. start transaction t1
5. select a record from A  -> read lock on A
(the proces is not starting another parallel trans:)
6. in another transaction t2 create a foreign key from B to A.
--> this is causing a lock on table A, thus causing a hanging lock

I think the lock during the creation on foreign key is not needed since B
has no records and since the table definition of A is NOT changed.

Is it possible to remove the lock during creation of foreign keys in such
situations so the database is more robust for runtime table alters?

Test queries:
1. create table A (field1 int primary key);
2. create table B (field2 int);
3. insert into A (field1) VALUES (3);
4. start transaction; select * from A where field1=3;
in another shell start another trans:
5. alter table B add constraint "x" foreign key (field2) references A
(field1) on update no action
--> hanging lock now, since waiting for query 4

btw: mention that database engines lik mssql are very flesible in such
scenarios, which makes it feeling robust.

pgsql-bugs by date:

Previous
From: "Craig Sacco"
Date:
Subject: BUG #5938: PostgreSQL Installer outputs log file with superuser password in clear text
Next
From: "Jonathan Brinkman"
Date:
Subject: Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change