PostgreSQL跨表条件下Partial Unique Index实现方案咨询

原表结构 #

create type status as enum ('ACTIVE', 'NOT_ACTIVE');

create table test (
  id uuid not null default gen_random_uuid(),
  name character varying (255) not null,
  stat status not null default 'ACTIVE'::status,
  next_id uuid,
  primary key (id),
  foreign key (next_id) references test(id)
);

create table details (
  id uuid not null default gen_random_uuid(),
  test_id uuid not null,
  cid bytea not null,
  primary key (id),
  foreign key (test_id) references test(id)
);

需求说明 #

当关联的test表中stat字段等于'ACTIVE'时，对details表的cid字段施加部分唯一索引约束。已知PostgreSQL中部分约束可通过唯一索引或排除约束实现，但stat字段属于关联的test表，无法直接在details的部分索引中引用。

用户当前方案 #

在details表中冗余stat字段，通过触发器同步test表的stat值，再创建部分唯一索引：

create table details (
  id uuid not null default gen_random_uuid(),
  test_id uuid not null,
  cid bytea not null,
  stat status not null,
  primary key (id),
  foreign key (test_id) references test(id)
);

create unique index on details(cid) where (stat = 'ACTIVE');

问题解答 #

1. 是否有其他实现该需求的方案？ #

有两种替代方案，但各有局限性：

• 排除约束结合自定义函数：创建排除约束，通过函数获取关联test表的stat值，约束逻辑为当test.stat = 'ACTIVE'时，cid不能重复。但这种方式每次操作details都要关联查询test，无法利用索引优化，性能损耗大，高并发场景不适用。
• 物化视图+唯一索引：创建包含details.cid和test.stat的物化视图，在视图上创建where stat='ACTIVE'的唯一索引。但物化视图无法实时同步数据，需要手动或定时刷新，仅适用于对数据一致性要求较低的场景。

2. 是否存在无需触发器同步stat字段值的实现方式？ #

存在，但都无法保证约束的可靠性或性能：

• 检查约束+函数：创建检查约束，调用函数验证当关联test.stat为ACTIVE时cid无重复。但PostgreSQL的检查约束无法处理并发竞态问题，两个事务同时插入相同cid且关联test为ACTIVE时，会绕过约束导致数据重复。
• 触发器直接验证：在details的BEFORE INSERT/UPDATE触发器中，查询关联test的stat，若为ACTIVE则检查cid是否重复。同样存在并发竞态问题，无法可靠保证唯一性。

相比之下，你当前的冗余字段+触发器方案是最可靠且性能最优的选择：触发器实时同步stat值，部分唯一索引可高效保证唯一性，同时避免了并发场景下的竞态问题，还能利用索引提升查询效率。

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