终止挂起的查询（在事务中处于空闲状态）

4 浏览
0 Comments

终止挂起的查询（在事务中处于空闲状态）

我正在使用Heroku的Crane Postgres选项，并且在本地机器上对数据库运行查询时，我的本地机器崩溃了。如果我运行

select * from pg_stat_activity

其中一个条目在current_query_text列中有 in transaction。

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

如何终止这个进程，以便我可以删除表？

0

3 答案

0 Comments

Terminate hung query (idle in transaction)问题的原因是由于数据库中的查询进程被挂起，无法终止。解决方法是使用Heroku平台的heroku-pg-extras附加组件来获取进程的PID，并使用heroku pg:kill命令终止该进程的连接。

首先，安装heroku-pg-extras附加组件，并运行以下命令获取PID：

heroku pg:locks --app <your-app>

然后，执行以下命令终止进程的连接：

heroku pg:kill <pid> --app <your-app>

需要注意的是，可以使用--force选项来使用pg_terminate_backend命令终止整个查询的连接。

如果使用heroku pg:locks命令没有列出任何内容，可以尝试使用heroku pg:ps命令。

更多信息，请参考以下链接：

https://devcenter.heroku.com/articles/heroku-postgresql#pg-ps-pg-kill-pg-killall

但是，如果在导入过程中计算机发生硬件冻结，导致无法终止PID，以上方法可能无效。

0
0 Comments

解决挂起查询 (事务中空闲) 问题的方法是使用以下代码：select pg_terminate_backend(pid int)。这是比通过系统杀死进程更"干净"的解决方案。你可以在这里找到更多相关信息：链接。请在你的回答中添加如何获取你的 pid 的说明。

0
0 Comments

在使用PostgreSQL时，有时会遇到查询长时间挂起的情况，也就是所谓的"Idle in transaction"。这种情况通常是由于事务没有被正确终止（即未执行"commit"或"rollback"），这可能是应用程序存在缺陷或不正确地设计了与事务型数据库的交互。长时间的"Idle in transaction"应该尽量避免，因为它还会导致严重的性能问题。

要解决这个问题，可以通过以下方式取消挂起的查询：

1. 通过SQL语句取消查询，只要是你自己的查询或者你拥有超级用户权限：

   SELECT pg_cancel_backend(1234);

这是一个“友好”的取消请求，如果运气好的话，查询可能会在一段时间后消失。如果需要更快地取消查询，可以使用以下更强制的终止命令：

   SELECT pg_terminate_backend(1234);

2. 如果你具有Shell访问权限和root或postgres权限，也可以通过Shell来取消查询：

   kill -INT 1234

或者简单地执行：

   kill 1234

注意，不要执行以下命令：

   kill -9 1234

这通常会导致整个PostgreSQL服务器崩溃，如果出现这种情况，最好还是重启PostgreSQL。虽然PostgreSQL非常健壮，数据不会被损坏，但我仍然建议不要使用"kill -9"命令。

需要注意的是，使用pg_cancel_backend可能会取消进程，但查询仍然会在pg_stat_activity中显示一段时间。

至于为什么会出现长时间的"Idle in transaction"连接对性能有害的问题，虽然没有进行具体的基准测试，但我曾经见过这种情况导致性能严重下降的情况。这也取决于写入活动的程度，如果数据库是一个纯读取的数据库，那可能并不重要。此外，长时间的事务还会导致VACUUM无法删除长时间事务可见的"dead"行。

总之，当遇到"Terminate hung query (idle in transaction)"的问题时，可以通过取消查询或者终止进程的方式来解决。同时，为了避免出现长时间的"Idle in transaction"，在应用程序设计中应该注意正确处理事务的终止。

0