-- card_id: SQL-PG-06
-- title: PostgreSQL 14-18 连接容量快照（只读、聚合、脱敏）
-- engine: PostgreSQL
-- versions: 14, 15, 16, 17, 18
-- risk: R2
-- purpose: 在连接失败或连接池排队时，形成单行实例级容量快照，供负责人继续核对连接池、事务、内存、后台进程和时间线。
-- input: 无；只读取 pg_stat_activity 聚合与只读配置。
-- output: 最大连接、超级用户保留连接、客户端连接、活动/空闲连接、近似余量、利用率和区间。
-- permission: pg_read_all_stats 成员或经负责人批准的等价可见性。
-- resource_limit: 单行结果，建议客户端超时 5 秒。
-- validation: 已在 PostgreSQL 14-18 隔离连接压力验证；生产及客户环境未验证。
-- failure: 缺少可见性时返回 insufficient_visibility；配置无法读取时由数据库明确报错。
-- prohibited: 不得输出 query、库名、用户名、应用名、对象名或客户端地址；不得取消、终止、SET、提交、回滚、修改参数或扩大权限。

WITH visibility AS (
    SELECT
        pg_has_role(current_user, 'pg_read_all_stats', 'MEMBER') AS can_observe
),
limits AS (
    SELECT
        current_setting('max_connections')::integer AS max_connections,
        current_setting('superuser_reserved_connections')::integer
            AS superuser_reserved_connections
),
connection_snapshot AS (
    SELECT
        count(*) FILTER (
            WHERE backend_type = 'client backend'
        )::integer AS client_connections,
        count(*) FILTER (
            WHERE backend_type = 'client backend' AND state = 'active'
        )::integer AS active_client_connections,
        count(*) FILTER (
            WHERE backend_type = 'client backend'
              AND state IN ('idle', 'idle in transaction', 'idle in transaction (aborted)')
        )::integer AS idle_client_connections
    FROM pg_stat_activity
    CROSS JOIN visibility
    WHERE visibility.can_observe
),
ready AS (
    SELECT
        'ready'::text AS observation_status,
        limits.max_connections,
        limits.superuser_reserved_connections,
        snapshot.client_connections,
        snapshot.active_client_connections,
        snapshot.idle_client_connections,
        greatest(limits.max_connections - snapshot.client_connections, 0)
            AS approximate_connection_headroom,
        round(
            snapshot.client_connections * 100.0 / nullif(limits.max_connections, 0),
            2
        ) AS connection_utilization_pct,
        CASE
            WHEN limits.max_connections = 0 THEN 'unknown'
            WHEN snapshot.client_connections * 100.0 / limits.max_connections < 50
                THEN 'under_50pct'
            WHEN snapshot.client_connections * 100.0 / limits.max_connections < 80
                THEN '50_to_80pct'
            WHEN snapshot.client_connections * 100.0 / limits.max_connections < 95
                THEN '80_to_95pct'
            ELSE '95pct_or_more'
        END::text AS utilization_bucket
    FROM limits
    CROSS JOIN connection_snapshot AS snapshot
    CROSS JOIN visibility
    WHERE visibility.can_observe
),
visibility_status AS (
    SELECT
        'insufficient_visibility'::text AS observation_status,
        NULL::integer AS max_connections,
        NULL::integer AS superuser_reserved_connections,
        NULL::integer AS client_connections,
        NULL::integer AS active_client_connections,
        NULL::integer AS idle_client_connections,
        NULL::integer AS approximate_connection_headroom,
        NULL::numeric AS connection_utilization_pct,
        'unknown'::text AS utilization_bucket
    FROM visibility
    WHERE NOT can_observe
)
SELECT * FROM ready
UNION ALL
SELECT * FROM visibility_status
LIMIT 1;
