Paul Jones <pbj@cmicdo.com> writes:
> I tried to define my own circle operator to use in an EXCLUDE constraint but it fails to detect
> insertion of rows that should not be simultaneously be allowed in the table.� The operator
> compares two circles' radii and works for a simple SELECT.� What am I doing wrong?
This:
> ALTER OPERATOR FAMILY circle_ops USING gist ADD
> ������� OPERATOR 15 === (circle, circle);
You can't just add a new operator to a GIST opclass and have it work with
no coding, because what makes it work is teaching the opclass'
consistent() function about it.
What I'd have expected to happen when you did this was bleating about
an unrecognized operator strategy number. The reason you didn't get that
was that rtree_internal_consistent doesn't throw an error in the default:
case in its switch, which seems pretty stupid now that I look at it.
In this particular application, circle_ops couldn't really help you even
if you were prepared to go and change the C code, because what it stores
in the index is bounding boxes for the circles. I can't see any way for
bounding-box comparisons to exclude subtrees of the index when the query
is about whether the radii match; so you'd not be able to do better than
a full index scan, which will not be faster than a full table scan.
You could probably solve your problem with a different index
representation. A brute-force way would be to make an expression index
on the range [radius(aa), radius(aa) + 0.005] and then look for
overlaps of those ranges. There might be a better answer.
regards, tom lane