postgresql Heroku Postgres - 终止挂起的查询（事务空闲）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11291456/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Heroku Postgres - terminate hung query (idle in transaction)
提问by alan
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
我正在使用带有 Crane Postgres 选项的 Heroku，当我的本地机器崩溃时，我正在从我的本地机器对数据库运行查询。如果我跑
select * from pg_stat_activity
one of the entries has
其中一个条目有
<IDLE> in transaction
in the current_query_text column.
在 current_query_text 列中。
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.
因此，我无法删除终止的查询正在写入的表。我试过使用 pg_cancel_backend(N) 并且它返回 True 但似乎什么也没发生。
How can I terminate this process so that I can drop the table?
如何终止此过程以便我可以删除表？
回答by tobixen
This is a general Postgres answer, and not specific to heroku
这是一个通用的 Postgres 答案，而不是特定于 heroku
(The simple-stupid answer to this question may be ... just restart postgresql. Assuming that's not desirable or not an option ...)
（这个问题的简单愚蠢的答案可能是......只需重新启动 postgresql。假设这不是可取的或不是一个选项......）
Find the PID by running this sql:
通过运行此 sql 查找 PID：
SELECT pid , query, * from pg_stat_activity
WHERE state != 'idle' ORDER BY xact_start;
(The query may need mending dependent of the version of postgres - eventually, just select * from pg_stat_activity). You'll find the pid 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.
（查询可能需要根据 postgres 的版本进行修正 - 最终，只需从 pg_stat_activity 中选择 *）。您会在第一（左）列中找到 pid，第一（顶）行可能是您要终止的查询。我假设pid为1234以下。
You may cancel a query through SQL (i.e. without shell access) as long as it's yours or you have super user access:
你可以通过 SQL 取消查询（即没有 shell 访问），只要它是你的或者你有超级用户访问权限：
select pg_cancel_backend(1234);
This is the "soft" way ... the query won't disappear immediately. If that won't work, use this instead:
这是“软”方式......查询不会立即消失。如果这不起作用，请改用它：
select pg_terminate_backend(1234);
If you have shell access and root or postgres permissions you can also do it from the shell:
如果您有 shell 访问权限和 root 或 postgres 权限，您也可以从 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 :-)
...这通常会导致整个 postgres 服务器着火，那么您不妨重新启动 postgres。Postgres 非常健壮，所以数据不会被破坏，但我建议不要在任何情况下使用“kill -9”:-)
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.
持久的“事务空闲”通常意味着事务没有因“提交”或“回滚”而终止，这意味着应用程序有问题或设计不当，无法与事务数据库一起使用。应该避免长时间的“事务空闲”，因为它也会导致严重的性能问题。
回答by evgenek
回答by davidfrancisco
You can install the heroku-pg-extrasadd-on and run the following command to get the PID:
您可以安装heroku-pg-extras附加组件并运行以下命令来获取 PID：
heroku pg:locks --app <your-app>
Then just do:
然后就这样做：
heroku pg:kill <pid> --app <your-app>
NOTE: --forceoption can be used to issue pg_terminate_backend which drops the entire connection for that query.
注意：--force选项可用于发出 pg_terminate_backend，它会丢弃该查询的整个连接。
If heroku pg:locksdoes not list anything, try heroku pg:ps.
如果heroku pg:locks没有列出任何内容，请尝试heroku pg:ps。
For more information check out:
https://devcenter.heroku.com/articles/heroku-postgresql#pg-ps-pg-kill-pg-killall
有关更多信息，请查看：https:
//devcenter.heroku.com/articles/heroku-postgresql#pg-ps-pg-kill-pg-killall
回答by codersofthedark
We can use the following to achieve it in a single query:
我们可以使用以下方法在单个查询中实现它：
SELECT pg_cancel_backend(pid), pg_terminate_backend(pid) FROM pg_stat_activity WHERE state != 'idle';
