PostgreSQL - Transaction Stats View

transaction_stats.sql

DROP VIEW transaction_stats;
DROP TYPE LOCK_MODE;

CREATE TYPE LOCK_MODE AS ENUM (
        'AccessShareLock',
        'RowShareLock',
        'RowExclusiveLock',
        'ShareUpdateExclusiveLock',
        'ShareLock',
        'ShareRowExclusiveLock',
        'ExclusiveLock',
        'AccessExclusiveLock'
    );

CREATE OR REPLACE VIEW transaction_stats AS
    SELECT  sa.usename AS user_name,
            CASE sa.application_name WHEN '' THEN '<none>' ELSE sa.application_name END AS application_name,
            REPLACE(INITCAP(sa.backend_type), ' ', '') AS backend_type,
            INITCAP(sa.state) AS transaction_state,
            CONCAT_WS(':', TO_CHAR(_state_age / 3600, 'FM9900'), TO_CHAR((_state_age::NUMERIC % 3600) / 60, 'FM00'), TO_CHAR(_state_age::NUMERIC % 60, 'FM00'), TO_CHAR(((ROUND(_state_age::NUMERIC, 3)-FLOOR(_state_age::NUMERIC))*1000)::INT, 'FM000')) AS with_state_since,
            sa.pid AS this_pid,
            COALESCE(bp.pids::TEXT[], ARRAY['<none>']) AS is_blocking,
            CASE CARDINALITY(_pids) WHEN 0 THEN ARRAY['<none>'] ELSE _pids::TEXT[] END AS is_blocked_by,
            CASE WHEN lm.mode IS NULL THEN '<none>' ELSE lm.mode END AS requesting_highest_lock,
            COALESCE(lm.relation::TEXT, '<none>') AS with_lock_on,
            CASE WHEN lm.mode IS NULL THEN '<none>' ELSE lm.grant_state END AS and_request_state,
            CONCAT_WS(':', TO_CHAR(_query_age / 3600, 'FM9900'), TO_CHAR((_query_age::NUMERIC % 3600) / 60, 'FM00'), TO_CHAR(_query_age::NUMERIC % 60, 'FM00'), TO_CHAR(((ROUND(_query_age::NUMERIC, 3)-FLOOR(_query_age::NUMERIC))*1000)::INT, 'FM000')) AS last_query_time,
            sa.query AS last_query
    FROM    pg_stat_activity AS sa,
            LATERAL DATE_PART('EPOCH', CLOCK_TIMESTAMP() - sa.state_change) AS _state_age,
            LATERAL DATE_PART('EPOCH', CASE WHEN sa.state = 'active' THEN CLOCK_TIMESTAMP() - sa.state_change ELSE sa.state_change - sa.query_start END) AS _query_age,
            LATERAL PG_BLOCKING_PIDS(sa.pid) AS _pids
    LEFT JOIN LATERAL (
        SELECT ARRAY_AGG(pbo.pid) AS pids
        FROM   pg_stat_activity AS pbo
        WHERE  sa.pid = ANY(PG_BLOCKING_PIDS(pbo.pid))
    ) AS bp ON TRUE
    LEFT JOIN LATERAL (
        SELECT  l.mode,
                CASE WHEN l.granted THEN 'Granted' ELSE 'Pending' END AS grant_state,
                l.relation::REGCLASS AS relation
        FROM    pg_locks AS l
        WHERE   l.pid = sa.pid
          AND   l.relation IS NOT NULL
        ORDER BY
                l.mode::LOCK_MODE DESC
        LIMIT   1
    ) AS lm ON TRUE
    WHERE   sa.query <> ''
    ORDER BY
            sa.usename, sa.application_name, sa.state, sa.pid
;