Foreign keys for non-default datatypes, redux - Mailing list pgsql-hackers

Almost a year ago, we talked about the problem that referential
integrity should be selecting comparison operators on the basis
of b-tree index opclasses, instead of assuming that the appropriate
operator is always named "=":
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php
http://archives.postgresql.org/pgsql-hackers/2006-03/msg00161.php

I'm about to go off and implement that at last.  To refresh folks'
memory, what I think we agreed to was that at the time of definition
of a foreign-key constraint, we should identify the specific equality
operator to be used for (each column of) the constraint.  The method
for doing this is to be:

* First, identify the unique index that is relied on to enforce
uniqueness of the PK entries (we do this already of course).

* Look to see if there is an equality operator in this index's
opfamily accepting exactly the PK and FK data types (ie, "PK = FK").
If so, use that.

* Else, check to see if there is an implicit promotion from the FK
datatype to the PK datatype.  If so, use the equality operator
"PK = PK", which must exist since the opfamily supports an index
on the PK datatype.

* Else fail (this means that the present warning about "inefficient"
foreign keys will become a hard error).

The good thing about this proposal is that we know that we have
identified an operator whose notion of equality is compatible with
the notion of equality being enforced by the unique index, and thus
a lot of potential gotchas with nondefault opclasses go away.

My intention is that we'd record pg_depend entries making the RI
constraint dependent on not only the index, but the specific operators
to use.  This would not have been too critical a year ago given that
opclasses were effectively immutable; but in the current opfamily design
it's entirely likely that we'd select cross-type equality operators that
are considered "loose" and potentially droppable from the opfamily.
So we need dependencies to prevent the operators from disappearing out
from under us.  (Come to think of it, we might want to record
dependencies on the casts too, if we're using implicit casts?)

What I'm thinking about right now is that the ri_triggers.c routines
need to be able to find out which operators they're supposed to use,
so that they can construct the RI queries correctly.  We could possibly
have them dredge the information out of pg_depend, but this seems
inefficient, and I'm not entirely sure how one would match up operators
with columns given only the pg_depend entries.  What I'd like to propose
instead is:

* Add an oid[] column to pg_constraint that stores the equality operator
OIDs for a foreign-key constraint, in the same column order as conkey[]
and confkey[].

* Add an OID column to pg_trigger giving the OID of the constraint
owning the trigger (or 0 if none).  Add this information to struct
Trigger as well, so that it gets passed to trigger functions.

Given the pg_constraint OID, the RI triggers could fetch the constraint
row and look at conkey[], confkey[], and the new operator oid[] array
to determine what they need to know.

This would actually mean that they don't need pg_trigger.tgargs at all.
I am pretty strongly tempted to stop storing anything in tgargs for RI
triggers --- it's ugly, and updating the info during RENAME commands
is a pain in the rear.  On the other hand removing it might break
client-side code that expects to look at tgargs to learn about FK
constraints.  I'd personally think that pg_constraint is a lot easier to
work with, but there might be some code out there left over from way
back before pg_constraint existed --- anyone know of any such issue?
        regards, tom lane