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

Asked
Viewd389

1

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

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

此表实际上是大大的表的缩小视图:

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

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

3 个答案

3

请注意,任何基于触发器的解决方案在竞争条件下(例如,当两个并发事务插入具有冲突范围的行时,由于“隔离” ACID属性(只能看到已提交的数据),它们中的任何一个都不会看到另一个冲突的行。

一些解决方案:

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

PostgreSQL开发人员Jeff Davis 一直在写 最近对此事和将在PostgreSQL 8.5中实现范围冲突约束

  • 添加了范围冲突限制,请参阅https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRAINT

    Oroboros102January 30, 2019 15:13
0

这并不能完全给您答案,但是听起来您可能想利用触发器。

1

没有标准的“重叠”约束。您将必须通过一些触发器来构建自己的触发器。虽然已经有8.5的讨论。

您可能会发现“ seg”模块也很有用。请参见手册-附录F。附加提供的模块