----- Original Message ----- > From: Tom Lane <tgl@xxxxxxxxxxxxx> > To: Paul Jones <pbj@xxxxxxxxxx> > Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx> > Sent: Sunday, April 13, 2014 4:25 PM > Subject: Re: User defined operator fails to work in EXCLUDE constraint > > 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 > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
