PostgreSQL - 为什么执行DROP VIEW命令会卡住？

Question

PostgreSQL - 为什么执行DROP VIEW命令会卡住？

23

我想执行一个简单的 DROP VIEW ... 但它卡住了。

我运行了这个查询 SELECT * FROM pg_locks WHERE NOT granted，这个查询是从这个页面 Lock Monitoring 中获取的。

然而，他们建议的以下查询没有返回结果：

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

我现在应该去哪里看？

- Stephan

这听起来像是一个数据库引擎问题；您尝试过在涉及视图的表上执行VACUUM ANALYZE和/或REINDEX吗？另外，重新启动Postgres呢？ - Gnudiff

@Gnudiff 我不想只是重启Postgres，因为我不会知道挂起的根本原因。 - Stephan

在我的情况下，挂起是因为我有一个运行中的REST服务（FastAPI），使用我的postgres数据库。我只需对我的REST服务进行重新加载，我的事务就可以顺利完成。也许重新加载使用您的postgres数据库的服务，或重新加载postgres本身可以解决您的挂起问题。 - Franco Gil

3个回答

13

我遇到了类似的问题，但是接受的答案并不能解决我的问题，因为我没有管理员权限来终止任何进程。相反，这是我解决问题的方法：

执行 SELECT * FROM pg_stat_activity; 查询 PostgreSQL 活动的统计信息。
在 query 列中查找从该视图中读取的查询。您可以通过仅查看与您的用户名相关的行（使用 username 列）或者如果您知道问题出现的时间，则使用 query_start 来缩小搜索范围。可能会有多个与您不想要的视图相关联的行。
从上一步中的行中识别所有 pid，并将它们逐个插入到 SELECT pg_terminate_backend(<pid>); 中（而不是 <pid>），然后运行它们。

现在，您应该能够删除您的视图了。

请注意，当您使用 pg_terminate_backend() 终止后端进程时，可能会遇到一些错误。原因是终止某些进程可能会自动结束其他进程。因此，某些已识别的 PID 在该时刻可能无效。

- Borhan Kazimipour

0

作为总结，这个评论中的解决方案对我有效：

步骤1：找到pid：

select * from pg_stat_activity 
  where pid in 
    (select pid from pg_locks 
      where relation = 
        (select relation from pg_locks where not granted));

步骤2：杀死pid：

kill -9 pid

- Path2Perfection

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Stephan · Accepted Answer

最终我弄清了问题所在。以下是找到根本原因的步骤：

解决方案

步骤 1：列出未授予的已请求锁

select * from pg_locks where not granted;

在我的情况下，尝试使用模式AccessExclusiveLock锁定我想要删除的视图未被授权。这就是为什么我的DROP VIEW...挂起的原因。

第二步：找出哪些其他进程持有冲突的锁。

select * from pg_locks where relation = <oid_of_view>

在这里，我列出了所有锁定或试图锁定我的视图的进程。我发现两个进程，一个是想要删除该视图的进程，另一个是......

步骤三：找出其他进程正在执行的操作

select xact_start,query_start,backend_start,state_change,state from pg_stat_activity where pid in (<list_of_other_process(es)_pid>);

在我的情况下，只有一个进程持有锁。令人惊讶的是，它的状态是：空闲事务

由于另一个进程处于空闲事务状态，我无法删除视图。我只需将其杀死即可解决问题。例如，如果procpid为8484，并且假设我的postgresql服务器在Linux上运行，则在shell中，我执行以下命令：

$ kill -9 8484

讨论

如果你遇到类似的问题，可以通过重现步骤1、2、3来快速找出问题所在。你可能需要自定义第二步以查找其他冲突进程。

PostgreSQL - 为什么执行DROP VIEW命令会卡住？

解决方案

步骤 1：列出未授予的已请求锁

步骤三：找出其他进程正在执行的操作

讨论

参考资料