Exclude constraint using custom operator

[Tobia Conforto <tobia.conforto@xxxxxxxxxx>]

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.



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general