使用背景
最近在使用PostgreSQL的時候，在執(zhí)行一些數(shù)據(jù)庫事務的時候，先后出現(xiàn)了statement timetout 和idle-in-transaction timeout的問題，導致數(shù)據(jù)庫操作失敗。
經(jīng)研究查找，PostgreSQL有關于SQL語句執(zhí)行超時和事務執(zhí)行超時的相關配置，而默認超時時間是10000毫秒，即10秒鐘的時間，這樣會導致執(zhí)行時間稍長的任務執(zhí)行失敗。可以通過修改PostgreSQL服務器配置文件的方式修改默認配置。
參數(shù)說明
statement_timeout
statement_timeout 在 postgresql 被用來控制語句執(zhí)行時長，單位是ms。
$ vi postgresql.conf
#statement_timeout = 0 # in milliseconds, 0 is disabled
默認是0，表示語句可以一直執(zhí)行下去。
如果設置為10000，那就意味著語句最多可以執(zhí)行 10000ms = 10s。
建議設置為0，禁用該參數(shù)。
1idle_in_transaction_session_timeout
PostgreSQL 9.6版本開始支持自動查殺超過指定時間的 idle in transaction 空閑事務連接，用于清理應用代碼中忘記關閉已開啟的事務，或者系統(tǒng)中存在僵死進程等。
idle_in_transaction_session_timeout 在 postgresql 被用來控制事務執(zhí)行時長，單位是ms。
$ vi postgresql.conf #idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
默認是0，表示語句可以一直執(zhí)行下去。超時會報 FATAL: terminating connection due to idle-in-transaction timeout。
修改方法
查找配置
通過命令查找到postgresql配置文件的位置，用vi進行編輯。
1
2find / -name "postgresql.conf"vi /var/lib/pgsql/9.6/data/postgresql.conf
修改參數(shù)
進入vi編輯界面，可以通過vi查找命令定位到相關參數(shù),修改成合適的時間，保存退出。
：/statement_timeout
重啟配置
通過以下命令，查找pg_ctl的位置，然后執(zhí)行 pg_ctl reload重新加載配置。
1
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 保存并重新使用上一次運行服務器的命令行參數(shù)。重啟服務器的最簡單的方法是：
1$ pg_ctl restart
重啟服務器，等待其停止和重啟：
1$ pg_ctl -w restart
使用 5433 端口重啟并且重啟后關閉 fsync ：
1$ pg_ctl -o "-F -p 5433" restart
顯示服務器狀態(tài)
下面是來自 pg_ctl 的狀態(tài)輸出的例子：
$ 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 模式中被調(diào)用的命令行。
補充：PostgreSQL 設置單條SQL的執(zhí)行超時 - 防雪崩
背景
設置單條SQL的執(zhí)行超時，防雪崩。
通常來說可以在SQL發(fā)起前設置事務級超時參數(shù)，SQL執(zhí)行結束，重置。（如果SQL異常退出，會自動重置事務級參數(shù)）
例子
begin;
......
set local statement_time='100ms';
select count(*) from a; -- 這條SQL的執(zhí)行時間超過100MS則主動退出，并回滾整個事務
set local statement_timeout to default;
......
end;
函數(shù)級超時例子 - statement_timeout不可用
例如這個QUERY，我們想讓它100毫秒超時。
1select count(*) as cnt, id from a where id<$1 group by id;
將它寫到函數(shù)中，在函數(shù)中設置超時
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 ;
調(diào)用SQL改成這樣
1select cnt,id from f1(1) as t(cnt int8, id int);
但是這么做實際上是沒有效果的，原因是statement_timeout的設計之初是為交互性SQL設計的，在postgres.c中。
所以需要plpgsql超時，需要通過插件HOOK來實現(xiàn)。
https://www.postgresql.org/message-id/flat/200702201200.53535.xzilla%40users.sourceforge.net#200702201200.53535.xzilla@users.sourceforge.net
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.
參數(shù)級別
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、函數(shù)級
1alter function fun_name() set parameter=?;
其他超時控制
1、空閑事務超時
1idle_in_transaction_session_timeout = 2h
2、鎖等待超時
1lock_timeout = 1s
3、死鎖檢測超時間隔
1deadlock_timeout = 1s
文章來源：腳本之家
來源地址：https://www.jb51.net/article/204224.htm
申請創(chuàng)業(yè)報道，分享創(chuàng)業(yè)好點子。點擊此處，共同探討創(chuàng)業(yè)新機遇！
