On Thu, Feb 23, 2006 at 01:10:07PM -0500, Tom Lane wrote: > I looked into the problem reported here: > http://archives.postgresql.org/pgsql-admin/2006-02/msg00261.php
<snip> > This has been a hazard in the RI code since day one, of course, but > I think it's time to face up to it and do something about it. The > RI code ought not be assuming that "=" will find an appropriate > operator --- it should be doing something based on semantics, not a > pure name search, and definitely not a search-path-dependent search. <snip> > Any thoughts about details? My feeling is that we should tie RI > semantics to btree opclasses, same as we have done for ORDER BY > and some other SQL constructs, but I don't have a concrete proposal > right offhand. The btree idea may not cover cross-type FKs anyway. Well, one solution is to simply use the '=' operator of the default btree operator class. In all the cases we're talking about here they have a btree operator class so this isn't an issue and (I think) gives the behaviour people expect. However, really they require less because we only care about equality, not order, so a btree operator class is overkill. The case I'm thinking of is "complex" which has equality but no order. Although people are unlikely to do foreign keys on them, there are probably other types where people might consider it. Your point about cross-type foreign keys is good, but I think it could be dealt with by explanding our use of cross-type operator classes, like we've done for int2/4/8. For example, I was surprised the other day by the fact that we didn't have cross-type operator classes for text/varchar/char. If you want to do cross-type foreign keys, would it be unreasonable to require a cross-type operator class to match? This could all tie in with my collate stuff by requiring people to declare the semantics of the type (equality, order, hash, etc) and building the operator classes and such automatically from that. Some types really only have equality but no order so you could have foreign keys but couldn't create a btree index. But you could create a hash index if a hash function was defined. The COLLATE clause gives people a simple way to supplying the semantics they want from a given type, which may deal with Stephan's concerns. The cross-type stuff for collations is an issue, although I was thinking about tackling it with cross-type equality, order and hash functions, which pretty much amounts to what we need for cross-type operator classes now. But I think people are getting tired of me going on about collate and I should just go implement it :) Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
signature.asc
Description: Digital signature
