Обсуждение: Exclude constraint on ranges : commutative containment : allow onlycomplete containment

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 1/29/20 8:12 AM, Achilleas Mantzios wrote:
> Hello Dear Postgresql ppl,
> I have a table with date ranges and need to express the following 
> constraint : allow overlaps only if there is complete containment, e.g.
> allow values in rows like :
> [2020-01-01,2020-01-31)
> [2020-01-02,2020-01-10)
> [2020-01-10,2020-01-20)
> 
> but disallow rows like
> 
> [2020-01-02,2020-01-10)
> [2020-01-08,2020-01-11)

I'm missing something. Can you provide a more complete example?

> 
> I think that writing a new commutative range operator e.g. |<@@> which 
> would return true if the left operand is either contained by or contains 
> the right operand and false otherwise would solve this, I am just 
> wondering if there is a more elegant and economical way to express this. 
> (besides writing a trigger which is always an option).|
> 
> -- 
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 29/1/20 8:43 μ.μ., Michael Lewis wrote:

> If the new value does not overlap with any existing, allow. If it does 
> overlap, then it must be fully contained by the existing element, or 
> the existing element must be fully contained by it. Else, reject. Is 
> that right?


Exactly.

On 29/1/20 8:32 μ.μ., Adrian Klaver wrote:
> On 1/29/20 8:12 AM, Achilleas Mantzios wrote:
>> Hello Dear Postgresql ppl,
>> I have a table with date ranges and need to express the following constraint : allow overlaps only if there is
completecontainment, e.g.
 
>> allow values in rows like :
>> [2020-01-01,2020-01-31)
>> [2020-01-02,2020-01-10)
>> [2020-01-10,2020-01-20)
>>
>> but disallow rows like
>>
>> [2020-01-02,2020-01-10)
>> [2020-01-08,2020-01-11)
>
> I'm missing something. Can you provide a more complete example?
Dear Adrian,
I can give an example, lets say that we want to model the concept of budget, and we allow basic complete budgets
coveringa specific time period (daterange) which will have predictions and also 
 
matched actual transactions (Debits, Credits) , but also want "super" budgets of larger time periods which include a
numberof basic budgets (sub-budgets) and for which only predictions are allowed, 
 
not actual transactions. We could make the design strict and explicit by using referential constraints (basic budget
pointingto a super budget) but If we chose to not make it strict , and conversely 
 
allow a more dynamic and liberal way that the system detects one form or the other , we could say :
each sub-budget (of the lowest level of the hierarchy - although for the time being we have only two levels) cannot
overlapwith any other sub-budget
 
each super-budget can only fully contain its sub-budgets , no partial overlap allowed.

This could be solved easily if there was a commutative containment operator like :
CREATE OR REPLACE FUNCTION range_containment(anyrange, anyrange)
  RETURNS boolean
  LANGUAGE sql
  IMMUTABLE PARALLEL SAFE STRICT
AS $function$
select $1 <@ $2 OR $1 @> $2;
$function$;

so range_containment returns true if the first operand is contained in the second or contains the second.

create operator <@@> (PROCEDURE=range_containment, LEFTARG=anyrange, RIGHTARG=anyrange, COMMUTATOR = <@@> );

But unfortunately :

alter table bdynacom.acc_budget ADD CONSTRAINT acc_budget_start_end EXCLUDE USING gist (daterange(period_start,
period_end,'[]'::text) WITH <@@>);
 
ERROR:  operator <@@>(anyrange,anyrange) is not a member of operator family "range_ops"
DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

 From a small research I did this might mean recompiling the source to make <@@> member of range_ops .

>
>>
>> I think that writing a new commutative range operator e.g. |<@@> which would return true if the left operand is
eithercontained by or contains the right operand and false otherwise would solve 
 
>> this, I am just wondering if there is a more elegant and economical way to express this. (besides writing a trigger
whichis always an option).|
 
