订单重复导入为何没被唯一约束拦住:业务键、NULL 与冲突验收

表里有自增主键,仍可能重复记录同一笔订单;增加唯一约束,也不能替团队判断空编号、重复回放与真正更正。本文限定 PostgreSQL 18,以十条导入记录解释业务身份与版本的区别,比较不可变事实和版本化状态两种实现,提供异常分流、并发冲突及迁移验收用例,让重复导入不再悄悄放大经营数据。

唯一约束只保护已经定义的键,不会自动识别“同一笔业务”。避免重复导入,要同时确定业务粒度、键的作用域、空值政策和同键异内容的处理办法。本篇限定 PostgreSQL 18、单一目标主库和经过授权的订单明细导入;SQL为设计示例,数值可复核,未宣称运行过数据库并发实验,不处理跨库原子提交或没有可信身份的自动实体合并。

先定义业务身份,自增主键只能证明行不同

假设导入的是订单当前明细,而不是支付流水或订单头。业务身份由 tenant_id、source_system、order_no、line_no 四项组成,分别表示租户、来源系统、订单号与明细行号;同一身份允许产生多个修订。另存 source_revision 表示该来源对该明细的可靠递增版本,金额 amount 使用同币种、不含税的十进制元。若实际输入是订单头,不能把本例明细键照搬过去。

数据库自动生成的 row_id 可以让每次插入都获得不同编号,却不阻止同一个来源明细被插入两次。文件名、批次号和导入行号用于追溯这次接收,也不宜直接充当业务身份:重导文件会变批次,业务本身没有因此变新。应分别保存接收身份、业务身份和修订身份,才能区分技术重试、业务更正与真正新增。

PostgreSQL 18约束文档说明联合唯一性约束的是列组合,主键同时要求唯一和非空。[1] 这给数据库提供了确定的执行边界,但组合内容仍由业务决定。省掉租户会把不同企业的订单误并,省掉来源会把ERP与售后系统的同号记录误并,省掉行号又会把一个订单的多条商品合为一行。

名称相似的两条客户记录是否同一主体,属于需要证据的实体匹配问题,不能靠增加唯一索引裁决。这里假设来源负责人已经批准四列身份及版本含义。没有可靠行号时,先进入身份待补区;若来源存在稳定替代标识,可以单独批准映射规则。不能临时取行位置、金额或商品名拼键,然后声称已恢复业务唯一性。

十条输入如何得到三条当前事实,而不是十笔销售

设下列十条按接收顺序到来,除注明外均为租户T1、来源ERP、订单O7;金额都是本例假设。R1为行1、版本1、100元;R2与R1完全相同;R3为行2、版本1、60元;R4为行号NULL、版本1、30元;R5再次接收到与R4相同的缺失行号内容;R6来自租户T2,其余为ERP、O7、行1、版本1、80元。NULL表示未知,不是字符串。

第一阶段可确认三条当前事实:T1行1为100、T1行2为60、T2行1为80,合计240元。R2是已确认身份与内容的重复;R4和R5都缺身份,需要分别记录接收证据并隔离,不能因为其他字段一样就认定是同一明细。六条接收金额合计400元,其中100元是已确认回放、60元属于待确认输入,不能把400元直接称为新增销售。

随后R7声称T1行1、版本1为120元,与已确认的同一版本100元冲突。系统应隔离R7并暂停该键的自动推进,而不是让最后到达者胜出。来源负责人核实R7为错误导出后,将该冲突关闭,并提供合法版本2:R8为T1行1、版本2、90元。修复证据和批准记录先完成,再允许新版本生效。

最后R9又回放T1行1、版本1的100元,R10重送T1行2、版本1的60元。最终当前结果应为三条:T1行1版本2金额90,T1行2版本1金额60,T2行1版本1金额80,合计230元。十条接收金额之和是770元,但这个数混合重试、异常和不同版本,没有本例销售指标的业务含义。

正确更新是把240中的旧贡献100替换成90,得到230,而不是再加90变成330。将十条输入整体重放,当前事实仍应为上述三条、230元;冲突与身份缺失仍须按记录呈现。验收要比较四列键、版本、金额及异常集合,不能只比较总额,因为错并一条与多收一条可能在数字上抵消。

可空唯一键的选择,必须服从NULL代表的业务含义

