-- card_id: SQL-PG-08
-- title: PostgreSQL 14-18 复制槽与 WAL 保留压力快照（只读、聚合、脱敏）
-- engine: PostgreSQL
-- versions: 14, 15, 16, 17, 18
-- risk: R2
-- purpose: 在 WAL 目录增长、复制槽长期不消费或逻辑订阅落后时，形成单行实例级聚合证据，供负责人继续核对时间线、磁盘、消费端和保留策略。
-- input: 无；只读取复制槽聚合和当前 WAL 位置。
-- output: 槽总数、物理/逻辑、活动/非活动、lost状态数量、聚合保留字节、最大单槽保留字节与展示区间。
-- permission: pg_read_all_stats 成员或经负责人批准的等价可见性。
-- resource_limit: 单行结果，建议客户端超时 5 秒。
-- validation: 已在 PostgreSQL 14-18 隔离合成槽活动与 WAL 保留压力验证；生产及客户环境未验证。
-- failure: 缺少可见性时返回 insufficient_visibility；不得为获得槽身份或业务对象扩大权限。
-- prohibited: 不得输出槽名、数据库、插件、PID、地址、LSN、订阅或业务对象；不得创建、推进、删除槽，不得触发 WAL 切换，不得写入、改参数、切换或重建。

WITH visibility AS (
    SELECT
        pg_has_role(current_user, 'pg_read_all_stats', 'MEMBER') AS can_observe
),
slot_snapshot AS (
    SELECT
        count(*)::integer AS total_slots,
        count(*) FILTER (WHERE slot_type = 'physical')::integer AS physical_slots,
        count(*) FILTER (WHERE slot_type = 'logical')::integer AS logical_slots,
        count(*) FILTER (WHERE active)::integer AS active_slots,
        count(*) FILTER (WHERE NOT active)::integer AS inactive_slots,
        count(*) FILTER (WHERE wal_status = 'lost')::integer AS invalidated_slots,
        coalesce(
            sum(
                greatest(
                    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn),
                    0
                )
            ) FILTER (WHERE restart_lsn IS NOT NULL),
            0
        )::numeric AS wal_retained_bytes_total,
        coalesce(
            max(
                greatest(
                    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn),
                    0
                )
            ) FILTER (WHERE restart_lsn IS NOT NULL),
            0
        )::numeric AS wal_retained_bytes_max
    FROM pg_replication_slots
    CROSS JOIN visibility
    WHERE visibility.can_observe
),
ready AS (
    SELECT
        'ready'::text AS observation_status,
        total_slots,
        physical_slots,
        logical_slots,
        active_slots,
        inactive_slots,
        invalidated_slots,
        wal_retained_bytes_total,
        wal_retained_bytes_max,
        CASE
            WHEN wal_retained_bytes_max = 0 THEN 'zero'
            WHEN wal_retained_bytes_max < 16777216 THEN 'under_16mb'
            WHEN wal_retained_bytes_max < 268435456 THEN '16mb_to_256mb'
            WHEN wal_retained_bytes_max < 1073741824 THEN '256mb_to_1gb'
            ELSE '1gb_or_more'
        END::text AS retention_bucket
    FROM slot_snapshot
    CROSS JOIN visibility
    WHERE visibility.can_observe
),
visibility_status AS (
    SELECT
        'insufficient_visibility'::text AS observation_status,
        NULL::integer AS total_slots,
        NULL::integer AS physical_slots,
        NULL::integer AS logical_slots,
        NULL::integer AS active_slots,
        NULL::integer AS inactive_slots,
        NULL::integer AS invalidated_slots,
        NULL::numeric AS wal_retained_bytes_total,
        NULL::numeric AS wal_retained_bytes_max,
        'unknown'::text AS retention_bucket
    FROM visibility
    WHERE NOT can_observe
)
SELECT * FROM ready
UNION ALL
SELECT * FROM visibility_status
LIMIT 1;
