git.8kb.co.uk Git - postgresql/pg_log_long_xact/blob - 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     v_srv_version numeric;
  22 BEGIN
  23     SELECT substr(setting,1,3)::numeric INTO v_srv_version FROM pg_catalog.pg_settings WHERE name = 'server_version';
  24
  25     FOR v_xact IN EXECUTE 'SELECT ' || CASE WHEN (v_srv_version < 9.2) THEN 'procpid AS ' ELSE '' END || 'pid, ' ||
  26                 E'usename,
  27                 COALESCE(application_name,\'[unknown]\') as app,
  28                 COALESCE(client_addr || \':\' || client_port, \'[local]\') AS client,' ||
  29                 CASE WHEN (v_srv_version < 9.2) THEN E'current_query, \'[not supported]\' AS state, ' ELSE 'query AS current_query, state, ' END ||
  30                 E'current_timestamp-xact_start AS runtime_int,
  31                 extract(EPOCH FROM current_timestamp-xact_start) AS runtime,' ||
  32                 CASE WHEN (v_srv_version < 9.6) THEN 'waiting, NULL::text AS wait_event, NULL::text AS wait_event_type ' ELSE 'wait_event IS NOT NULL AS waiting, wait_event, wait_event_type ' END ||
  33             E'FROM pg_stat_activity
  34             WHERE current_timestamp-xact_start > \'' || in_duration || E'\'::interval' ||
  35             ' AND datname = current_database()
  36             ORDER BY waiting ASC, runtime DESC;'
  37     LOOP
  38         v_line := format(E'long_xact pid: %s duration: %s ms user: %s application: %s client: %s statement: %s',
  39             v_xact.pid, v_xact.runtime, v_xact.usename, v_xact.app, v_xact.client, v_xact.current_query);
  40
  41         CASE lower(in_raise_type)
  42             WHEN 'notice' THEN RAISE NOTICE '%', v_line;
  43             WHEN 'debug' THEN RAISE DEBUG '%', v_line;
  44             WHEN 'log' THEN RAISE LOG '%', v_line;
  45             WHEN 'info' THEN RAISE INFO '%', v_line;
  46             WHEN 'warning' THEN RAISE WARNING '%', v_line;
  47             ELSE RETURN NEXT v_line;
  48         END CASE;
  49
  50         -- Really better to enable log_lock_waits than use this
  51         IF in_report_blockers AND v_xact.waiting THEN
  52             IF (v_srv_version < 9.2) THEN
  53                 SELECT INTO v_blk_xact extract(EPOCH FROM current_timestamp-ba.xact_start) AS blk_runtime,
  54                     b.pid AS blk_pid,
  55                     ba.usename AS blk_usename,
  56                     COALESCE(ba.application_name,'[unknown]') as blk_app,
  57                     COALESCE(ba.client_addr || ':' || ba.client_port, '[local]') AS blk_client,
  58                     ba.current_query AS blk_query,
  59                     '[not supported]' AS blk_state,
  60                     COALESCE(t.schemaname || '.' || t.relname || ' (' || b.mode || ')', bt.locks, '[unknown]') AS blk_relation,
  61                     CASE b.locktype WHEN 'transactionid' THEN 'transaction id ' || b.transactionid ELSE b.locktype END AS blk_type
  62                 FROM pg_catalog.pg_locks l
  63                 LEFT JOIN pg_catalog.pg_locks b ON b.granted
  64                 AND ((b.locktype = 'transactionid' AND b.transactionid = l.transactionid) OR (b.locktype = 'relation' AND b.relation = l.relation))
  65                 AND b.pid != l.pid
  66                 LEFT JOIN pg_catalog.pg_stat_activity ba ON ba.procpid = b.pid
  67                 LEFT JOIN pg_catalog.pg_stat_user_tables t ON l.relation = t.relid
  68                 LEFT JOIN (SELECT pid, 'any (' || string_agg(schemaname || '.' || relname || ' (' || mode || ')',', ') || ')' AS locks
  69                 FROM pg_locks JOIN pg_stat_user_tables ON relation = relid
  70                 WHERE mode ~ 'ExclusiveLock'
  71                 GROUP BY pid) bt ON bt.pid = b.pid
  72                 WHERE l.pid = v_xact.pid AND NOT l.granted LIMIT 1;
  73             ELSE
  74                 SELECT INTO v_blk_xact extract(EPOCH FROM current_timestamp-ba.xact_start) AS blk_runtime,
  75                     b.pid AS blk_pid,
  76                     ba.usename AS blk_usename,
  77                     COALESCE(ba.application_name,'[unknown]') as blk_app,
  78                     COALESCE(ba.client_addr || ':' || ba.client_port, '[local]') AS blk_client,
  79                     ba.query AS blk_query,
  80                     ba.state AS blk_state,
  81                     COALESCE(t.schemaname || '.' || t.relname || ' (' || b.mode || ')', bt.locks, '[unknown]') AS blk_relation,
  82                     CASE b.locktype WHEN 'transactionid' THEN 'transaction id ' || b.transactionid ELSE b.locktype END AS blk_type
  83                 FROM pg_catalog.pg_locks l
  84                 LEFT JOIN pg_catalog.pg_locks b ON b.granted
  85                 AND ((b.locktype = 'transactionid' AND b.transactionid = l.transactionid) OR (b.locktype = 'relation' AND b.relation = l.relation))
  86                 AND b.pid != l.pid
  87                 LEFT JOIN pg_catalog.pg_stat_activity ba ON ba.pid = b.pid
  88                 LEFT JOIN pg_catalog.pg_stat_user_tables t ON l.relation = t.relid
  89                 LEFT JOIN (SELECT pid, 'any (' || string_agg(schemaname || '.' || relname || ' (' || mode || ')',', ') || ')' AS locks
  90                 FROM pg_locks JOIN pg_stat_user_tables ON relation = relid
  91                 WHERE mode ~ 'ExclusiveLock'
  92                 GROUP BY pid) bt ON bt.pid = b.pid
  93                 WHERE l.pid = v_xact.pid AND NOT l.granted LIMIT 1;
  94             END IF;
  95
  96             IF FOUND THEN
  97                 v_line := format(E'long_xact waiter pid: %s wait type: %s blocker detail is; pid: %s duration: %s ms relation: %s lock type: %s user: %s application: %s client: %s statement: %s',
  98                     v_xact.pid, CASE WHEN (v_srv_version < 9.6) THEN '[not supported]' ELSE COALESCE(v_xact.wait_event_type,'N/A')||':'||COALESCE(v_xact.wait_event,'N/A') END, 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, v_blk_xact.blk_client, v_blk_xact.blk_query);
  99
 100                 CASE lower(in_raise_type)
 101                     WHEN 'notice' THEN RAISE NOTICE '%', v_line;
 102                     WHEN 'debug' THEN RAISE DEBUG '%', v_line;
 103                     WHEN 'log' THEN RAISE LOG '%', v_line;
 104                     WHEN 'info' THEN RAISE INFO '%', v_line;
 105                     WHEN 'warning' THEN RAISE WARNING '%', v_line;
 106                     ELSE RETURN NEXT v_line;
 107                 END CASE;
 108             END IF;
 109         END IF;
 110
 111         -- In 9.3+ it's really better to use lock_timeout than use this
 112         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
 113             v_cx_attempts := v_cx_attempts+1;
 114             IF v_xact.current_query <> '<IDLE> in transaction' AND v_xact.state <> 'idle in transaction' AND pg_cancel_backend(v_xact.pid) THEN
 115                 v_line := format(E'long_xact cancelled backend with pid: %s', v_xact.pid);
 116
 117                 CASE lower(in_raise_type)
 118                     WHEN 'notice' THEN RAISE NOTICE '%', v_line;
 119                     WHEN 'debug' THEN RAISE DEBUG '%', v_line;
 120                     WHEN 'log' THEN RAISE LOG '%', v_line;
 121                     WHEN 'info' THEN RAISE INFO '%', v_line;
 122                     WHEN 'warning' THEN RAISE WARNING '%', v_line;
 123                     ELSE RETURN NEXT v_line;
 124                 END CASE;
 125             ELSIF in_terminate_duration IS NOT NULL AND v_xact.runtime_int > in_terminate_duration THEN
 126                 IF pg_terminate_backend(v_xact.pid) THEN
 127                     v_line := format('long_xact terminated backend with pid: %s', v_xact.pid);
 128
 129                     CASE lower(in_raise_type)
 130                         WHEN 'notice' THEN RAISE NOTICE '%', v_line;
 131                         WHEN 'debug' THEN RAISE DEBUG '%', v_line;
 132                         WHEN 'log' THEN RAISE LOG '%', v_line;
 133                         WHEN 'info' THEN RAISE INFO '%', v_line;
 134                         WHEN 'warning' THEN RAISE WARNING '%', v_line;
 135                         ELSE RETURN NEXT v_line;
 136                     END CASE;
 137                 ELSE
 138                     v_line := format('long_xact unable to terminate backend with pid: %s', v_xact.pid);
 139
 140                     CASE lower(in_raise_type)
 141                         WHEN 'notice' THEN RAISE NOTICE '%', v_line;
 142                         WHEN 'debug' THEN RAISE DEBUG '%', v_line;
 143                         WHEN 'log' THEN RAISE LOG '%', v_line;
 144                         WHEN 'info' THEN RAISE INFO '%', v_line;
 145                         WHEN 'warning' THEN RAISE WARNING '%', v_line;
 146                         ELSE RETURN NEXT v_line;
 147                     END CASE;
 148
 149                 END IF;
 150             ELSE
 151                 v_line := format('long_xact unable to cancel backend with pid: %s', v_xact.pid);
 152
 153                 CASE lower(in_raise_type)
 154                     WHEN 'notice' THEN RAISE NOTICE '%', v_line;
 155                     WHEN 'debug' THEN RAISE DEBUG '%', v_line;
 156                     WHEN 'log' THEN RAISE LOG '%', v_line;
 157                     WHEN 'info' THEN RAISE INFO '%', v_line;
 158                     WHEN 'warning' THEN RAISE WARNING '%', v_line;
 159                     ELSE RETURN NEXT v_line;
 160                 END CASE;
 161             END IF;
 162         END IF;
 163     END LOOP;
 164
 165     RETURN;
 166 END;
 167 $BODY$
 168 LANGUAGE plpgsql VOLATILE;