Heroku Postgres - 终止挂起查询（闲置交易） [英] Heroku Postgres - terminate hung query (idle in transaction)

问题描述

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

  select * from pg_stat_activity 
  

其中一项有

 < IDLE> in current_query_text列中的交易
  

。

因此，我无法删除被终止的查询写入的表。我曾尝试使用pg_cancel_backend（N），它返回True，但似乎没有发生。

如何终止此过程，以便我可以删除表？

解决方案

这是一个普遍的答案，并非针对heroku。一般来说，取消查询可能需要用户具有超级用户访问权限，无论是postgres服务器还是postgres数据库 - 看起来像普通用户不会在不向DBA或系统管理员寻求帮助的情况下杀死他自己的查询进程。 em>

---

通过运行这个sql查找PID：

  SELECT procpid，* from pg_stat_activity 
 WHERE current_query<>'< IDLE>'ORDER BY xact_start; 
  

您会在第一列（左侧）找到procpid，第一列很可能是您想要终止的查询。

您可以通过SQL取消查询（即无需shell访问，但您可能需要超级用户访问数据库）：

  select pg_cancel_backend（1234）; 
  

这是软的方式...查询不会立即消失。如果你很着急，试试这个：

  select pg_terminate_backend（1234）; 
  

如果您有shell访问权限，您也可以从shell执行它：

  kill -INT 1234 
  

如果没有帮助，请使用：

 杀死1234 
  

$ b

不要：

  kill  - 9 1234 
  

...通常会导致整个postgres服务器熄火，然后你可以重新启动postgres。 Postgres非常强大，所以数据不会被破坏，但我建议不要在任何情况下使用kill -9; - ）

---

持久的闲置事务通常意味着事务没有以提交或回滚结束，这意味着应用程序有问题或者没有正确设计用于事务处理数据库。应避免持久的闲置交易，因为它也可能导致重大性能问题。

I'm using Heroku with the Crane Postgres option and I was running a query on the database from my local machine when my local machine crashed. If I run

select * from pg_stat_activity

one of the entries has

<IDLE> in transaction

in the current_query_text column.

As a result, I can't drop the table that was being written to by the query that was terminated. I have tried using pg_cancel_backend(N) and it returns True but nothing seems to happen.

How can I terminate this process so that I can drop the table?

解决方案

This is a general answer, and not specific to heroku. In general, cancelling a query may require that the user has superuser access, either to the postgres server or to the postgres database - it seems like a regular user can't kill his own query process without asking his DBA or sysadmin for help

---

Find the PID by running this sql:

SELECT procpid,* from pg_stat_activity 
WHERE current_query<>'<IDLE>' ORDER BY xact_start;

You'll find the procpid in the first (left) column, and the first (top) row is likely to be the query you'd like to terminate. I'll assume the pid is 1234 below.

You can cancel the query through SQL (i.e. without shell access, but you probably need superuser access to the database):

select pg_cancel_backend(1234);

This is the "soft" way ... the query won't disappear immediately. If you're in a hurry, try this one:

select pg_terminate_backend(1234);

If you have shell access you can also do it from the shell:

kill -INT 1234

If that doesn't help, use:

kill 1234

DO NOT:

kill -9 1234

... that will often result in the the whole postgres server going down in flames, then you may as well restart postgres. Postgres is pretty robust, so the data won't be corrupted, but I'd recommend against using "kill -9" in any case ;-)

---

A long-lasting "idle in transaction" often means that the transaction was not terminated with a "commit" or a "rollback", meaning that the application is buggy or not properly designed to work with transactional databases. Long-lasting "idle in transaction" should be avoided, as it also can cause major performance problems.

这篇关于Heroku Postgres - 终止挂起查询（闲置交易）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！