adding foreign key constraint locks up table

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_3开发者_开发百科942a46c6ab2c0b4" 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.

---

ALTER TABLE needs a table lock, so you can't use the table for reads. Your problem sounds like a problem with locks, check pg_stat_activity to see what is going on.

Offtopic: Why do you restart your database after midnight? We never restart the database, there is no need for it.