-- 
-- 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;
    v_srv_version numeric;
BEGIN
    SELECT substr(setting,1,3)::numeric INTO v_srv_version FROM pg_catalog.pg_settings WHERE name = 'server_version';

    FOR v_xact IN EXECUTE 'SELECT ' || CASE WHEN (v_srv_version < 9.2) THEN 'procpid AS ' ELSE '' END || 'pid, ' ||
                E'usename, 
                COALESCE(application_name,\'[unknown]\') as app, 
                COALESCE(client_addr || \':\' || client_port, \'[local]\') AS client,' ||
                CASE WHEN (v_srv_version < 9.2) THEN E'current_query, \'[not supported]\' AS state, ' ELSE 'query AS current_query, state, ' END ||
                E'current_timestamp-xact_start AS runtime_int,
                extract(EPOCH FROM current_timestamp-xact_start) AS runtime,' ||
                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 ||
            E'FROM pg_stat_activity 
            WHERE current_timestamp-xact_start > \'' || in_duration || E'\'::interval' ||
            ' 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 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;
        
        -- Really better to enable log_lock_waits than use this
        IF in_report_blockers AND v_xact.waiting THEN
            IF (v_srv_version < 9.2) 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,
                    '[not supported]' AS blk_state,
                    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;
            ELSE
                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.query AS blk_query,
                    ba.state AS blk_state,
                    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.pid = 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;
            END IF;
                        
            IF FOUND THEN
                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',
                    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);
                    
                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 <> '<IDLE> in transaction' AND v_xact.state <> 'idle 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;