>>
>> -- 
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 1/30/20 3:46 AM, Achilleas Mantzios wrote:
> On 29/1/20 8:32 μ.μ., Adrian Klaver wrote:
>> On 1/29/20 8:12 AM, Achilleas Mantzios wrote:
>>> Hello Dear Postgresql ppl,
>>> I have a table with date ranges and need to express the following 
>>> constraint : allow overlaps only if there is complete containment, e.g.
>>> allow values in rows like :
>>> [2020-01-01,2020-01-31)
>>> [2020-01-02,2020-01-10)
>>> [2020-01-10,2020-01-20)
>>>
>>> but disallow rows like
>>>
>>> [2020-01-02,2020-01-10)
>>> [2020-01-08,2020-01-11)
>>
>> I'm missing something. Can you provide a more complete example?
> Dear Adrian,
> I can give an example, lets say that we want to model the concept of 
> budget, and we allow basic complete budgets covering a specific time 
> period (daterange) which will have predictions and also matched actual 
> transactions (Debits, Credits) , but also want "super" budgets of larger 
> time periods which include a number of basic budgets (sub-budgets) and 
> for which only predictions are allowed, not actual transactions. We 
> could make the design strict and explicit by using referential 
> constraints (basic budget pointing to a super budget) but If we chose to 
> not make it strict , and conversely allow a more dynamic and liberal way 
> that the system detects one form or the other , we could say :
> each sub-budget (of the lowest level of the hierarchy - although for the 
> time being we have only two levels) cannot overlap with any other 
> sub-budget
> each super-budget can only fully contain its sub-budgets , no partial 
> overlap allowed.

Um, that makes my head hurt:) Questions:

1) Are the basic complete budgets and the sub-budgets and super budgets 
existing in the same table?

2) Depending on answer to 1, to prevent overlap could you not use a form 
of the example here?:

https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-CONSTRAINT

CREATE TABLE reservation (
     during tsrange,
     EXCLUDE USING GIST (during WITH &&)
);



> 
> This could be solved easily if there was a commutative containment 
> operator like :
> CREATE OR REPLACE FUNCTION range_containment(anyrange, anyrange)
>   RETURNS boolean
>   LANGUAGE sql
>   IMMUTABLE PARALLEL SAFE STRICT
> AS $function$
> select $1 <@ $2 OR $1 @> $2;
> $function$;
> 
> so range_containment returns true if the first operand is contained in 
> the second or contains the second.
> 
> create operator <@@> (PROCEDURE=range_containment, LEFTARG=anyrange, 
> RIGHTARG=anyrange, COMMUTATOR = <@@> );
> 
> But unfortunately :
> 
> alter table bdynacom.acc_budget ADD CONSTRAINT acc_budget_start_end 
> EXCLUDE USING gist (daterange(period_start, period_end, '[]'::text) WITH 
> <@@>);
> ERROR:  operator <@@>(anyrange,anyrange) is not a member of operator 
> family "range_ops"
> DETAIL:  The exclusion operator must be related to the index operator 
> class for the constraint.
> 
>  From a small research I did this might mean recompiling the source to 
> make <@@> member of range_ops .
> 
>>
>>>
>>> I think that writing a new commutative range operator e.g. |<@@> 
>>> which would return true if the left operand is either contained by or 
>>> contains the right operand and false otherwise would solve this, I am 
>>> just wondering if there is a more elegant and economical way to 
>>> express this. (besides writing a trigger which is always an option).|
>>>
>>> -- 
>>> Achilleas Mantzios
>>> IT DEV Lead
>>> IT DEPT
>>> Dynacom Tankers Mgmt
>>>
>>
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 30/1/20 6:49 μ.μ., Adrian Klaver wrote:
> On 1/30/20 3:46 AM, Achilleas Mantzios wrote:
>> On 29/1/20 8:32 μ.μ., Adrian Klaver wrote:
>>> On 1/29/20 8:12 AM, Achilleas Mantzios wrote:
>>>> Hello Dear Postgresql ppl,
>>>> I have a table with date ranges and need to express the following constraint : allow overlaps only if there is
completecontainment, e.g.
 
