PostgreSQL 9.6主键冲突：已提交不可见行无法冻结问题排查

核心问题定位 #

你遇到的是已提交事务记录不可见但主键约束仍生效的异常，以下是针对性的排查与修复步骤：

1. 确认目标事务ID的实际状态 #

先验证xmin=226114219的事务是否真的处于已提交状态：

SELECT pg_xact_status('226114219'::xid);

返回值说明：

• committed：事务确实已提交；
• aborted：事务已回滚（但主键冲突说明状态识别可能存在异常）；
• in progress：事务仍在运行（你已排查无长事务，此情况概率极低）。

2. 检查行级安全策略（RLS） #

PostgreSQL 9.6支持行级安全，若表开启RLS，当前用户可能因权限规则无法看到记录，但主键约束是全局生效的：

-- 检查表是否开启RLS
SELECT relrowsecurity, relforcerowsecurity 
FROM pg_class 
WHERE relname = 'system_parameter' 
  AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'posstorage');

-- 切换超级用户尝试查询目标记录
SET ROLE postgres;
SELECT * FROM posstorage.system_parameter WHERE name = 'DEFAULT_OFD';
RESET ROLE;

如果超级用户能查到记录，说明是RLS规则导致普通用户不可见。

3. 验证主键约束的定义 #

排查主键是否为表达式索引/部分索引，可能存在表面值相同但计算后冲突的情况：

SELECT conname, condef 
FROM pg_constraint 
WHERE conrelid = (SELECT oid FROM pg_class WHERE relname='system_parameter' AND relnamespace=(SELECT oid FROM pg_namespace WHERE nspname='posstorage'))
  AND contype = 'p';

若主键包含函数（如lower(name)），需检查插入值与隐藏记录的函数计算结果是否冲突。

4. 检查表可见性映射（VM）和空闲空间映射（FSM） #

可见性映射标记页中是否有需要Vacuum的记录，若VM异常，会导致记录无法被识别为可见：

-- 查看表的可见性映射状态
SELECT * FROM pg_visibility('posstorage.system_parameter');

-- 手动刷新可见性映射
VACUUM ANALYZE posstorage.system_parameter;

5. 排查TOAST表异常 #

若name字段为大字段（如超长text/varchar），数据可能存储在TOAST表中，TOAST记录损坏会导致查询显示异常但主键约束冲突：

-- 获取目标表的OID
SELECT oid FROM pg_class WHERE relname='system_parameter' AND relnamespace=(SELECT oid FROM pg_namespace WHERE nspname='posstorage');

-- 查询对应的TOAST表（替换<表OID>为上面的结果）
SELECT * FROM pg_toast.pg_toast_<表OID>;

若TOAST表存在异常记录，可尝试VACUUM FULL重建TOAST表（注意操作会锁表）。

6. 检查事务ID冻结相关元数据 #

查看表的relfrozenxid，确认目标xid是否已超出冻结范围：

SELECT relfrozenxid, relminmxid 
FROM pg_class 
WHERE relname='system_parameter' AND relnamespace=(SELECT oid FROM pg_namespace WHERE nspname='posstorage');

如果relfrozenxid > 226114219，说明理论上该记录应被冻结但未处理，可能是表元数据损坏，可尝试：

-- 强制冻结表
VACUUM FREEZE posstorage.system_parameter;
-- 若无效，尝试CLUSTER重建表
CLUSTER posstorage.system_parameter USING system_parameter_pkey;

7. 排查pg_clog损坏 #

事务状态存储在pg_clog目录中，若该目录下的文件损坏，会导致事务状态识别错误。可通过pg_controldata检查事务ID范围：

pg_controldata | grep -E "Latest checkpoint's NextXID|Latest checkpoint's oldestXID|Latest checkpoint's oldestActiveXID"

若目标xid226114219小于oldestXID，说明该事务应被冻结，但pg_clog未正确标记，此时需谨慎使用pg_resetxlog修复（操作前必须做全量备份）。

内容的提问来源于stack exchange，提问作者byx