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.
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.
regards, tom lane
