Thread

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

   Dionisis Kontominas <dkontominas@gmail.com> — 2023-07-26T09:00:13Z

   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
   

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

   shammat@gmx.net — 2023-07-26T09:18:21Z

   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.
   
   
   
   

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

   Dionisis Kontominas <dkontominas@gmail.com> — 2023-07-26T09:25:46Z

   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.
   >
   >
   >
   

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

   Dominique Devienne <ddevienne@gmail.com> — 2023-07-26T09:39:38Z

   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
   
   [1]: https://www.postgresql.org/docs/current/btree-gist.html
   

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

   shammat@gmx.net — 2023-07-26T09:56:25Z

   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.