On July 8, 2015 at 1:36:49 PM, Tom Lane (t...@sss.pgh.pa.us) wrote: Paul Ramsey <pram...@cleverelephant.ca> writes: >> UNION will preferentially glom onto the btree equality operator, if memory >> serves. If that isn't also the hash equality operator, things won't work >> pleasantly.
> So… what does that mean for types that have both btree and hash equality > operators? Don’t all the built-ins also have this problem? What I'm asking is why it would possibly be sensible to have different notions of equality for hash and btree. In every existing type that has both btree and hash opclasses, the equality members of those opclasses are *the same operator*. You don't really want UNION giving different answers depending on which implementation the planner happens to pick, do you? Well, that’s where things get pretty darn ugly. For reasons of practicality at the time, our equality btree operator ‘=‘ got tied to ‘bounding box equals’ way back in the mists of pre-history. (Basically the reasoning was “all our index work is about bounding boxes!”. I must admit, given my understanding of the zen of PostgreSQL now (and the features that PostgreSQL now has) that would not happen now.) So… yeah. Probably ‘=‘ should be something else, probably something quite expensive but logically defensible like a geometric equality test, but it’s not, and we have lots of third part stuff layered on top of us that expects existing semantics. If getting the objects to UNION means that the btree and hash ops have to be the same, then that just means I’m SOL and will revert to my hack of just casting the objects to bytea to force them to UNION in the recursive CTE. P.
