postgresql - 终止挂起的查询(事务中空闲)

Question

我正在使用带有 Crane Postgres 选项的 Heroku，当我的本地机器崩溃时，我正在从我的本地机器对数据库运行查询。如果我跑

select * from pg_stat_activity

其中一个条目有

<IDLE> in transaction

在 current_query_text 列中。

因此，我无法删除被终止的查询正在写入的表。我试过使用 pg_cancel_backend(N) 并返回 True 但似乎没有任何反应。

我怎样才能终止这个进程，以便我可以删除表？

Answer 1

这是一个通用的 PostgreSQL 答案，并不特定于 Heroku

---

(这个问题的简单愚蠢的答案可能是...只是重新启动 postgresql。假设这是不可取的或不是一个选项...)

通过运行此 sql 查找 PID:

SELECT pid , query, * from pg_stat_activity
  WHERE state != 'idle' ORDER BY xact_start;

(查询可能需要根据 postgres 的版本进行修改——最终，只需从 pg_stat_activity 中选择 *)。您会在第一(左)列中找到 pid，第一(上)行可能是您想要终止的查询。我假设下面的 pid 是 1234。

您可以通过 SQL 取消查询(即没有 shell 访问权限)，只要它是您的或者您有 super 用户访问权限:

select pg_cancel_backend(1234);

这是取消 1234 查询的“友好”请求，运气好的话它会在一段时间后消失。如果需要，以下是更多的“硬终止”命令，这可能会导致它更快地取消:

select pg_terminate_backend(1234);

如果您有 shell 访问权限和 root 或 postgres 权限，您也可以从 shell 执行此操作。要“取消”，可以这样做:

kill -INT 1234

要“终止”，只需:

kill 1234

不要:

kill -9 1234

...这通常会导致整个 postgres 服务器崩溃，那么您不妨重新启动 postgres。 Postgres 非常健壮，所以数据不会被破坏，但我建议在任何情况下都不要使用“kill -9”:-)

---

持久的“事务空闲”通常意味着事务未通过“提交”或“回滚”终止，这意味着应用程序存在错误或未正确设计以与事务数据库一起使用。应避免长时间的“事务空闲”，因为它也会导致严重的性能问题。