PostgreSQL无事务‘查找或创建’accounts表遇空结果集问题

PostgreSQL并发会话是否可能出现无法互见数据的情况？ #

是的，这种情况完全可能发生，核心原因是PostgreSQL默认的READ COMMITTED隔离级别特性，以及你当前CTE写法的竞态条件：

当两个会话同时执行“查找或创建”操作（org_id=12345、account_number=NULL）时：

• 会话A执行CTE的查找阶段，此时没有已提交的匹配记录，准备插入。
• 会话B几乎同时执行查找阶段，由于会话A的插入尚未提交，会话B的查找看不到这条未提交数据，同样准备插入。
• 会话A完成插入并提交事务。
• 会话B尝试插入时，触发唯一约束冲突（你应该是用了部分唯一索引来限制org_id对应唯一的NULL账号），插入失败。
• 会话B的CTE中，existing部分是语句开始时的快照（看不到会话A已提交的记录），inserted部分没有数据，最终返回空结果集，导致抛出no rows in result set错误。

更优解决方案 #

放弃CTE的写法，改用PostgreSQL原生的INSERT ... ON CONFLICT ... RETURNING原子语句，这是处理“查找或创建”场景的标准方案，完全避免竞态条件，且性能更优。

第一步：确保约束正确 #

首先需要创建两个约束来覆盖所有场景：

1. 复合唯一约束：处理account_number非NULL时的唯一性

ALTER TABLE accounts ADD CONSTRAINT uq_accounts_org_id_account UNIQUE (org_id, account_number);

2. 部分唯一索引：处理account_number为NULL时，每个org_id只能有一条记录

CREATE UNIQUE INDEX uq_accounts_org_id_null_account ON accounts (org_id) WHERE account_number IS NULL;

第二步：原子“查找或创建”语句 #

PostgreSQL 15+版本（支持多冲突目标）

直接用一条语句覆盖两种冲突场景，插入成功返回新记录，冲突时返回现有记录：

INSERT INTO accounts (org_id, account_number)
VALUES ($1, $2)
ON CONFLICT (org_id, account_number) WHERE account_number IS NOT NULL
DO UPDATE SET org_id = accounts.org_id  -- 无意义更新，仅触发RETURNING返回现有记录
ON CONFLICT (org_id) WHERE account_number IS NULL
DO UPDATE SET org_id = accounts.org_id
RETURNING *;

PostgreSQL 14及以下版本

由于不支持多冲突目标，可通过指定约束名统一处理：

INSERT INTO accounts (org_id, account_number)
VALUES ($1, $2)
ON CONFLICT ON CONSTRAINT uq_accounts_org_id_account
DO UPDATE SET org_id = accounts.org_id
ON CONFLICT ON CONSTRAINT uq_accounts_org_id_null_account
DO UPDATE SET org_id = accounts.org_id
RETURNING *;

方案优势 #

• 原子性：整个操作在数据库内部作为单个原子步骤执行，彻底避免并发竞态条件。
• 性能：比CTE写法减少了冗余查询，数据库层面的冲突处理更高效。
• 可靠性：无论并发情况如何，要么插入新记录返回，要么返回已存在的匹配记录，绝不会出现空结果集。

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