Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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.

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group