CODEWITHSUNDEEP
sqlmysqldatabasepostgresqlmigration
kakarukeys
kakarukeys

Reputation: 23561

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_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.

Upvotes: 2

Views: 3385

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 127367

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.

Upvotes: 2

Related Questions