Paul Jones <pbj@xxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
