Re: User defined operator fails to work in EXCLUDE constraint

----- Original Message -----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Paul Jones <pbj(at)cmicdo(dot)com>
> Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
> Sent: Sunday, April 13, 2014 4:25 PM
> Subject: Re: [GENERAL] User defined operator fails to work in EXCLUDE constraint
>
> Paul Jones <pbj(at)cmicdo(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>