X-Git-Url: https://git.8kb.co.uk/?p=postgresql%2Fpg_log_long_xact;a=blobdiff_plain;f=pg_long_xact_9.1%2B.plpgsql;fp=pg_long_xact_9.1%2B.plpgsql;h=a89f093893a0b61b34b5759c1f041b826c9ea09f;hp=0000000000000000000000000000000000000000;hb=854b031fba63292ea22a9d653a4f2b844fe377a3;hpb=3e933ba2e286a9753f7f3110e75ae356794b6022 diff --git a/pg_long_xact_9.1+.plpgsql b/pg_long_xact_9.1+.plpgsql new file mode 100755 index 0000000..a89f093 --- /dev/null +++ b/pg_long_xact_9.1+.plpgsql @@ -0,0 +1,143 @@ +-- +-- Glyn Astill 20/12/2014 +-- Function to schedule in order to log and possibly terminate long running transactions (pg9.1+) +-- + +DROP FUNCTION IF EXISTS public.pg_log_long_xact(interval, boolean, text, interval, interval, boolean); +CREATE OR REPLACE FUNCTION public.pg_log_long_xact( + in_duration interval, -- Report all transactions running longer than this + in_report_blockers boolean DEFAULT false, -- Also report detail of the blocking transaction. Default false. + 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. + 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. + 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. + 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. +RETURNS SETOF text AS +$BODY$ +DECLARE + v_xact record; + v_blk_xact record; + v_line text; + v_cx_attempts integer := 0; +BEGIN + FOR v_xact IN ( + SELECT procpid AS pid, + usename, + COALESCE(application_name,'[unknown]') as app, + COALESCE(client_addr || ':' || client_port, '[local]') AS client, + current_query, + current_timestamp-xact_start AS runtime_int, + extract(EPOCH FROM current_timestamp-xact_start) AS runtime, + waiting + FROM pg_stat_activity + WHERE current_timestamp-xact_start > in_duration + AND datname = current_database() + ORDER BY waiting ASC, runtime DESC + ) LOOP + v_line := format(E'long_xact pid: %s duration: %s ms user: %s application: %s client: %s statement: %s', + v_xact.pid, v_xact.runtime, v_xact.usename, v_xact.app, v_xact.client, v_xact.current_query); + + CASE lower(in_raise_type) + WHEN 'notice' THEN RAISE NOTICE '%', v_line; + WHEN 'debug' THEN RAISE LOG '%', v_line; + WHEN 'log' THEN RAISE LOG '%', v_line; + WHEN 'info' THEN RAISE INFO '%', v_line; + WHEN 'warning' THEN RAISE WARNING '%', v_line; + ELSE RETURN NEXT v_line; + END CASE; + + -- Really better to enable log_lock_waits than use this + IF in_report_blockers AND v_xact.waiting THEN + SELECT INTO v_blk_xact extract(EPOCH FROM current_timestamp-ba.xact_start) AS blk_runtime, + b.pid AS blk_pid, + ba.usename AS blk_usename, + COALESCE(ba.application_name,'[unknown]') as blk_app, + COALESCE(ba.client_addr || ':' || ba.client_port, '[local]') AS blk_client, + ba.current_query AS blk_query, + COALESCE(t.schemaname || '.' || t.relname || ' (' || b.mode || ')', bt.locks, '[unknown]') AS blk_relation, + CASE b.locktype WHEN 'transactionid' THEN 'transaction id ' || b.transactionid ELSE b.locktype END AS blk_type + FROM pg_catalog.pg_locks l + LEFT JOIN pg_catalog.pg_locks b ON b.granted + AND ((b.locktype = 'transactionid' AND b.transactionid = l.transactionid) OR (b.locktype = 'relation' AND b.relation = l.relation)) + AND b.pid != l.pid + LEFT JOIN pg_catalog.pg_stat_activity ba ON ba.procpid = b.pid + LEFT JOIN pg_catalog.pg_stat_user_tables t ON l.relation = t.relid + LEFT JOIN (SELECT pid, 'any (' || string_agg(schemaname || '.' || relname || ' (' || mode || ')',', ') || ')' AS locks + FROM pg_locks JOIN pg_stat_user_tables ON relation = relid + WHERE mode ~ 'ExclusiveLock' + GROUP BY pid) bt ON bt.pid = b.pid + WHERE l.pid = v_xact.pid AND NOT l.granted LIMIT 1; + + IF FOUND THEN + 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', + 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, + v_blk_xact.blk_client, v_blk_xact.blk_query); + + CASE lower(in_raise_type) + WHEN 'notice' THEN RAISE NOTICE '%', v_line; + WHEN 'debug' THEN RAISE DEBUG '%', v_line; + WHEN 'log' THEN RAISE LOG '%', v_line; + WHEN 'info' THEN RAISE INFO '%', v_line; + WHEN 'warning' THEN RAISE WARNING '%', v_line; + ELSE RETURN NEXT v_line; + END CASE; + END IF; + END IF; + + -- In 9.3+ it's really better to use lock_timeout than use this + 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 + v_cx_attempts := v_cx_attempts+1; + IF v_xact.current_query <> ' in transaction' AND pg_cancel_backend(v_xact.pid) THEN + v_line := format(E'long_xact cancelled backend with pid: %s', v_xact.pid); + + CASE lower(in_raise_type) + WHEN 'notice' THEN RAISE NOTICE '%', v_line; + WHEN 'debug' THEN RAISE DEBUG '%', v_line; + WHEN 'log' THEN RAISE LOG '%', v_line; + WHEN 'info' THEN RAISE INFO '%', v_line; + WHEN 'warning' THEN RAISE WARNING '%', v_line; + ELSE RETURN NEXT v_line; + END CASE; + ELSIF in_terminate_duration IS NOT NULL AND v_xact.runtime_int > in_terminate_duration THEN + IF pg_terminate_backend(v_xact.pid) THEN + v_line := format('long_xact terminated backend with pid: %s', v_xact.pid); + + CASE lower(in_raise_type) + WHEN 'notice' THEN RAISE NOTICE '%', v_line; + WHEN 'debug' THEN RAISE DEBUG '%', v_line; + WHEN 'log' THEN RAISE LOG '%', v_line; + WHEN 'info' THEN RAISE INFO '%', v_line; + WHEN 'warning' THEN RAISE WARNING '%', v_line; + ELSE RETURN NEXT v_line; + END CASE; + ELSE + v_line := format('long_xact unable to terminate backend with pid: %s', v_xact.pid); + + CASE lower(in_raise_type) + WHEN 'notice' THEN RAISE NOTICE '%', v_line; + WHEN 'debug' THEN RAISE DEBUG '%', v_line; + WHEN 'log' THEN RAISE LOG '%', v_line; + WHEN 'info' THEN RAISE INFO '%', v_line; + WHEN 'warning' THEN RAISE WARNING '%', v_line; + ELSE RETURN NEXT v_line; + END CASE; + + END IF; + ELSE + v_line := format('long_xact unable to cancel backend with pid: %s', v_xact.pid); + + CASE lower(in_raise_type) + WHEN 'notice' THEN RAISE NOTICE '%', v_line; + WHEN 'debug' THEN RAISE DEBUG '%', v_line; + WHEN 'log' THEN RAISE LOG '%', v_line; + WHEN 'info' THEN RAISE INFO '%', v_line; + WHEN 'warning' THEN RAISE WARNING '%', v_line; + ELSE RETURN NEXT v_line; + END CASE; + END IF; + END IF; + END LOOP; + + RETURN; +END; +$BODY$ +LANGUAGE plpgsql VOLATILE;