postgres.git / summary / log / commit / refs
commit 9ca46f5bb67de41f524315e414ea57fdbd614111
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat Oct 15 00:24:32 2011 +0000
First released in: 9.2.0, 8.2.23, 8.3.17, 8.4.10, 9.0.6, 9.1.2
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 5e44c5b8e8b..d9c9825958b 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1121,20 +1121,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;
[parent: 7ddd5bd7cee5]