如何找到阻止另一个查询的查询? [英] How to find the queries that is blocking another query?

问题描述

我正在尝试删除一张空桌子drop table temp; 查询永远运行.

I am trying to drop an empty table drop table temp; the query is running forever.

因此，我尝试查找可能阻止当前查询的其他查询.这是我尝试过的:

So, I tried to find other queries that could block the current query. Here is what I tried:

SELECT *
  FROM pg_locks l
  JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r'
 WHERE t.relname = 'temp';

此查询返回零结果

SELECT database, gid FROM pg_prepared_xacts;

这也没有给出任何结果.

This does not give any result as well.

该表为分区表，已成功删除该表的分区.

The table is a partitioned table, the partitions attached to the table has been deleted successfully already.

我什至无法获得 \d temp 的结果.

I am not even able to get result for \d temp.

发生了什么?我怎样才能解决这个问题?

What has happened? How can I get this fixed?

当我在本地尝试相同时，这不会发生.工作得很好.

This does not happen when I tried the same in local. Works perfectly fine.

推荐答案

在运行DROP TABLE之前，执行

SELECT pg_backend_pid();

这会告诉您后端进程 ID.然后在同一个数据库会话中运行 DROP TABLE .

That will tell you the backend process ID. Then run DROP TABLE in the same database session.

然后，当DROP TABLE挂起时，开始一个新的会话并运行

Then, when DROP TABLE is hanging, start a new session and run

SELECT pg_blocking_pids(<backend PID>);

然后您就知道哪些会话在阻止您，并且可以杀死它们或采取较少破坏性的措施来摆脱它们.

Then you know which sessions are blocking you, and can kill them or take less disruptive measures for getting rid of them.

这篇关于如何找到阻止另一个查询的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！