PostgreSQL查询超时设置为何被忽略？如何正确配置？

问题背景 #

使用jOOQ对PostgreSQL执行耗时较长的查询时，尝试多种方式设置查询超时均被忽略，查询始终在1分钟（数据库默认超时）后失败，报错：

canceling statement due to statement timeout

尝试的jOOQ设置方式：

1. 通过Settings全局设置超时：

DSL.using(dataSource, SQLDialect.POSTGRES, new Settings().withQueryTimeout(600))
    .deleteFrom(...)
    ...
    .execute();

2. 针对单个查询设置超时：

DSL.using(dataSource, SQLDialect.POSTGRES)
    .deleteFrom(...)
    ...
    .queryTimeout(6000)
    .execute();

3. 事务中设置超时：

DSLContext transactionContext = 
    DSL.using(dataSource, SQLDialect.POSTGRES, new Settings().withQueryTimeout(600));
transactionContext.transaction(configuration ->
{
    DSL.using(configuration).deleteFrom(...)
        ...
        .execute();
});

为排除jOOQ影响，直接使用JDBC测试：

import org.apache.commons.dbcp2.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
...
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(dbURL, username, password);
Connection connection = connectionFactory.createConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select pg_sleep(80)");
preparedStatement.setQueryTimeout(120);
preparedStatement.execute();

测试仍在1分钟后出现相同错误，确认问题与jOOQ无关（使用的是PgConnection和PgPreparedStatement）。

问题原因 #

报错信息中的statement_timeout是PostgreSQL服务端的参数，而非客户端JDBC的超时设置。默认情况下，你的数据库可能全局配置了1分钟的statement_timeout，此时即使通过JDBC/jOOQ设置了客户端超时，服务端会先触发超时并终止语句，导致客户端的设置被忽略。

JDBC的setQueryTimeout是客户端层面的超时控制（由驱动在客户端检测语句执行时间，超时后发送取消请求），但如果服务端本身有更短的statement_timeout配置，会优先生效。

正确设置查询超时的方法 #

1. 修改服务端全局配置 #

编辑PostgreSQL的配置文件postgresql.conf，调整statement_timeout参数：

# 设置为10分钟（单位：毫秒）
statement_timeout = 600000

修改后需要重启PostgreSQL服务生效。

2. 通过JDBC连接URL设置会话级超时 #

在连接URL中添加options参数，指定会话级的statement_timeout，无需修改全局配置：

String dbURL = "jdbc:postgresql://localhost:5432/dbname?options=-c%20statement_timeout=600000";

这里%20是空格的URL转义符，600000代表10分钟（单位毫秒），也可以用PostgreSQL的时间格式，比如'10min'（注意转义单引号：%2710min%27）。

3. 会话执行前动态设置超时 #

在执行目标查询前，先执行SET语句修改当前会话的statement_timeout：

// 使用jOOQ
DSL.using(configuration).execute("SET statement_timeout = 600000");
// 或者直接JDBC
connection.createStatement().execute("SET statement_timeout = 600000");

// 再执行耗时查询
DSL.using(configuration).deleteFrom(...).execute();

4. 客户端与服务端超时配合 #

确保客户端的超时设置（jOOQ的queryTimeout或JDBC的setQueryTimeout）不小于服务端的statement_timeout，避免服务端先触发超时。

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