如果旧表只有UNIQUE (tenant_id, source_system, order_no, line_no),且line_no允许NULL,默认唯一性规则可能允许R4、R5同时进入。PostgreSQL默认不把两个NULL视为相等的唯一值;NULLS NOT DISTINCT可以改变这一比较规则。[1] 数据库没有失灵,它执行的是不同于业务期望的空值政策。

本例更适合四列全部NOT NULL,并建立联合主键。原因不是“空值总是不允许”,而是缺少行号就无法判断对象身份。若改用UNIQUE NULLS NOT DISTINCT,第二条缺行号记录会冲突,却仍不能证明第一条就是正确业务对象。把许多未知对象压成一个未知对象,同样会损坏事实,不能用去重率上升证明修复成功。

NULLS NOT DISTINCT适合另一种已经明确的语义,例如配置表约定region为空表示唯一的默认配置,在同一租户与指标下只允许一条默认项。此时“没有区域”是一个受控类别,而不是未知客户或未知订单行。先写清类别契约,再选择约束。不要用COALESCE(line_no,0)随意造哨兵值;若0本身合法,未知与真实0会发生碰撞。

SQL空值筛选与统计方法处理的是查询阶段的三值逻辑,本篇处理写入阶段的身份约束。两者应一起检查,但不能互相替代:查询不漏NULL,不代表导入没有重复;唯一约束阻止重复,也不证明缺失字段足以支持业务判断。规范化大小写、空白和编码前同样要保留原值并验证碰撞,不能顺手改变来源身份。

先在暂存区分流,再把确定事实交给约束

建议暂存区保存import_id、接收行号、原始业务键、来源版本、规范化字段、内容摘要和判定结果。它允许暂存重复或缺失输入,用于解释接收过程;正式事实表才执行批准的非空业务键。异常区不是垃圾桶:每条应有原因、原记录位置、负责人和复验条件,且受与业务数据相应的访问控制。

分流可依次检查:身份字段是否齐全;金额、币种和单位是否可解释;同一业务键同一版本是否内容一致;同键不同版本是否可排序;版本来源是否可信。重复同内容可以记录回放而不新增事实;同版本异内容应报冲突;不可比较的版本暂停自动处理。不能让“最新接收时间”替代来源版本,两个来源的编号也不能跨作用域比较。

同批先发现冲突再挑选候选。若一个新批次同时给出同键同版本的100和120,尚没有来源裁定,应将这一组置为冲突,不能随机保留一行。若同键有合法版本1与2,可依已批准规则选最终候选,并保留各版本证据。排序后取第一条只有在排序字段可靠、同版本冲突已排除时才有意义。

PostgreSQL的ON CONFLICT DO UPDATE不允许同一条语句重复影响同一个目标行,重复候选可能触发基数错误。[2] 因此批量插入前的分组检查也是语句正确性的前提。但暂存查重不是最后防线:其他批次可能同时处理相同键,正式表的唯一约束和事务协议仍必须保留,不能把查重SQL通过理解成全系统已经唯一。

比较不可变事实与版本化状态两条实现

路径A只接收不可变事实:同键同内容视为重送,同键异内容进入人工或业务更正流程,正式记录不被普通重试覆盖。它适合来源不能提供可靠修订号、数据量有限且更正需要批准的交付。修正可以通过新批准批次构建替代结果,再核对后切换;代价是更正延迟和人工工作,不能把这些成本藏在“零重复”指标后面。

路径B保留修订账本并维护当前状态。修订表以四列业务键加source_revision为唯一身份,保存规范化业务字段或可核对内容、来源证据和结果;当前表以四列业务键为主键,只接受获准且更新的版本。它适合稳定递增版本、频繁更正与可重放来源,但需要更多存储、冲突处理和维护。两张表及处理结果须在同一目标事务中一致提交。

例如当前表结构可表达为:CREATE TABLE order_current (tenant_id text NOT NULL, source_system text NOT NULL, order_no text NOT NULL, line_no integer NOT NULL, source_revision bigint NOT NULL, amount numeric(18,2) NOT NULL, PRIMARY KEY (tenant_id, source_system, order_no, line_no), CHECK (source_revision > 0));。这是表达约束的示例,生产还需补齐批准的值域、权限、来源代次和审计字段。

不要把内容摘要当成全部证据。应明确规范化覆盖哪些业务字段,金额100与100.00是否同义,哪些备注或接收时间不参与业务内容比较。摘要可加速对比,冲突裁定仍应能核对受控原字段;不能把完整敏感载荷写进普通错误日志。字段契约改变后需版本化规范化规则,否则同一原记录可能突然变成“新内容”。

