From 0134608f60d5a7efa5a6a3b7306d75c6ac9add97 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 14 Oct 2011 20:24:22 -0400 Subject: [PATCH] Fix bugs in information_schema.referential_constraints view. This view was being insufficiently careful about matching the FK constraint to the depended-on primary or unique key constraint. That could result in failure to show an FK constraint at all, or showing it multiple times, or claiming that it depended on a different constraint than the one it really does. Fix by joining via pg_depend to ensure that we find only the correct dependency. Back-patch, but don't bump catversion because we can't force initdb in back branches. The next minor-version release notes should explain that if you need to fix this in an existing installation, you can drop the information_schema schema then re-create it by sourcing $SHAREDIR/information_schema.sql in each database (as a superuser of course). --- src/backend/catalog/information_schema.sql | 32 ++++++++++++---------- 1 file changed, 18 insertions(+), 14 deletions(-) diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 452a0ead44..342b704054 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1158,20 +1158,24 @@ CREATE VIEW referential_constraints AS FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace - INNER JOIN pg_class c ON con.conrelid = c.oid) - LEFT JOIN - (pg_constraint pkc - INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid) - ON con.confrelid = pkc.conrelid - AND _pg_keysequal(con.confkey, pkc.conkey) - - WHERE c.relkind = 'r' - AND con.contype = 'f' - AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL) - AND (pg_has_role(c.relowner, 'USAGE') - -- SELECT privilege omitted, per SQL standard - OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') - OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ); + INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f') + LEFT JOIN pg_depend d1 -- find constraint's dependency on an index + ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass + AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0 + LEFT JOIN pg_depend d2 -- find pkey/unique constraint for that index + ON d2.refclassid = 'pg_constraint'::regclass + AND d2.classid = 'pg_class'::regclass + AND d2.objid = d1.refobjid AND d2.objsubid = 0 + AND d2.deptype = 'i' + LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid + AND pkc.contype IN ('p', 'u') + AND pkc.conrelid = con.confrelid + LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid + + WHERE pg_has_role(c.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ; GRANT SELECT ON referential_constraints TO PUBLIC; -- 2.40.0