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

adding foreign key constraint locks up table

From: kakarukeys <kakarukeys(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: adding foreign key constraint locks up table
Date: 2010-12-28 07:08:45
Message-ID: 75218696-61be-4730-89f6-dd6058fa9eda@a28g2000prb.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

Responses

pgsql-performance by date

Next:From: LewDate: 2010-12-28 13:34:30
Subject: Re: adding foreign key constraint locks up table
Previous:From: Scott MarloweDate: 2010-12-28 06:11:43
Subject: Re: How to turn autovacuum prevent wrap around run faster?

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