Thread: User defined operator fails to work in EXCLUDE constraint
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?
Here is the code to reproduce. The second insert at the end should fail because the two circles
have the same radius.
CREATE OR REPLACE FUNCTION circradcmp(aa CIRCLE, bb CIRCLE)
RETURNS BOOLEAN AS $$
DECLARE zz DOUBLE PRECISION;
BEGIN
zz := abs(radius(aa) - radius(bb));
IF (zz < 0.0005)
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OPERATOR === (
LEFTARG = CIRCLE,
RIGHTARG = CIRCLE,
PROCEDURE = circradcmp,
COMMUTATOR = ===
);
ALTER OPERATOR FAMILY circle_ops USING gist ADD
OPERATOR 15 === (circle, circle);
CREATE TABLE punky
(
acirc CIRCLE,
EXCLUDE USING GIST (acirc circle_ops WITH ===)
);
INSERT INTO punky VALUES ('(0,0),3)');
INSERT INTO punky VALUES ('(7,0),3)');
Paul Jones
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
----- 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 >