Re: Foreign keys for non-default datatypes

On Thu, 23 Feb 2006, Tom Lane wrote:

> I looked into the problem reported here:
> http://archives.postgresql.org/pgsql-admin/2006-02/msg00261.php
> To wit, a pg_restore of a foreign key constraint involving user-defined
> types produces
>
> pg_restore: WARNING:  foreign key constraint "luuid_fkey" will require costly sequential scans
> DETAIL:  Key columns "luuid" and "luuid" are of different types: public.uniqueidentifier and
public.uniqueidentifier.
>
> A small variation of the example (I tried it with the contrib isbn type
> instead, just to see if it was uniqueidentifier's fault) produces
> complete failure:
>
> isbn=# ALTER TABLE ONLY beta ADD CONSTRAINT beta_luuid_fkey FOREIGN KEY (luuid) REFERENCES alpha(luuid);
> ERROR:  foreign key constraint "beta_luuid_fkey" cannot be implemented
> DETAIL:  Key columns "luuid" and "luuid" are of incompatible types: public.isbn and public.isbn.
>
> The problem is that pg_dump likes to set a restrictive search path:
> the above follows
> isbn# SET search_path = delta, pg_catalog;
> while the data type and its operators all are defined in the public
> schema.  So when ATAddForeignKeyConstraint checks to see if there's a
> suitable "=" operator, it doesn't find the intended operator.  In the
> isbn case it doesn't find anything at all; in the uniqueidentifier case
> there's an implicit cast to text and so the texteq operator is found,
> but it's not a member of the index's opclass and thus the warning
> appears.
>
> Even if ATAddForeignKeyConstraint weren't trying to be helpful by
> checking the operator, we'd be facing the exact same risks at runtime
> --- the RI triggers blithely assume that "foo = bar" will do the right
> thing.
>
> 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.
>
> This ties into Stephan's nearby concerns about whether unique indexes
> using nondefault opclasses make sense as infrastructure for foreign
> key checks.  The answer of course is that they make sense if and only
> if the "=" operator used for the RI check is a member of the index
> opclass.

Right, when I'd said "or we allow you to specify a different equality
operator for that case which matches the one in the opclass" I sort of was
thinking that direction, although I was considering either matching the
index one or allowing you to specify the operator if we went that way.
Those still bug me a little (especially matching the index one) because
writing out the constraint's check by hand by looking at the spec would
give different results.

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