添加外键约束锁定表
我有一张桌子“aaa”，不是很大。它的行数少于 10'000。然而对该表的读操作非常频繁。
每当我尝试创建一个新表“bbb”，其外键指向“aaa”。操作锁定，并且无法读取“aaa”。查询似乎也永远不会完成。
ALTER TABLE "bbb" ADD CONSTRAINT "topic_id_refs_id_3942a46c6ab2c0b4" FOREIGN KEY ("topic_id") REFERENCES "aaa" ("id") DEFERRABLE INITIALLY DEFERRED;
当前的解决方法是在非高峰时间创建任何新表，例如重新启动数据库后的午夜。
我想知道是否有任何适当的解决方案。这是一个影响所有关系数据库的问题吗？我的数据库是 PostgreSQL 8.3。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
ALTER TABLE需要表锁，因此您不能使用该表进行读取。您的问题听起来像是锁的问题，请检查 pg_stat_activity 以了解发生了什么情况。题外话：为什么午夜过后要重启数据库？我们从不重新启动数据库，没有必要。
ALTER TABLEneeds 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.