]> git.8kb.co.uk Git - postgresql/pg_log_long_xact/blob - pg_long_xact_9.1+.plpgsql
Add in actual function and create a quick 9.1+ version using format rather than append
[postgresql/pg_log_long_xact] / pg_long_xact_9.1+.plpgsql
1 -- 
2 -- Glyn Astill 20/12/2014
3 -- Function to schedule in order to log and possibly terminate long running transactions (pg9.1+)
4 --
5
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.
14 RETURNS SETOF text AS
15 $BODY$
16 DECLARE
17     v_xact record;
18     v_blk_xact record;
19     v_line text;
20     v_cx_attempts integer := 0;
21 BEGIN
22     FOR v_xact IN (
23         SELECT procpid AS pid, 
24             usename, 
25             COALESCE(application_name,'[unknown]') as app, 
26             COALESCE(client_addr || ':' || client_port, '[local]') AS client,
27             current_query, 
28             current_timestamp-xact_start AS runtime_int,
29             extract(EPOCH FROM current_timestamp-xact_start) AS runtime,
30             waiting
31         FROM pg_stat_activity 
32         WHERE current_timestamp-xact_start > in_duration
33         AND datname = current_database()
34         ORDER BY waiting ASC, runtime DESC
35     ) LOOP
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);        
38             
39         CASE lower(in_raise_type)
40             WHEN 'notice' THEN RAISE NOTICE '%', v_line; 
41             WHEN 'debug' THEN RAISE LOG '%', 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;
46         END CASE;
47         
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,
51                 b.pid AS blk_pid,
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)) 
61             AND b.pid != l.pid
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;
69                         
70             IF FOUND THEN
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);
74                     
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; 
82                 END CASE;
83             END IF;
84         END IF;
85         
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);
91
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; 
99                 END CASE;
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);
103
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; 
111                     END CASE;            
112                 ELSE
113                     v_line := format('long_xact unable to terminate backend with pid: %s', v_xact.pid);
114
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; 
122                     END CASE;            
123
124                 END IF;
125             ELSE
126                 v_line := format('long_xact unable to cancel backend with pid: %s', v_xact.pid);
127
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; 
135                 END CASE;
136             END IF;        
137         END IF;
138     END LOOP;
139     
140     RETURN;
141 END;
142 $BODY$
143 LANGUAGE plpgsql VOLATILE;