git.8kb.co.uk Git - postgresql/pg_log_long_xact/blob - pg_long_xact.plpgsql

   1 --
   2 -- Glyn Astill 27/05/2012
   3 -- Function to schedule in order to log and possibly terminate long running transactions
   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 := E'long_xact pid: ' || v_xact.pid || ' duration: ' || v_xact.runtime || ' ms user: ' || v_xact.usename ||
  37             ' application: ' || v_xact.app || ' client: ' || v_xact.client ||' statement: ' || v_xact.current_query;
  38
  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;
  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 := E'long_xact waiter pid: ' || v_xact.pid || ' blocker detail is; pid: ' || v_blk_xact.blk_pid || ' duration: ' ||
  72                     v_blk_xact.blk_runtime || ' ms relation: ' || v_blk_xact.blk_relation || ' lock type: ' || v_blk_xact.blk_type ||
  73                     ' user: ' || v_blk_xact.blk_usename || ' application: ' || v_blk_xact.blk_app || ' client: ' ||
  74                     v_blk_xact.blk_client || ' statement: ' || v_blk_xact.blk_query;
  75
  76                 CASE lower(in_raise_type)
  77                     WHEN 'notice' THEN RAISE NOTICE '%', v_line;
  78                     WHEN 'debug' THEN RAISE DEBUG '%', v_line;
  79                     WHEN 'log' THEN RAISE LOG '%', v_line;
  80                     WHEN 'info' THEN RAISE INFO '%', v_line;
  81                     WHEN 'warning' THEN RAISE WARNING '%', v_line;
  82                     ELSE RETURN NEXT v_line;
  83                 END CASE;
  84             END IF;
  85         END IF;
  86
  87         -- In 9.3+ it's really better to use lock_timeout than use this
  88         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
  89             v_cx_attempts := v_cx_attempts+1;
  90             IF v_xact.current_query <> '<IDLE> in transaction' AND pg_cancel_backend(v_xact.pid) THEN
  91                 v_line := 'long_xact cancelled backend with pid: ' || v_xact.pid;
  92
  93                 CASE lower(in_raise_type)
  94                     WHEN 'notice' THEN RAISE NOTICE '%', v_line;
  95                     WHEN 'debug' THEN RAISE DEBUG '%', v_line;
  96                     WHEN 'log' THEN RAISE LOG '%', v_line;
  97                     WHEN 'info' THEN RAISE INFO '%', v_line;
  98                     WHEN 'warning' THEN RAISE WARNING '%', v_line;
  99                     ELSE RETURN NEXT v_line;
 100                 END CASE;
 101             ELSIF in_terminate_duration IS NOT NULL AND v_xact.runtime_int > in_terminate_duration THEN
 102                 IF pg_terminate_backend(v_xact.pid) THEN
 103                     v_line := 'long_xact terminated backend with pid: ' || v_xact.pid;
 104
 105                     CASE lower(in_raise_type)
 106                         WHEN 'notice' THEN RAISE NOTICE '%', v_line;
 107                         WHEN 'debug' THEN RAISE DEBUG '%', v_line;
 108                         WHEN 'log' THEN RAISE LOG '%', v_line;
 109                         WHEN 'info' THEN RAISE INFO '%', v_line;
 110                         WHEN 'warning' THEN RAISE WARNING '%', v_line;
 111                         ELSE RETURN NEXT v_line;
 112                     END CASE;
 113                 ELSE
 114                     v_line := 'long_xact unable to terminate backend with pid: ' || v_xact.pid;
 115
 116                     CASE lower(in_raise_type)
 117                         WHEN 'notice' THEN RAISE NOTICE '%', v_line;
 118                         WHEN 'debug' THEN RAISE DEBUG '%', v_line;
 119                         WHEN 'log' THEN RAISE LOG '%', v_line;
 120                         WHEN 'info' THEN RAISE INFO '%', v_line;
 121                         WHEN 'warning' THEN RAISE WARNING '%', v_line;
 122                         ELSE RETURN NEXT v_line;
 123                     END CASE;
 124
 125                 END IF;
 126             ELSE
 127                 v_line := 'long_xact unable to cancel backend with pid: ' || 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             END IF;
 138         END IF;
 139     END LOOP;
 140
 141     RETURN;
 142 END;
 143 $BODY$
 144 LANGUAGE plpgsql VOLATILE;