Postgres:删除外键约束查询卡住 [英] Postgres: drop foreign key constraint query getting stuck

问题描述

我想从架构中删除所有外键约束.我成功地从大多数表中删除了约束，但是在其中的一些表中，删除外键约束查询被卡住了.

I wanted to remove all foreign key constraints from a schema. I was successful in dropping constraints from most of the tables but in few of them drop foreign key constraint query is getting stuck.

ALTER TABLE table_name DROP CONSTRAINT fkey_name;

我尝试了截断级联，但是它也被卡住了.我从两个表中手动删除了所有行.仍然被卡住.

I tried truncate cascade but it was also getting stuck. I deleted all rows from both the tables manually. Still getting stuck.

卡住是指查询继续运行很长时间，即使表为空，也不会出现任何错误消息.

Edits: By getting stuck I mean query continues running for long time without any error message even though tables are empty.

推荐答案

使用以下方法检查是否有死锁

Check for any dead locks using

SELECT * FROM pg_stat_activity;

如果有的话，杀死并在sql下运行，然后使用

If any then kill and run below sql,and then drop using

SELECT pg_terminate_backend(pid);

如果未解决，请检查是否有任何虚拟交易

If Not solved check for any virtual transaction

SELECT database, gid FROM pg_prepared_xacts;

使用回滚

回滚准备好的'gid';

ROLLBACK PREPARED 'gid';

这篇关于Postgres:删除外键约束查询卡住的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！