PostgreSQL 一次由 string_agg 引发的数据错位 Bug 深度复盘

背景在一次日常数据排查中我们发现系统中某个合作伙伴以下称PartnerA的验证状态在界面上显示异常——明明该合作伙伴的数据是正常的却被系统标记为校验失败而另一个真正有问题的合作伙伴以下称PartnerB的错误信息却莫名其妙地出现在了PartnerA的详情里。更诡异的是这个问题并非每次都能稳定复现时而出现、时而消失极难定位。经过深入排查最终将问题根源锁定在一段看似无害的 SQL 聚合查询上。业务背景简介系统中存在一张核心的合作伙伴主数据表下文统称partner_data表其核心字段如下字段脱敏含义group_id合作伙伴集团 ID多个成员共享同一 group_idmember_id合作伙伴成员 ID唯一标识每一行check_status数据校验状态如Success/Failedcheck_msg数据校验失败时的错误原因描述业务上一个group_id对应多个member_id一对多关系。前端需要展示在某个group_id下所有成员的 ID、校验状态、错误信息。为了减少查询次数、降低传输数据量开发者采用了行转列的思路在 SQL 层将同一group_id下的多行数据聚合为一行三个字段分别用特殊分隔符拼接成字符串由后端程序 split 后再逐一解析对应关系。原始 SQL 实现SELECTgroup_id,string_agg(member_id,#)ASall_member_ids,string_agg(check_status,#)ASall_check_status,string_agg(check_msg,#)ASall_check_msgFROMpartner_dataGROUPBYgroup_idORDERBYgroup_idDESCLIMIT20OFFSET0;后端代码伪代码String[]memberIdsresult.getAllMemberIds().split(#);String[]statusesresult.getAllCheckStatus().split(#);String[]messagesresult.getAllCheckMsg().split(#);for(inti0;imemberIds.length;i){// 假设三个数组下标一一对应process(memberIds[i],statuses[i],messages[i]);}看起来逻辑清晰、简洁高效但这里隐藏着两个足以致命的陷阱。Bug 一string_agg无ORDER BY三列聚合顺序各自独立、随机不定问题根因PostgreSQL 官方文档对string_agg的描述非常明确If ORDER BY is not specified, the order of the aggregated values is implementation-dependent.即在没有ORDER BY子句时string_agg聚合的顺序由数据库底层决定不保证任何顺序。更关键的是三个独立的string_agg调用各自有各自的执行顺序彼此之间毫无关联。这意味着三列的聚合顺序完全可以各不相同。具体还原假设group_id G001下有三条记录member_idcheck_statuscheck_msgM001SuccessNULLM002FailedofficeCountry is emptyM003SuccessNULL原始 SQL 执行后三列可能产生如下完全合法但错位的结果all_member_ids: M003#M001#M002 ← 顺序 A all_check_status: Failed#Success#Success ← 顺序 B与 member_id 顺序不同 all_check_msg: officeCountry is empty ← 见 Bug 二后端 split 后memberIds[0] M003 → statuses[0] Failed ❌ M003 实际是 Success memberIds[1] M001 → statuses[1] Success memberIds[2] M002 → statuses[2] Success ❌ M002 实际是 Failed结果校验状态完全错位M003 被错误标记为 FailedM002 的错误被掩盖。为什么时而复现、时而消失PostgreSQL 的并发写入、VACUUM、autovacuum、表膨胀、TOAST 机制等都会影响物理存储布局进而影响没有 ORDER BY 时的扫描顺序。在数据量小、写入稳定时三列恰好顺序一致一旦表经历频繁更新顺序就可能悄然改变——这正是该 Bug 间歇性出现的原因。Bug 二string_agg静默跳过 NULL导致三列数组长度不一致问题根因PostgreSQLstring_agg的另一个重要特性它会自动忽略 NULL 值既不将 NULL 参与拼接也不为 NULL 插入分隔符占位。这意味着如果某一行的check_msg为 NULL则该行在all_check_msg的拼接结果中完全消失而不是留下一个空槽位。具体还原同样是 G001 的三条记录其中 M001、M003 的check_msg为 NULL-- 即使三列恰好按相同顺序聚合假设都按 M001, M002, M003all_member_ids:M001#M002#M003→ split →[M001,M002,M003]长度3all_check_status:Success#Failed#Success→ split →3个元素 长度3all_check_msg:officeCountry is empty→ split →1个元素 ❌ 长度1后端按下标对应memberIds[0] M001 → messages[0] officeCountry is empty ❌ 实际 M001 没有 msg memberIds[1] M002 → messages[1] 数组越界异常 / 取到 null ❌结果错误信息挂错到了没有问题的成员上真正有问题的成员反而得不到正确的错误原因。两个 Bug 的叠加效应在真实场景中Bug 一乱序和 Bug 二NULL 被吞同时存在、相互叠加造成三列数组顺序不一致三列数组长度不一致同一个group_id下成员 ID、校验状态、错误信息三者无法正确对应异常数据如check_msg为 NULL 的脏数据因 NULL 被跳过在错误信息维度上彻底隐身系统无法感知。Bug 三修复不完整——遗漏了另一处相同的查询在排查过程中还发现系统中存在两处类似的聚合查询一处分页查询被修复了另一处条件筛选查询simpleListByValid在修复时仅对check_status和check_msg加了ORDER BY却遗漏了member_id本身-- 修复不完整的版本仍有 Bugstring_agg(member_id,#)ASall_member_ids,-- ❌ 无 ORDER BYstring_agg(COALESCE(check_status,),#ORDERBYmember_id)ASall_check_status,string_agg(COALESCE(check_msg,),#ORDERBYmember_id)ASall_check_msgall_member_ids仍然无序而后两列已按member_id排序——三列排序基准不统一错位问题依然存在。根本修复方案核心原则所有参与行转列的string_agg列必须使用完全一致的ORDER BY基准且所有可能为 NULL 的字段必须用COALESCE处理为空字符串占位。SELECTgroup_id,string_agg(member_id,#ORDERBYmember_id)ASall_member_ids,string_agg(COALESCE(check_status,),#ORDERBYmember_id)ASall_check_status,string_agg(COALESCE(check_msg,),#ORDERBYmember_id)ASall_check_msgFROMpartner_dataGROUPBYgroup_idORDERBYgroup_idDESCLIMIT20OFFSET0;修复要点统一ORDER BY member_id三列聚合按同一基准排序保证顺序严格一致COALESCE(field, )NULL 值被替换为空字符串不再被跳过确保三列数组长度始终相等所有同类查询同步修复避免修了一处、遗漏另一处的不完整修复。举一反三这类 Bug 的通用识别模式凡是代码中出现以下模式均需高度警惕-- 危险信号 ⚠️string_agg(col_a,separator)ASagg_a,string_agg(col_b,separator)ASagg_b,-- col_b 可能为 NULLstring_agg(col_c,separator)ASagg_c-- col_c 可能为 NULL配合后端代码String[]aresult.getAggA().split(separator);String[]bresult.getAggB().split(separator);// 按下标 i 对应 a[i] 与 b[i]只要以下任一条件成立就存在对应错位的风险风险条件后果任意一列string_agg缺少ORDER BY顺序不确定各列可能乱序各列ORDER BY基准不一致顺序标准不同仍可能错位任意一列的源字段存在 NULL 值该列数组长度可能小于其他列下标越界或对应错误总结原始 SQL修复后 SQLstring_agg排序无随机不定统一ORDER BY member_idNULL 值处理直接聚合NULL 被跳过COALESCE(field, )占位三列数组长度不保证一致严格一致三列顺序对应不保证一致严格一致Bug 覆盖范围两处查询均有问题两处同步修复这个 Bug 的教训在于SQL 聚合函数的隐式行为无序、忽略 NULL与业务代码的显式假设有序、长度一致之间存在致命的语义鸿沟。在涉及多列string_agg行转列的场景中ORDER BY 和 COALESCE 不是可选项而是必要的正确性保障。“The devil is in the details.”— 在数据库聚合里这句话尤为贴切。