-- card_id: SQL-PG-04
-- title: PostgreSQL 14-18 锁等待与候选阻塞链（只读、脱敏）
-- engine: PostgreSQL
-- versions: 14, 15, 16, 17, 18
-- risk: R2
-- purpose: 列出当前数据库采集时刻最多 100 条等待关系，供负责人继续核验事务用途与终止风险。
-- input: 无；只读取当前数据库的 pg_stat_activity 快照与 pg_blocking_pids 结果。
-- output: 快照内会话代号、等待类型、等待事件、状态、事务年龄区间和候选阻塞者数量。
-- permission: pg_read_all_stats 成员或经负责人批准的等价可见性。
-- resource_limit: 当前快照、Top 100，建议客户端超时 5 秒。
-- validation: 已在 PostgreSQL 14-18 隔离合成锁等待验证；生产及客户环境未验证。
-- failure: 缺少可见性时返回 insufficient_visibility；无等待时返回空结果。
-- prohibited: 不得输出 query、库名、用户名、应用名、对象名或地址；不得取消、终止、提交、回滚或改配置。

WITH visibility AS (
    SELECT
        pg_has_role(current_user, 'pg_read_all_stats', 'MEMBER') AS can_observe
),
wait_pairs AS (
    SELECT DISTINCT
        activity.pid AS waiting_pid,
        blocker.blocking_pid,
        activity.wait_event_type,
        activity.wait_event,
        activity.state,
        CASE
            WHEN activity.xact_start IS NULL THEN 'unknown'
            WHEN CURRENT_TIMESTAMP - activity.xact_start < INTERVAL '1 minute' THEN 'under_1m'
            WHEN CURRENT_TIMESTAMP - activity.xact_start < INTERVAL '5 minutes' THEN '1m_to_5m'
            WHEN CURRENT_TIMESTAMP - activity.xact_start < INTERVAL '30 minutes' THEN '5m_to_30m'
            ELSE 'over_30m'
        END AS transaction_age_bucket,
        cardinality(pg_blocking_pids(activity.pid)) AS blocker_count
    FROM pg_stat_activity AS activity
    CROSS JOIN LATERAL unnest(pg_blocking_pids(activity.pid)) AS blocker(blocking_pid)
    CROSS JOIN visibility
    WHERE visibility.can_observe
      AND activity.datname = current_database()
      AND activity.pid <> pg_backend_pid()
),
ranked AS (
    SELECT
        'ready'::text AS observation_status,
        'wait-' || dense_rank() OVER (ORDER BY waiting_pid) AS waiting_session_ref,
        'block-' || dense_rank() OVER (ORDER BY blocking_pid) AS blocking_session_ref,
        wait_event_type,
        wait_event,
        state,
        transaction_age_bucket,
        blocker_count
    FROM wait_pairs
),
visibility_status AS (
    SELECT
        'insufficient_visibility'::text AS observation_status,
        NULL::text AS waiting_session_ref,
        NULL::text AS blocking_session_ref,
        NULL::text AS wait_event_type,
        NULL::text AS wait_event,
        NULL::text AS state,
        NULL::text AS transaction_age_bucket,
        NULL::integer AS blocker_count
    FROM visibility
    WHERE NOT can_observe
)
SELECT * FROM ranked
UNION ALL
SELECT * FROM visibility_status
ORDER BY
    waiting_session_ref NULLS LAST,
    blocking_session_ref NULLS LAST
LIMIT 100;
