当会话或请求不再存在时，停止(长)在PostgreSQL中运行SQL查询?

I'm not sure where to start on addressing this issue but if I have a AJAX web application that sends requests to the server and runs long queries on the database (postgresql in my case), is there a way to stop or kill the queries if while still running, the user refreshes the page or closes the session...etc?

我不确定从哪里开始解决这个问题,但如果我有一个AJAX web应用程序向服务器发送请求和长时间运行的查询在数据库(postgresql就我而言),是否有办法停止或杀死查询如果同时运行,用户刷新页面或关闭会话…等?

2 个解决方案

#1

To stop the query:

停止查询:

SELECT pg_cancel_backend(procpid);

To kill the database connection:

要杀死数据库连接:

SELECT pg_terminate_backend(procpid);

To get an overview of the current transactions, to get the proced id's:

要获得当前事务的概述，请获取已证明的id:

SELECT * FROM pg_stat_activity;

http://www.postgresql.org/docs/current/interactive/functions-admin.html

#2

Considering your psql configured to run and connect to current dev database this one liner comes handy in development when testing complicated queries which can hung, just kills whatever runs:

考虑到您的psql被配置为运行并连接到当前的dev数据库，在测试可能挂起的复杂查询时，这一行代码在开发中非常有用，它只会终止任何运行:

If not configured properly - add flags for user/password/database

如果配置不正确——为用户/密码/数据库添加标志

psql -c "SELECT procpid FROM pg_stat_activity;" -t | xargs -n1 -I {} psql -c "SELECT pg_cancel_backend({})"

psql -c“从pg_stat_activity选择procpid;”-t | xargs -n1 -I {psql -c "SELECT pg_cancel_后端({})"

#1