2 -- Glyn Astill 20/12/2014
3 -- Function to schedule in order to log and possibly terminate long running transactions (pg9.1+)
6 DROP FUNCTION IF EXISTS public.pg_log_long_xact(interval, boolean, text, interval, interval, boolean);
7 CREATE OR REPLACE FUNCTION public.pg_log_long_xact(
8 in_duration interval, -- Report all transactions running longer than this
9 in_report_blockers boolean DEFAULT false, -- Also report detail of the blocking transaction. Default false.
10 in_raise_type text DEFAULT 'notice', -- Level to use with raise, can be notice/debug/log/info/warning or something else to return as text. Default notice.
11 in_cancel_duration interval DEFAULT null, -- Attempt to cancel the current statement of longest running transaction if running longer than this, starting with blockers. Default don't do anything.
12 in_terminate_duration interval DEFAULT null, -- Attempt to terminate the longest running transaction if running longer than this, starting with blockers. Default don't do anything.
13 in_cancel_all boolean DEFAULT false) -- When false will only attempt to cancel/terminate one at a time, when true will mercilessly cancel/terminate all. Default false.
20 v_cx_attempts integer := 0;
23 SELECT procpid AS pid,
25 COALESCE(application_name,'[unknown]') as app,
26 COALESCE(client_addr || ':' || client_port, '[local]') AS client,
28 current_timestamp-xact_start AS runtime_int,
29 extract(EPOCH FROM current_timestamp-xact_start) AS runtime,
32 WHERE current_timestamp-xact_start > in_duration
33 AND datname = current_database()
34 ORDER BY waiting ASC, runtime DESC
36 v_line := format(E'long_xact pid: %s duration: %s ms user: %s application: %s client: %s statement: %s',
37 v_xact.pid, v_xact.runtime, v_xact.usename, v_xact.app, v_xact.client, v_xact.current_query);
39 CASE lower(in_raise_type)
40 WHEN 'notice' THEN RAISE NOTICE '%', v_line;
41 WHEN 'debug' THEN RAISE DEBUG '%', v_line;
42 WHEN 'log' THEN RAISE LOG '%', v_line;
43 WHEN 'info' THEN RAISE INFO '%', v_line;
44 WHEN 'warning' THEN RAISE WARNING '%', v_line;
45 ELSE RETURN NEXT v_line;
48 -- Really better to enable log_lock_waits than use this
49 IF in_report_blockers AND v_xact.waiting THEN
50 SELECT INTO v_blk_xact extract(EPOCH FROM current_timestamp-ba.xact_start) AS blk_runtime,
52 ba.usename AS blk_usename,
53 COALESCE(ba.application_name,'[unknown]') as blk_app,
54 COALESCE(ba.client_addr || ':' || ba.client_port, '[local]') AS blk_client,
55 ba.current_query AS blk_query,
56 COALESCE(t.schemaname || '.' || t.relname || ' (' || b.mode || ')', bt.locks, '[unknown]') AS blk_relation,
57 CASE b.locktype WHEN 'transactionid' THEN 'transaction id ' || b.transactionid ELSE b.locktype END AS blk_type
58 FROM pg_catalog.pg_locks l
59 LEFT JOIN pg_catalog.pg_locks b ON b.granted
60 AND ((b.locktype = 'transactionid' AND b.transactionid = l.transactionid) OR (b.locktype = 'relation' AND b.relation = l.relation))
62 LEFT JOIN pg_catalog.pg_stat_activity ba ON ba.procpid = b.pid
63 LEFT JOIN pg_catalog.pg_stat_user_tables t ON l.relation = t.relid
64 LEFT JOIN (SELECT pid, 'any (' || string_agg(schemaname || '.' || relname || ' (' || mode || ')',', ') || ')' AS locks
65 FROM pg_locks JOIN pg_stat_user_tables ON relation = relid
66 WHERE mode ~ 'ExclusiveLock'
67 GROUP BY pid) bt ON bt.pid = b.pid
68 WHERE l.pid = v_xact.pid AND NOT l.granted LIMIT 1;
71 v_line := format(E'long_xact waiter pid: %s blocker detail is; pid: %s duration: %s ms relation: %s lock type: %s user: %s application: %s client: %s statement: %s',
72 v_xact.pid, v_blk_xact.blk_pid, v_blk_xact.blk_runtime, v_blk_xact.blk_relation, v_blk_xact.blk_type, v_blk_xact.blk_usename, v_blk_xact.blk_app,
73 v_blk_xact.blk_client, v_blk_xact.blk_query);
75 CASE lower(in_raise_type)
76 WHEN 'notice' THEN RAISE NOTICE '%', v_line;
77 WHEN 'debug' THEN RAISE DEBUG '%', v_line;
78 WHEN 'log' THEN RAISE LOG '%', v_line;
79 WHEN 'info' THEN RAISE INFO '%', v_line;
80 WHEN 'warning' THEN RAISE WARNING '%', v_line;
81 ELSE RETURN NEXT v_line;
86 -- In 9.3+ it's really better to use lock_timeout than use this
87 IF (v_cx_attempts = 0 OR in_cancel_all) AND in_cancel_duration IS NOT NULL AND v_xact.runtime_int > in_cancel_duration THEN
88 v_cx_attempts := v_cx_attempts+1;
89 IF v_xact.current_query <> '<IDLE> in transaction' AND pg_cancel_backend(v_xact.pid) THEN
90 v_line := format(E'long_xact cancelled backend with pid: %s', v_xact.pid);
92 CASE lower(in_raise_type)
93 WHEN 'notice' THEN RAISE NOTICE '%', v_line;
94 WHEN 'debug' THEN RAISE DEBUG '%', v_line;
95 WHEN 'log' THEN RAISE LOG '%', v_line;
96 WHEN 'info' THEN RAISE INFO '%', v_line;
97 WHEN 'warning' THEN RAISE WARNING '%', v_line;
98 ELSE RETURN NEXT v_line;
100 ELSIF in_terminate_duration IS NOT NULL AND v_xact.runtime_int > in_terminate_duration THEN
101 IF pg_terminate_backend(v_xact.pid) THEN
102 v_line := format('long_xact terminated backend with pid: %s', v_xact.pid);
104 CASE lower(in_raise_type)
105 WHEN 'notice' THEN RAISE NOTICE '%', v_line;
106 WHEN 'debug' THEN RAISE DEBUG '%', v_line;
107 WHEN 'log' THEN RAISE LOG '%', v_line;
108 WHEN 'info' THEN RAISE INFO '%', v_line;
109 WHEN 'warning' THEN RAISE WARNING '%', v_line;
110 ELSE RETURN NEXT v_line;
113 v_line := format('long_xact unable to terminate backend with pid: %s', v_xact.pid);
115 CASE lower(in_raise_type)
116 WHEN 'notice' THEN RAISE NOTICE '%', v_line;
117 WHEN 'debug' THEN RAISE DEBUG '%', v_line;
118 WHEN 'log' THEN RAISE LOG '%', v_line;
119 WHEN 'info' THEN RAISE INFO '%', v_line;
120 WHEN 'warning' THEN RAISE WARNING '%', v_line;
121 ELSE RETURN NEXT v_line;
126 v_line := format('long_xact unable to cancel backend with pid: %s', v_xact.pid);
128 CASE lower(in_raise_type)
129 WHEN 'notice' THEN RAISE NOTICE '%', v_line;
130 WHEN 'debug' THEN RAISE DEBUG '%', v_line;
131 WHEN 'log' THEN RAISE LOG '%', v_line;
132 WHEN 'info' THEN RAISE INFO '%', v_line;
133 WHEN 'warning' THEN RAISE WARNING '%', v_line;
134 ELSE RETURN NEXT v_line;
143 LANGUAGE plpgsql VOLATILE;