How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

Dionisis Kontominas schrieb am 26.07.2023 um 11:00:

Hello all,

In the Subject I mention what I am intending to do. Letme put some context; this is my table:

portal_user_role
(
    f_id INTEGER NOT NULL,
    f_portal_user_id INTEGER NOT NULL,
    f_portal_role_id INTEGER NOT NULL,
    f_is_active BOOLEAN NOT NULL,
    f_is_deleted BOOLEAN NOT NULL,
    f_start_date DATE NOT NULL,
    f_end_date DATE,
    f_created_on TIMESTAMP WITH TIME ZONE NOT NULL,
    f_updated_on TIMESTAMP WITH TIME ZONE,
    f_created_by CHARACTER VARYING(255) NOT NULL,
    f_updated_by CHARACTER VARYING(255),
    CONSTRAINT pk_portal_user_role PRIMARY KEY (f_id),
    CONSTRAINT fk1_portal_user_role FOREIGN KEY (f_portal_user_id) REFERENCES portal_user (f_id),
    CONSTRAINT fk2_portal_user_role FOREIGN KEY (f_portal_role_id) REFERENCES portal_role (f_id),
    EXCLUDE USING gist (f_portal_user_id WITH =,
                        f_portal_role_id WITH =,
    DATERANGE(f_start_date, COALESCE(f_end_date, 'infinity'), '[]') WITH &&)
);

So, this table has a range of dates [f_start_date, f_end_date] that I
do not want two records to overlap, for the same user, the same role
and also when the f_is_deleted is TRUE only.

I do not care for the records when the f_is_deleted is FALSE on them; i.e. they should not be part of the restriction/constraint. 

How can I achieve this?

You can add a WHERE clause to the exclusion constraint (the condition must be enclosed in parentheses though):

EXCLUDE USING gist (f_portal_user_id WITH =, f_portal_role_id WITH =, DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_deleted)

Note that you don't need COALESCE(f_end_date, 'infinity') because a daterange will treat null as infinity anyways.

Hi Thomas,

Thank you very much for your reply and comment.

I am trying to avoid writing trigger code to handle this requirement.

I will do so and try your suggestion.

I believe also that the partial constraint you propose to me should be in
the end: ... WHERE (NOT f_is_deleted) as I do not want the deleted
records to participate in the constraint logic.

Kindest regards,
Dionisis

On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer <shammat@gmx.net> wrote:

On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontominas@gmail.com>
wrote:

On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer <shammat@gmx.net> wrote:

Dionisis Kontominas schrieb am 26.07.2023 um 11:00:

do not want two records to overlap, for the same user, the same role
and also when the f_is_deleted is TRUE only.
I do not care for the records when the f_is_deleted is FALSE on them;

i.e. they should not be part of the restriction/constraint.

How can I achieve this?

EXCLUDE USING gist (f_portal_user_id WITH =, f_portal_role_id WITH =,
DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_deleted)

But that requires the btree_gist extension [1]https://www.postgresql.org/docs/current/btree-gist.html extension, no?

Just confirming, because I'm been considering a similar approach for
storing chunks of large files (> 1GB),
to enforce those chunks don't overlap, per-"file". Seems ideal to enforce
no-overlap, but OTOH,
you can't seem to see how to enforce "no-holes" for chunks. One concern is
the cost of adding that
enforcement of no-overlap. Most "files" will be small (a few bytes to a
single digit MBs), while some
definitely go into multi-GB territory. So how well do exclusion constraints
scale to 100K or 1M rows?
What's their time-complexity? In other words, should "smaller" (i.e. < 1MB)
"files" go into a separate
table w/o an exclusion constraint and w/o chunking, while only the larger
ones go to the chunked table?

Thanks, --DD

[1]: https://www.postgresql.org/docs/current/btree-gist.html

Dominique Devienne schrieb am 26.07.2023 um 11:39:

On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontominas@gmail.com <mailto:dkontominas@gmail.com>> wrote:

Dionisis Kontominas schrieb am 26.07.2023 um 11:00:

do not want two records to overlap, for the same user, the same role
and also when the f_is_deleted is TRUE only.
I do not care for the records when the f_is_deleted is FALSE on them; i.e. they should not be part of the restriction/constraint. 
How can I achieve this?

    EXCLUDE USING gist (f_portal_user_id WITH =, f_portal_role_id WITH =, DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_deleted)

But that requires the btree_gist extension [1] extension, no?

Yes, but that would also be the case if you didn't include the WHERE clause.

The "WITH =" is the reason you need the btree_gist extension.

So how well do exclusion constraints scale to 100K or 1M rows?
What's their time-complexity?

They are using a GIST index, so I would expect all restrictions and advantages that apply
to GIST indexes in general, also apply to exclusion constraints.

The main drawback is most probably the slower update compared to a Btree index.

Unless you have a really high update frequency, I wouldn't worry about that for such a small table.