Debezium 3.7文档说明其通常提供至少一次交付,并讨论Kafka Connect条件下的恰好一次支持。[6] 这说明接收端必须核对实际部署的交付边界。本文不把任何消息层保证扩展成订单表、汇总和外部通知的整体保证;即使输入只到一次,两个导入入口仍可能提交同一业务记录。

版本化写入怎样分别处理重送、冲突和更正

路径B的一次处理可以按以下协议设计:开启事务,尝试登记修订身份;若已存在,锁定并读取已有修订,比较规范化内容。相同则返回既有结果,不重复改当前状态;不同则记录冲突并停止该键自动推进。若是新修订,确认不存在未解决冲突且来源顺序有效,再尝试推进当前状态,并把接受或过期结果写入同一事务。阻断冲突也要成为可查询的持久状态。

当前状态的核心推进可表达为:INSERT INTO order_current AS c (tenant_id, source_system, order_no, line_no, source_revision, amount) VALUES ($1,$2,$3,$4,$5,$6) ON CONFLICT (tenant_id, source_system, order_no, line_no) DO UPDATE SET source_revision=EXCLUDED.source_revision, amount=EXCLUDED.amount WHERE EXCLUDED.source_revision > c.source_revision RETURNING source_revision, amount;。参数由受控导入程序绑定,不能让模型拼接未验证输入。

这个语句只负责版本更大才更新,不独自完成同版本内容冲突检查。若仅靠WHERE条件,同版本100与120可能都表现为没有更新,错误会被吞掉。前述修订账本负责对同一版本作内容核对;有未解决冲突时,还需要所有写路径遵守同一业务键门控。门控可通过预建并锁定的键状态行实现,不能只在进程内保存一个布尔变量。

对R8,事务应留下版本2及90元的修订证据,并将当前行从100改为90;对R9,已存在版本1及100元的记录,返回历史重送状态,不能把当前版本降回1。若系统维护预聚合金额,需要与当前行变化同事务调整,或按已提交事实重算;本例选择重算三条当前行得到230,减少跨表增量补偿的复杂性。

遇到返回零行,要区分旧版本、相同版本重送、未解决冲突和其他错误,不能统一报成功。将原始接收数量、已接受修订数量、当前事实数和未决异常数分开。它们描述不同对象,不能用“导入成功十条”让读者以为形成了十条有效销售明细。前述协议是待集成验证的设计,不是完整可直接上线的驱动实现。

冲突裁定必须绑定原业务键、修订及被判定的具体内容。R7再次回放时,若与已经裁定的错误导出完全相同,应返回原拒绝结果,不反复重开同一冲突;出现未经裁定的另一份异内容,仍须阻断。保留批准与拒绝的可恢复记录,不能只把异常表清空后说问题已经解决。否则整批重放虽然不增加金额,却可能不断中断合法版本服务。

并发与软删除会改变唯一性的有效范围

先查询不存在、随后插入,两个会话都可能在各自检查时看到空结果。数据库约束负责最终裁决,应用应处理等待、冲突与回滚,而不是把查前检查当成锁。Read Committed下,DO NOTHING可能因另一事务的结果跳过插入,即使该行不在当前语句快照里;后续读取应使用新的语句并核对主库状态。[3] 不要为了让重试成功而生成新业务键。

对路径B,可为一个业务键串行处理其修订与冲突状态,事务内完成账本比较和当前行推进。这样同一键版本2与3竞争时可复核最终为3,版本1回放不会降级。多个键按统一顺序处理,避免相反锁序形成死锁;事务内不等待模型回答或人工确认。需要人工裁定时先提交受阻状态,释放事务,再由新批准动作继续。

另一种常见陷阱是仅对deleted_at IS NULL的行建立部分唯一索引。PostgreSQL支持在满足谓词的子集内保证唯一。[4] 这适合明确允许历史记录同号、仅活动记录唯一的业务,却意味着软删除后的旧身份可能再次插入。本例导入若要求历史重送永远不能变成新销售,不能只依赖活动子集约束;应另保留完整修订或接收身份控制。

唯一性也有部署边界。分表、分区、其他数据库及旁路脚本需要另行证明实际约束覆盖,不将单表结论直接推广为全局身份保证。对不同来源的相同订单号,本例允许并存;如果企业要跨来源归并,应交由明确的映射契约,不能在导入程序里悄悄省略source_system作为“优化”。

