Re: Exclude constraint using custom operator - Mailing list pgsql-general

Stated differently, is there a simple way to extend or "subclass" the builtin range_ops operator family, adding my own
operatorto it, so that I can use it in an exclude index? 

I couldn't find a SQL definition for the range_ops family, probably because it's defined and implemented in C and
headerfiles. 

-Tobia


> On 5 Apr 2016, at 19:30, Tobia Conforto <tobia.conforto@gruppo4.eu> wrote:
>
> Hello
>
> I'd like to use the Nested Set model[1] to represent a hierarchical data structure in PostgreSQL. I'm considering
usinga single int4range column instead of the traditional two columns (lft and rgt) because the two values do in fact
representa range of integers. This should allow me to add a constraint to the range column that checks the consistency
ofthe Nested Set model. 
>
> My column is currently:
>
>     pos int4range not null check (lower(pos) between 1 and upper(pos) - 2)
>
> This is already performing the traditional check of lft >= 1 and rgt > lft, given the canonical form of an int4range.
>
> Additionally, I would like to add an exclude constraint to validate the Nested Set model: any two ranges must be
disjoint(not a.pos && b.pos) OR strictly contained one within the other, without sharing either bound. 
>
> One possible expression for the *exclusion* is that two bad ranges overlap and each is right- or left-bound by the
other.I can easily write that as a commutative operator[2]: 
>
>     create function bad_nested(int4range, int4range) returns boolean as $$
>     begin
>         return $1 && $2 and ($1 &< $2 or $1 &> $2) and ($2 &< $1 or $2 &> $1);
>     end;
>     $$ language 'plpgsql' immutable;
>
>     create operator &/ (
>         leftarg    = int4range
>     ,    rightarg   = int4range
>     ,    procedure  = bad_nested
>     ,    commutator = &/
>     );
>
> Unfortunately, trying to use this operator in an exclude constraint makes PostgreSQL complain about operator
families:
>
>     create table test (
>         pos int4range not null check (lower(pos) between 1 and upper(pos) - 2)
>     ,    exclude using gist (pos with &/)
>     );
>
>     ERROR: operator &/(int4range,int4range) is not a member of operator family "range_ops"
>     Detail: The exclusion operator must be related to the index operator class for the constraint.
>
> I don't fully understand operator classes / families and I find the manual is being somewhat cryptic about them.
>
> Can anybody suggest the right create statement(s) to enable using my operator in a GiST index?
>
> What other "strategies", functions, or operators should I write? The standard ordering of ranges with "<" should be
enough,because it maps to the traditional ordering of nested sets. 
>
> Can I do everything in sql / plpgsql or do I need to write C code?
>
>
> -Tobia
>
>
> [1] https://en.wikipedia.org/wiki/Nested_set_model
>
> [2] There may be simpler / faster expressions for the same condition. A custom plpgsql expression on the lower and
upperbounds will probably be faster, if I store the four integers as local variables. But that's just an optimization. 
>