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.
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 ?) |