I've got a table "aaa" that is not so large. It's under 10'000 rows. However read procedures about this table is extremely frequent.
Whenever I attempt to produce a new table "bbb" with foreign key pointing to "aaa". The operation locks, and reading through "aaa" isn't feasible. The query also never appears to complete.
ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4" FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY DEFERRED;
The present workaround would be to create any new table at off-peak hrs, e.g. night time after restarting the db.
I must determine if there's any proper solution of the. Is an problem affecting all relational databases? My db is PostgreSQL 8.3.
ALTER TABLE requires a table lock, which means you can't make use of the table for reads. Your condition seems like an issue with locks, check pg_stat_activity to determine what's going on.
Offtopic: So why do you restart your database after night time? We never restart the database, there's no need for this.