>>>> allow values in rows like :
>>>> [2020-01-01,2020-01-31)
>>>> [2020-01-02,2020-01-10)
>>>> [2020-01-10,2020-01-20)
>>>>
>>>> but disallow rows like
>>>>
>>>> [2020-01-02,2020-01-10)
>>>> [2020-01-08,2020-01-11)
>>>
>>> I'm missing something. Can you provide a more complete example?
>> Dear Adrian,
>> I can give an example, lets say that we want to model the concept of budget, and we allow basic complete budgets
coveringa specific time period (daterange) which will have predictions and also 
 
>> matched actual transactions (Debits, Credits) , but also want "super" budgets of larger time periods which include a
numberof basic budgets (sub-budgets) and for which only predictions are 
 
>> allowed, not actual transactions. We could make the design strict and explicit by using referential constraints
(basicbudget pointing to a super budget) but If we chose to not make it strict , and 
 
>> conversely allow a more dynamic and liberal way that the system detects one form or the other , we could say :
>> each sub-budget (of the lowest level of the hierarchy - although for the time being we have only two levels) cannot
overlapwith any other sub-budget
 
>> each super-budget can only fully contain its sub-budgets , no partial overlap allowed.
>
> Um, that makes my head hurt:) Questions:
>
> 1) Are the basic complete budgets and the sub-budgets and super budgets existing in the same table?
>
> 2) Depending on answer to 1, to prevent overlap could you not use a form of the example here?:
>
> https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-CONSTRAINT
>
> CREATE TABLE reservation (
>     during tsrange,
>     EXCLUDE USING GIST (during WITH &&)
> );
>
same table.
overlap is allowed but only when it is complete containment, which is the whole point of this thread.

>
>
>>
>> This could be solved easily if there was a commutative containment operator like :
>> CREATE OR REPLACE FUNCTION range_containment(anyrange, anyrange)
>>   RETURNS boolean
>>   LANGUAGE sql
>>   IMMUTABLE PARALLEL SAFE STRICT
>> AS $function$
>> select $1 <@ $2 OR $1 @> $2;
>> $function$;
>>
>> so range_containment returns true if the first operand is contained in the second or contains the second.
>>
>> create operator <@@> (PROCEDURE=range_containment, LEFTARG=anyrange, RIGHTARG=anyrange, COMMUTATOR = <@@> );
>>
>> But unfortunately :
>>
>> alter table bdynacom.acc_budget ADD CONSTRAINT acc_budget_start_end EXCLUDE USING gist (daterange(period_start,
period_end,'[]'::text) WITH <@@>);
 
>> ERROR:  operator <@@>(anyrange,anyrange) is not a member of operator family "range_ops"
>> DETAIL:  The exclusion operator must be related to the index operator class for the constraint.
>>
>>  From a small research I did this might mean recompiling the source to make <@@> member of range_ops .
>>
>>>
>>>>
>>>> I think that writing a new commutative range operator e.g. |<@@> which would return true if the left operand is
eithercontained by or contains the right operand and false otherwise would solve 
 
>>>> this, I am just wondering if there is a more elegant and economical way to express this. (besides writing a
triggerwhich is always an option).|
 
>>>>
>>>> -- 
>>>> Achilleas Mantzios
>>>> IT DEV Lead
>>>> IT DEPT
>>>> Dynacom Tankers Mgmt
>>>>
>>>
>>>
>>
>>
>
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 1/30/20 9:03 AM, Achilleas Mantzios wrote:

>>
>> Um, that makes my head hurt:) Questions:
>>
>> 1) Are the basic complete budgets and the sub-budgets and super 
>> budgets existing in the same table?
>>
>> 2) Depending on answer to 1, to prevent overlap could you not use a 
>> form of the example here?:
>>
>> https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-CONSTRAINT
>>
>> CREATE TABLE reservation (
>>     during tsrange,
>>     EXCLUDE USING GIST (during WITH &&)
>> );
>>
> same table.
> overlap is allowed but only when it is complete containment, which is 
> the whole point of this thread.
> 

I finally got it. Unfortunately that does not mean I have solution.


-- 
Adrian Klaver
adrian.klaver@aklaver.com