解决PostgreSQL 执行超时的情况

statement_timeout
statement_timeout 在 postgresql 被用来控制语句执行时长，单位是ms。
$ vi postgresql.conf
#statement_timeout = 0 # in milliseconds, 0 is disabled

idle_in_transaction_session_timeout

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

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

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

$ pg_ctl start

$ pg_ctl -w start

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

$ pg_ctl stop

$ pg_ctl restart

$ pg_ctl -w restart

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

$ 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’

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

select 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 ;

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

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.

postgresql.conf

alter database dbname set parameter=?;

alter role rolname set parameter=?;

set parameter=?;

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

alter function fun_name() set parameter=?;

idle_in_transaction_session_timeout = 2h

lock_timeout = 1s

deadlock_timeout = 1s