Re: Bug in the information_schema.referential_constraints

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