六组失败验收,每组都保留输入与复验证据

第一组:把R1原样重送两次,再按不同文件名重导。预期当前事实仍是一条100元,修订身份不增加;接收台账可以增加。检查实际业务键及唯一约束,而非自增row_id。若变成两条,修正键的作用域与正式写路径,再跨两个入口并发重送,核对结果不随文件名变化。

第二组:输入R4、R5,并加入一条合法line_no=0的对照。按企业若已批准0为有效行号,0应正常保留,NULL仍隔离;若0不合法则分别报告值域错误。不能把两者都变成0合并。检查规范化前后值、隔离原因及正式表非空规则,修正后复验缺失、空字符串、0和合法正数的实际解析路径。

第三组:同键同版本先后送100与120,再放进同一新批次。预期冲突可见,不能静默覆盖;未解决时新修订也不自动推进。检查账本、门控和异常状态是否一致提交。修复后先证明受阻,再提供来源裁定并解除阻断,最后用R8与R9复验合法更新到90且旧版本不能复活。

第四组:两个租户各送O7行1,再送同租户O7行2。预期三种业务身份均可保留;第一阶段合计240元。检查键集合,不能只看行数。若误并,恢复来源证据、纠正联合键并重新构建隔离结果,核对每条金额后才切换正式使用,不能靠手改总额掩盖丢失明细。

第五组:让两个会话在同键插入处相遇,并分别模拟第一事务提交与回滚。预期提交时后者返回已存在结果或受控冲突,回滚时可按原身份继续;不能出现两条当前行。检查事务结束顺序、返回状态及主库记录。若使用MERGE替代,必须重新测试竞争行为,不能假定它与ON CONFLICT相同。[3]

第六组:在批量候选中保留两个相同目标键,或让迁移前旧表已经存在冲突。预期候选分流或迁移验收阻止上线,而不是绕过约束。检查批次分组、异常明细与索引有效状态;清理应依据业务裁定保全原证据,不能任意删除“多余行”。修正后重跑全套十条输入及恢复重放,核对最终230元、三条当前事实和全部未决身份异常。

上线先验收约束有效,再讨论吞吐与维护

存量表增加唯一约束前,应按拟采用的规范化键扫描缺失、同键同版本异内容、跨来源误并和软删除历史。把待裁定对象交给业务负责人,先在影子结果中确认差异。迁移期间还要控制新写入,否则扫描通过后仍可能出现新的冲突;静态检查只是一个时点证据,不能代替切换窗口的写入协调。

PostgreSQL的并发建索引减少对正常写入的阻塞,但需要额外扫描和等待;失败可能遗留INVALID索引,且某些失败阶段仍可能影响唯一性检查。[5] 因此不能只因索引名称存在就宣布完成。检查创建命令最终结果、索引有效状态及实际插入反例,按运行规程处理失败对象,避免自动忽略错误后继续开放新写路径。

规模扩大时,重点观察热点业务键的等待、批次冲突率、异常积压、修订账本大小和重算耗时。完整账本不是无限免费:其保留窗口至少要覆盖批准的回放范围,超出窗口的旧输入应进入重建或人工检查,不因账本过期就自动当新事实。索引列越宽,维护与存储代价也需通过目标负载测试,不能用本文三条数据推算生产吞吐。

可复用交付清单应逐项填写:事实粒度;四列作用域;空值与规范化政策;来源版本证据;同版本冲突处理;当前状态推进规则;软删除与回放窗口;并发与迁移结果;异常负责人;最终键集合及金额。上线时交付输入样本、预期输出和真实测试记录。唯一约束成为可靠边界,依靠的是这些约定与证据共同成立,而不是给表增加一个名为unique的对象。

参考资料

  1. [1] PostgreSQL 18 — Constraints(查阅2026-11-14)
  2. [2] PostgreSQL 18 — INSERT / ON CONFLICT
  3. [3] PostgreSQL 18 — Transaction Isolation
  4. [4] PostgreSQL 18 — Partial Indexes
  5. [5] PostgreSQL 18 — CREATE INDEX
  6. [6] Debezium 3.7 — Exactly once delivery
返回洞察
鲁ICP备2024109755号-2
可拖动移动。右键、长按或按 Shift+F10 可选择停靠位置。