Re: Bug in the information_schema.referential_constraints view

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> 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.

Mmm ... can't say that I agree. The FK constraint itself is a perfectly
good constraint. It may be that ignoring such constraints is okay per
the letter of the spec, but given that we have the extension to support
FK constraints on non-constraint-associated indexes, it seems to me that
this view should cope too.

>> 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.

No, because that would entail a genuine loss of capability: FK
constraints couldn't be built using indexes that were made by CREATE
UNIQUE INDEX rather than through the unique/pk constraint syntax.
In particular this would mean that non-btree indexes could not be used.
(Yes, I know that as of today we don't have UNIQUE support in any of the
non-btree index types, but that will change. IIRC Neil Conway has
already been working on unique hashes, and I'm sure GIST will support it
eventually as well.)

>> A more robust way to handle things would be to make use of pg_depend to

> I've used pg_depend for some other views, but that entails problems as
> well, for example, because they don't track system tables.

Good point. But we don't support explicit foreign key constraints on
system tables, and probably aren't going to start anytime soon, so I
think this is probably okay.

>> 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.

Okay. I've been reviewing the rest of information_schema and have found
some other issues, but am not all the way through yet. Please hold off
the initdb force until I've reported on the other stuff.

regards, tom lane