postgresql - 如何在 PostgreSQL 数据库中声明范围重叠约束？

Question

假设我们有一个定义如下的表:

range (
  id bigint primary key,
  colourId int references colour(id),
  smellId int references smell(id),
  from bigint,
  to bigint
)

这个表实际上是一个非常大的表的缩减 View :

item (
  id bigint primary key,
  colourId int references colour(id),
  smellId int references smell(id),
  CONSTRAINT item_colour_smell_unique UNIQUE (colour, smell, id)
)

我想翻译 range 表中的 item_colour_smell_unique 约束。它应该观察 [from, to] 范围的重叠，同时考虑 colourId 和 smellId 列值。

Answer 1

请注意，任何基于触发器的解决方案本质上都是不安全的，不受竞争条件的影响，例如当两个并发事务插入一个范围冲突的行时，由于“隔离”ACID 属性(只能看到提交的数据)，它们都不会看到另一个冲突的行。

一些解决方案:

• 使用显式锁定表的过程来强制序列化插入。
• 将 [from, to] 范围拆分为 [from, from+1, ..., to-1, to] 并为每个范围插入一行。这样您就可以在“范围”表上使用一个简单的 UNIQUE INDEX。

PostgreSQL 开发人员 Jeff Davis has been writing about this lately并将在 PostgreSQL 8.5 中实现范围冲突约束