Re: Foreign keys for non-default datatypes

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> 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.

> This means getting rid of SPI usage, right?

No, there's no need for that. It means that the RI stuff would have to
take whatever steps we agree on to determine the exact comparison
operator to use, and then be sure to emit SQL that will select exactly
that operator --- this involves using the OPERATOR(foo.=) syntax to
remove schema-ambiguity and possibly adding explicit type coercions of
the operands. This'll make the RI queries noticeably uglier, but
they're not meant to be read by humans anyway. I think it wouldn't be
any slower, because OPERATOR() syntax will suppress a search-path
search that the parser would otherwise make for the operator --- but
in any case, since the plan result is cached, a few microseconds here or
there won't matter.

The hard part of this is just deciding what an RI constraint really
means semantically, in the sense of what equality operator are we going
to use to define the constraint.

regards, tom lane