BUG #5940: Creating a foreign key on an empty table locks table

From: "Adriaan" <adriaanvk(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5940: Creating a foreign key on an empty table locks table
Date: 2011-03-22 09:50:29
Message-ID: 201103220950.p2M9oTLW088719@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 5940
Logged by: Adriaan
Email address: adriaanvk(at)gmail(dot)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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Jonathan Brinkman 2011-03-22 12:56:47 Re: TO_CHAR(timestamptz,datetimeformat) wrong after DST change
Previous Message genamiga 2011-03-22 07:07:35 Re: BUG #5800: "corrupted" error messages (encoding problem ?)