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.