ロールとデータベースごとにstatement_timeout / lock_timeoutを設定

コマンド

-- Application role: short queries, short lock waits, a limit on forgotten transactions
ALTER ROLE web_app SET statement_timeout = '30s';
ALTER ROLE web_app SET lock_timeout = '5s';
ALTER ROLE web_app SET idle_in_transaction_session_timeout = '10min';

-- Let the reporting role run longer, only in this database
ALTER ROLE report_app IN DATABASE mydb SET statement_timeout = '15min';

-- Show configured role/database settings
SELECT coalesce(r.rolname, '(all)') AS role,
       coalesce(d.datname, '(all)') AS database,
       s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase
ORDER BY 1, 2;

-- Revert
ALTER ROLE web_app RESET lock_timeout;

概要

タイムアウトパラメータをロール(および任意でデータベース)レベルで永続化し、グローバル設定に触れずに各ワークロードが独自の上限で実行されるようにする。

使用する場面

暴走したクエリによるリソースの枯渇、DDL/ロックキューによるアプリケーションの停止、忘れられたトランザクションによる肥大化を防ぐため。

注意

設定は新しいセッションにのみ適用されるため、既存のプール接続を再接続させる。ユーザーはセッション内でSETにより値を上書きできるため、これはセキュリティ境界ではない。statement_timeoutをpostgresql.confでグローバルに設定しない: pg_dump、VACUUM、CREATE INDEXなどのメンテナンスジョブも取り消される。優先順位: ロール+データベース > ロール > データベース > postgresql.conf。