Re: Bug in the information_schema.referential_constraints view

malerba(at)gnome-db(dot)org writes:
> The information_schema.referential_constraints retuns wrong data because
> there is an incomplete joining condition in the WHERE clause.
> [ these two conditions need to be added: ]
> AND con.contype = 'f'
> AND con.confrelid = pkc.conrelid

I think this is correct as far as it goes, but there are more problems.

For one, I believe we also need to check the contype of the pkc row;
otherwise matches against check constraints are possible.

Another problem is that the view will fail to list FK constraints at all
if it cannot identify a matching unique constraint. Which there may not
be (the backend code for creating an FK checks for a matching unique
index, quite a different animal). And the check for match is inadequate
anyway, because it is using "con.confkey = pkc.conkey", which only
matches if the unique constraint lists the same columns *in the same
order* as the FK constraint does. The backend code does not require
that.

A more robust way to handle things would be to make use of pg_depend to
find the index the FK constraint depends on and then chain to the unique
constraint associated with that index. However, we need to decide what
to do if there is no such unique constraint. I don't think "omit the FK
constraint from the view" is the right answer. We could return nulls
for the unique_constraint_schema and unique_constraint_name, or we could
return the name of the index itself (not standard, but then the
underlying situation isn't standard either).

Comments?

Another question is whether to force an initdb after making this change.
If we don't, existing beta testers may continue to use the incorrect
view definition.

regards, tom lane