PostgreSQL exclusion constraints in a bitemporal setting?

Question

I am currently working with a bitemporal application, which stores data entries using 4 timestamps:

Valid_from, Valid_to
Registration_from, Registration_to

The first two state when the given entry is valid_from and valid_to, and the other two are when the entry has been registered_from, and assumed as true until registration_to.

In this time setting I need to make sure that each row has unique column within the same valid_from and valid_to known within the same registration_from and registration_to across multiple entries.

So I need to check every row before insertion (pseudo-code):

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

I have tried with an exclusion like this:

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

But I am not sure whether I am using it correctly. I am currently always getting an error, since the check is done in opposite order, and among the incorrect ones. Is there a way to make this exclusion check nested, such that it only check validation overlap if the registration is overlapping, and throws an error if this is true?

How do I go about that in PostreSQL?

Answer 1

Just list both ranges in the exclusion constraint.

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

It should be safe to assume that essential basics are clear after I answered your related (simpler) question with more explanation a couple of weeks ago. Others may want to read this first:

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

To enforce your constraint, the order of expressions doesn't even matter. Consider the basic definition in the manual of how exclusion constraints operate:

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

This effectively enforces your constraint: only if all expressions evaluate to true, in other words, both ranges overlap and all attributes match exactly, the constraint raises an exception.

However, since the constraint is implemented with the use of a corresponding multicolumn GiST index, the order of expressions matters for performance after all. The manual:

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

So rearrange expressions to place the ones with the most distinct values in the column first.