From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | Dionisis Kontominas <dkontominas(at)gmail(dot)com> |
Cc: | Thomas Kellerer <shammat(at)gmx(dot)net>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint? |
Date: | 2023-07-26 09:39:38 |
Message-ID: | CAFCRh--OBg2szyN=YZ1HGya1fyqKirvPbP4A2FLzjWdP4NfSeA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontominas(at)gmail(dot)com>
wrote:
> On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer <shammat(at)gmx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2023-07-26 09:56:25 | Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint? |
Previous Message | Dionisis Kontominas | 2023-07-26 09:25:46 | Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint? |