Re: [BUGS] Bug in the information_schema.referential_constraints

#1

Tom Lane writes:

Quote:

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.

Quote:

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

Done.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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 (AT) gmx (DOT) net

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#2

I have fixed the problem with the keys being in different order and the
problem of missing unique constraints.

Tom Lane writes:

Quote:

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.

But that means the deficiency is elsewhere, namely that you cannot build
non-btree indexes for primary key or unique constraints.

Quote:

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

Isn't the whole unique index thing a dead end anyway? How are we ever
going to get deferrable unique constraints that way?

--
Peter Eisentraut peter_e (AT) gmx (DOT) net

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] Bug in the information_schema.referential_constraints

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] Bug in the information_schema.referential_constraints - 10-16-2003 , 02:17 PM

Re: [BUGS] Bug in the information_schema.referential_constraints - 10-16-2003 , 06:50 PM