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

On Fri, 9 Feb 2007, Tom Lane wrote:

> 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).

I assume you're speaking of the version where we just change the
constraints to use statements with the OPERATOR() syntax and potential
casts rather than the discussion at the end about changing the pk checks
to avoid planning entirely?

> 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?)

I think we probably should, so the above seems reasonable to me.

> * 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?

I'd say we probably want to keep the tgargs info for at least a version or
two after changing the implementation.  Getting rid of using the args info
sounds like a good idea.  One side question is what should we do about the
places in the current system where it checks for the key sets being empty?
AFAIK, we still don't actually support letting you define a constraint
that way, and I haven't heard any complaints about that, and I'm not even
sure if that actually made it into the spec proper.