关于postgresql:解决PostgreSQL-执行超时的情况

应用背景

最近在应用 PostgreSQL 的时候，在执行一些数据库事务的时候，先后呈现了 statement timetout 和 idle-in-transaction timeout 的问题，导致数据库操作失败。

经钻研查找，PostgreSQL 有对于 SQL 语句执行超时和事务执行超时的相干配置，而默认超时工夫是 10000 毫秒，即 10 秒钟的工夫，这样会导致执行工夫稍长的工作执行失败。能够通过批改 PostgreSQL 服务器配置文件的形式批改默认配置。

参数阐明

statement_timeout
statement_timeout 在 postgresql 被用来管制语句执行时长，单位是 ms。
$ vi postgresql.conf

默认是 0，示意语句能够始终执行上来。

如果设置为 10000，那就意味着语句最多能够执行 10000ms = 10s。

倡议设置为 0，禁用该参数。

1idle_in_transaction_session_timeout

PostgreSQL 9.6 版本开始反对主动查杀超过指定工夫的 idle in transaction 闲暇事务连贯，用于清理利用代码中遗记敞开已开启的事务，或者零碎中存在僵死过程等。

idle_in_transaction_session_timeout 在 postgresql 被用来管制事务执行时长，单位是 ms。

$ vi postgresql.conf #idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled

默认是 0，示意语句能够始终执行上来。超时会报 FATAL: terminating connection due to idle-in-transaction timeout。

批改办法

查找配置

通过命令查找到 postgresql 配置文件的地位，用 vi 进行编辑。

2find / -name “postgresql.conf”vi /var/lib/pgsql/9.6/data/postgresql.conf

批改参数

进入 vi 编辑界面，能够通过 vi 查找命令定位到相干参数, 批改成适合的工夫，保留退出。

：/statement_timeout

重启配置

通过以下命令，查找 pg_ctl 的地位，而后执行 pg_ctl reload 从新加载配置。

2find / -name “pg_ctl”/usr/pgsql-9.6/bin/pg_ctl reload

PG_CTL 用法

启动服务器

启动服务器：

1$ pg_ctl start

启动服务器的一个例子，等到服务器启动了才退出：

1$ pg_ctl -w start

服务器应用 5433 端口，而且不带 fsync 运行，应用：

1$ pg_ctl -o “-F -p 5433” start

进行服务器

1$ pg_ctl stop

应用 -m 选项进行服务器容许用户管制如何敞开后端。

重启服务器

这个命令简直等于先进行服务器而后再启动它，只不过 pg_ctl 保留并从新应用上一次运行服务器的命令行参数。重启服务器的最简略的办法是：

1$ pg_ctl restart

重启服务器，期待其进行和重启：

1$ pg_ctl -w restart

应用 5433 端口重启并且重启后敞开 fsync：

1$ pg_ctl -o “-F -p 5433” restart

显示服务器状态

上面是来自 pg_ctl 的状态输入的例子：

$ pg_ctl statuspg_ctl: server is running (pid: 13718)
Command line was:
/usr/local/pgsql/bin/postgres ‘-D’ ‘/usr/local/pgsql/data’ ‘-p’ ‘5433’ ‘-B’ ‘128’

这就是在 restart 模式中被调用的命令行。

补充：PostgreSQL 设置单条 SQL 的执行超时 – 防雪崩

背景

设置单条 SQL 的执行超时，防雪崩。

通常来说能够在 SQL 发动前设置事务级超时参数，SQL 执行完结，重置。（如果 SQL 异样退出，会主动重置事务级参数）

例子

begin;
……
set local statement_time=’100ms’;
select count(*) from a; — 这条 SQL 的执行工夫超过 100MS 则被动退出，并回滚整个事务
set local statement_timeout to default;
……
end;

函数级超时例子 – statement_timeout 不可用

例如这个 QUERY，咱们想让它 100 毫秒超时。

1select count(*) as cnt, id from a where id<$1 group by id;

将它写到函数中，在函数中设置超时

create or replace function f1(int) returns setof record as $$
declare
begin
set local statement_timeout=’100ms’;
return query select count(*) as cnt, id from a where id<$1 group by id;
end;
$$ language plpgsql strict ;

调用 SQL 改成这样

1select cnt,id from f1(1) as t(cnt int8, id int);

然而这么做实际上是没有成果的，起因是 statement_timeout 的设计之初是为交互性 SQL 设计的，在 postgres.c 中。

所以须要 plpgsql 超时，须要通过插件 HOOK 来实现。

https://www.postgresql.org/me…

statement_timeout is measured across an entire interactive command, not
individual commands within a function; and the timeout that applies to
an interactive command is determined at its beginning. So the above
doesn’t do what you think.

参数级别

1、实例级

批改

1postgresql.conf

2、库级

1alter database dbname set parameter=?;

3、用户级

1alter role rolname set parameter=?;

4、会话级

1set parameter=?;

5、事务级

begin;
set local parameter=?;
….
end;

6、函数级

1alter function fun_name() set parameter=?;

其余超时管制

1、闲暇事务超时

1idle_in_transaction_session_timeout = 2h

2、锁期待超时

1lock_timeout = 1s

3、死锁检测超时距离

1deadlock_timeout = 1s

statement_timeout = 0 # in milliseconds, 0 is disabled