Postgres Pro
Downloads
Home   >   mailing lists

Re: adding foreign key constraint locks up table - Mailing list pgsql-performance

From Gurjeet Singh
Subject Re: adding foreign key constraint locks up table
Date
Msg-id AANLkTimuC13j-cvPr57k6PDCv1=BUMZLb90qs_gznbmh@mail.gmail.com
Whole thread Raw
In response to adding foreign key constraint locks up table  (kakarukeys <kakarukeys@gmail.com>)
Responses Re: adding foreign key constraint locks up table
List pgsql-performance
Tree view
On Tue, Dec 28, 2010 at 2:08 AM, kakarukeys <kakarukeys@gmail.com> wrote:
I have a table "aaa" which is not very big. It has less than 10'000
rows. However read operations on this table is very frequent.

Whenever I try to create a new table "bbb" with foreign key pointing
to "aaa". The operation locks, and reading "aaa" is not possible. The
query also never seems to finish.

ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4"
FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY
DEFERRED;

The current workaround is to create any new table at off-peak hours,
e.g. midnight after restarting the db.

I would like to know if there's any proper solution of this. Is this
an issue affecting all relational databases? My db is PostgreSQL 8.3.


how many rows does "bbb" have? And what are the data types of column aaa.id and bbb.topic_id?

Creating a foreign key should not lock out aaa against reads. Can you provide the output of the following:

select relname, oid from pg_class where relname in ( 'aaa', 'bbb' );

select * from pg_locks; -- run this from a new session when you think "aaa" is locked by foreign key creation.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

pgsql-performance by date:

Previous
From: kakarukeys
Date:
Subject: adding foreign key constraint locks up table
Next
From: "Kevin Grittner"
Date:
Subject: Re: adding foreign key constraint locks up table