Ограничения исключения 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?

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 &&);

Можно с уверенностью предположить, что основные основы ясны после того, как я ответил на ваш связанный (более простой) вопрос с дополнительными пояснениями пару недель назад. Другие могут сначала прочитать это:

• Как обеспечить записи с неперекрывающимися временными диапазонами?

Чтобы обеспечить соблюдение вашего ограничения, порядок выражений даже не имеет значения. Рассмотрим основное определение в руководстве того, как действуют ограничения исключения:

Ограничения исключения гарантируют, что при сравнении любых двух строк в указанных столбцах или выражениях с использованием указанных операторов по крайней мере одно из этих сравнений операторов вернет false или null.

Это эффективно применяет ваше ограничение: только если все выражения оцениваются как true, in other words, оба диапазона перекрываются, и все атрибуты точно совпадают, ограничение вызывает исключение.

Однако, поскольку ограничение реализуется с использованием соответствующего многоколоночного индекса GiST, порядок выражений в конце концов имеет значение для производительности. Руководство:

Многоколоночный индекс GiST можно использовать с условиями запроса, которые включают любое подмножество столбцов индекса. Условия для дополнительных столбцов ограничивают записи, возвращаемые индексом, но условие для первого столбца является наиболее важным для определения того, какую часть индекса необходимо просканировать. Индекс GiST будет относительно неэффективным, если его первый столбец имеет только несколько различных значений, даже если в дополнительных столбцах много различных значений.

Поэтому измените порядок выражений, чтобы сначала поместить в столбец те, которые имеют самые разные значения.