Обсуждение: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

Поиск
Список
Период
Сортировка

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

От
Dionisis Kontominas
Дата:
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?

Also, should I post this question on pgsql-sql as more appropriate?

Thank you In Advance!

Regards,
Dionisis





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

От
Thomas Kellerer
Дата:
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.

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

От
Dionisis Kontominas
Дата:
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:
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.


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

От
Dominique Devienne
Дата:
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] 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

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

От
Thomas Kellerer
Дата:
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.