Exclude constraint using custom operator

From: Tobia Conforto <tobia(dot)conforto(at)gruppo4(dot)eu>
To: pgsql-general(at)postgresql(dot)org
Subject: Exclude constraint using custom operator
Date: 2016-04-05 17:30:25
Message-ID: 4DB1D013-B270-4CC7-9AC9-5A94CEE1429A@gruppo4.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello

I'd like to use the Nested Set model[1] to represent a hierarchical data structure in PostgreSQL. I'm considering using a single int4range column instead of the traditional two columns (lft and rgt) because the two values do in fact represent a range of integers. This should allow me to add a constraint to the range column that checks the consistency of the 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 upper bounds will probably be faster, if I store the four integers as local variables. But that's just an optimization.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-04-05 17:37:49 Re: What does Rows Counted mean
Previous Message Rakesh Kumar 2016-04-05 17:22:04 Re: What does Rows Counted mean