c# - 双时态设置中的 PostgreSQL 排除约束？

Question

我目前正在使用双时态应用程序，它使用 4 个时间戳存储数据条目:

Valid_from, Valid_to
Registration_from, Registration_to

当给定条目为valid_from和valid_to时的前两个状态，另外两个是当条目被 registered_from 时，并假定为真，直到 registration_to。

在这个时间设置中，我需要确保每一行在相同的 valid_from 和 valid_to 中都有唯一的列，在相同的 registration_from 中已知和 registration_to 跨越多个条目。

所以我需要在插入前检查每一行(伪代码):

If registration period is overlapping
    If Valid period is overlapping 
        Check if properties are the same 
            Throw error if they are

我试过这样的排除:

ADD Constraint exclusion_reg_{entity.InternalName}_registration_{string.Join('_', listOfAttributes)}_key Exclude using gist({string.Join(',', listOfAttributes.Select(x => x + " with =").ToList())} , registration WITH &&);

但我不确定我是否正确使用它。我目前总是遇到错误，因为检查是以相反的顺序进行的，而且是在不正确的检查中。有没有办法使这个排除检查嵌套，这样它只在注册重叠时检查验证重叠，如果是真的则抛出错误？

我如何在 PostreSQL 中处理它？<​​/p>

Answer 1

只需在排除约束中列出两个范围。

ALTER TABLE tbl ADD CONSTRAINT foo
EXCLUDE USING gist (attribute_1 WITH =, attribute_2 WITH =  -- more?
                  , tsrange(valid_from, valid_to) WITH &&
                  , tsrange(registration_from, registration_to) WITH &&);

在我几周前回答了您的相关(更简单)问题并提供了更多解释后，可以肯定地认为基本的基础知识已经很清楚了。其他人可能想先阅读此内容:

• How to ensure entries with non-overlapping time ranges?

为了强制执行您的约束，表达式的顺序甚至都不重要。考虑 basic definition in the manual of how exclusion constraints operate:

Exclusion constraints ensure that if any two rows are compared on thespecified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

这有效地加强了您的约束:只有当所有表达式的计算结果都为 true 时，换句话说，两个范围重叠并且所有属性完全匹配，约束会引发异常。

但是，由于约束是通过使用相应的多列 GiST 索引来实现的，所以毕竟表达式的顺序对性能很重要。 The manual:

A multicolumn GiST index can be used with query conditions thatinvolve any subset of the index's columns. Conditions on additionalcolumns restrict the entries returned by the index, but the conditionon the first column is the most important one for determining how muchof the index needs to be scanned. A GiST index will be relativelyineffective if its first column has only a few distinct values, evenif there are many distinct values in additional columns.

因此重新排列表达式，将具有最不同值的表达式放在列中的最前面。