Re: Foreign keys for non-default datatypes

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.