如何自动终止PostgreSQL中运行超2小时的查询？含非触发器方案咨询

你写的触发器完全不适用当前需求：行级触发器是在表执行增删改操作时触发的，和定时终止长时间查询的场景完全不搭。而且你的函数里还有语法逻辑错误，比如var_pid未定义，也没有循环处理查到的所有超时查询进程ID。

下面给你两种可行的实现方案，包括无需自定义函数的方式：

一、自定义函数+定时任务方案（灵活可控） #

先写一个能正确终止超时查询的函数：

CREATE OR REPLACE FUNCTION stop_long_queries()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
BEGIN
    -- 遍历所有符合条件的查询进程
    FOR rec IN
        SELECT pid
        FROM pg_stat_activity
        WHERE (now() - query_start) > interval '120 minutes'
          AND pid != pg_backend_pid()  -- 避免终止当前执行清理任务的进程
          AND state = 'active'  -- 只终止正在运行的查询
    LOOP
        PERFORM pg_cancel_backend(rec.pid);
        -- 如果需要强制终止（pg_cancel_backend无效时），可以替换为pg_terminate_backend(rec.pid);
    END LOOP;
END;
$$;

然后通过定时任务周期性执行这个函数：

方式1：用PostgreSQL的pg_cron扩展（推荐） #

-- 先安装pg_cron（如果未安装）
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 设置每10分钟执行一次清理任务（可根据需求调整执行频率）
SELECT cron.schedule('stop-long-queries', '*/10 * * * *', 'SELECT stop_long_queries();');

方式2：用操作系统定时任务（比如Linux的crontab） #

*/10 * * * * psql -U 你的用户名 -d 目标数据库名 -c "SELECT stop_long_queries();"

二、无需自定义函数的实现方式 #

直接用定时任务执行原生SQL语句，省去写函数的步骤：

用pg_cron直接执行SQL #

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule('stop-long-queries-direct', '*/10 * * * *', $$
    SELECT pg_cancel_backend(pid)
    FROM pg_stat_activity
    WHERE (now() - query_start) > interval '120 minutes'
      AND pid != pg_backend_pid()
      AND state = 'active'
$$);

用操作系统定时任务直接执行SQL #

*/10 * * * * psql -U 你的用户名 -d 目标数据库名 -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE (now() - query_start) > interval '120 minutes' AND pid != pg_backend_pid() AND state = 'active';"

额外补充：全局查询超时配置 #

如果你的需求是所有查询都不能超过2小时，可以直接修改PostgreSQL的全局配置参数statement_timeout，不需要任何定时任务：

-- 临时生效（数据库重启后失效）
SET statement_timeout = '120min';

-- 永久生效，修改postgresql.conf后重启数据库
statement_timeout = 7200000  -- 单位为毫秒，120分钟=7200秒=7200000毫秒

注意：这个参数会作用于所有查询，如果有需要长时间运行的合法任务（比如批量数据导入），请谨慎使用。

内容的提问来源于stack exchange，提问作者Raju