Re: Foreign keys for non-default datatypes

On Thu, 2 Mar 2006, Tom Lane wrote:

> [ returning to a week-old thread... ]
>
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > On Thu, 23 Feb 2006, Tom Lane wrote:
> >> 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.
>
> > ISTM that the btree opclass is too restrictive right now since I'm
> > guessing we'd want to allow say int4 <-> numeric which I don't think is in
> > either btree opclass, but I don't know if they're not there because it
> > wasn't worth putting in or if there's a more fundamental reason.
>
> I thought a bit more about this and realized that there's really a
> fairly natural way to associate an FK constraint with a btree index
> opclass.  To wit, we already identify a unique index that the FK
> constraint depends on to enforce uniqueness of the PK column --- and in
> the current system, only btree indexes can be unique.  So we can just
> use the opclass(es) of that index.  (If we ever add uniqueness support
> to GiST, it would not be unreasonable to expect that the opclasses that
> support uniqueness identify exactly which operator they think defines
> equality, so we could still make it work for that.)
>
> To handle the cross-type cases, I propose that we make two checks:
>
> 1. If the index opclass contains an exact operator for the case
> "PKtype = FKtype", use that operator.

Is this rule to read explicitly naming '=' or just the item in that
position in the opclass?

> 2. Otherwise, if there's an implicit coercion from the FK column
> type to the PK column type, apply that coercion and use the opclass's
> native equality operator.
>
> If neither of these work, I think it'd be OK to fail (ie, reject
> creation of the FK constraint).  This would have the interesting
> property that the current warning condition "FK constraint will require
> costly sequential scans" would become an error case.  I don't really
> have a problem with that --- if there are any practically-useful cases
> where people still get that warning, it means there are gaps we ought to
> fill in the btree cross-type indexing support, not that we ought to go
> out of our way to continue supporting a very inefficient mode of FK
> operation.  In any case, the current code is willing to try to enforce
> FKs that use an "equality" operator that we have no hard evidence works
> like equality at all, and that surely isn't a good idea.

I think it's an acceptable idea to fail if we're going to extend the
cross-type indexing support, but AFAICS we have to at the very least allow
all of the "standard" numeric types in all combinations to work to meet
the spec, and I don't think the above rules and current opclasses will
give that to us (and I don't honestly understand some of the bits of this
to know if there's a problem with extending the opclasses to allow that).