Tom Lane writes:
> > AND con.contype = 'f'
> > AND con.confrelid = pkc.conrelid
>
> I think this is correct as far as it goes, but there are more problems.
Added.
> For one, I believe we also need to check the contype of the pkc row;
> otherwise matches against check constraints are possible.
Done.
> Another problem is that the view will fail to list FK constraints at all
> if it cannot identify a matching unique constraint.
If you want information under those conditions, you're looking at the
wrong view. table_constraints gives you general information about
constraints.
> Which there may not be (the backend code for creating an FK checks for a
> matching unique index, quite a different animal).
I think that should be changed.
> 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.
OK, that is indeed a problem. I'll see if I can up with a solution.
> 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.
I've used pg_depend for some other views, but that entails problems as
well, for example, because they don't track system tables. It might be
worth a shot in this particular case, though.
> 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.
I think we will have to.
--
Peter Eisentraut peter_e@gmx.net