----- Original Message -----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Paul Jones <pbj@cmicdo.com>
> Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Sent: Sunday, April 13, 2014 4:25 PM
> Subject: Re: [GENERAL] User defined operator fails to work in EXCLUDE constraint
>
> 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
>
I was afraid it was something like this. I see that I was way in over my head
on this one and I was mislead because it didn't complain about anything.
I do appreciate the lesson.
PJ
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
