BUG #5940: Creating a foreign key on an empty table locks table
От | Adriaan |
---|---|
Тема | BUG #5940: Creating a foreign key on an empty table locks table |
Дата | |
Msg-id | 201103220950.p2M9oTLW088719@wwwmaster.postgresql.org обсуждение исходный текст |
Список | 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 по дате отправления: