postgresのチートシート

ロングトランザクションの確認

SELECT
  procpid,
  waiting,
  (current_timestamp - xact_start)::interval(3) AS duration,
  current_query FROM pg_stat_activity
WHERE procpid <> pg_backend_pid()
AND (current_timestamp - xact_start)::interval(3) >= '3 minutes'::interval;

9.3の場合

SELECT
  pid,
  waiting, 
  (current_timestamp - xact_start)::interval(3) AS duration,
  query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND (current_timestamp - xact_start)::interval(3) >= '3 minutes'::interval;

interval(小数点以下の桁数)

queryの取り消し

SELECT pg_cancel_backend(プロセスID);

取り消しが効かなかったら

SELECT pg_terminate_backend(プロセスID);