Re: Bug in the information_schema.referential_constraints - Mailing list pgsql-bugs

From Peter Eisentraut
Subject Re: Bug in the information_schema.referential_constraints
Date
Msg-id Pine.LNX.4.44.0310162054490.21950-100000@peter.localdomain
Whole thread Raw
In response to Re: Bug in the information_schema.referential_constraints view  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Bug in the information_schema.referential_constraints view
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re:
Next
From: Tom Lane
Date:
Subject: Re: Bug in the information_schema.referential_constraints view