Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9ca46f5

Browse files
committed
Fix bugs in information_schema.referential_constraints view.
This view was being insufficiently careful about matching the FK constraintto the depended-on primary or unique key constraint. That could result infailure to show an FK constraint at all, or showing it multiple times, orclaiming that it depended on a different constraint than the one it reallydoes. Fix by joining via pg_depend to ensure that we find only the correctdependency.Back-patch, but don't bump catversion because we can't force initdb in backbranches. The next minor-version release notes should explain that if youneed to fix this in an existing installation, you can drop theinformation_schema schema then re-create it by sourcing$SHAREDIR/information_schema.sql in each database (as a superuser ofcourse).
1 parent7ddd5bd commit9ca46f5

File tree

1 file changed

+18
-14
lines changed

1 file changed

+18
-14
lines changed

‎src/backend/catalog/information_schema.sql

Lines changed: 18 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1121,20 +1121,24 @@ CREATE VIEW referential_constraints AS
11211121

11221122
FROM (pg_namespace ncon
11231123
INNER JOIN pg_constraint conONncon.oid=con.connamespace
1124-
INNER JOIN pg_class cONcon.conrelid=c.oid)
1125-
LEFT JOIN
1126-
(pg_constraint pkc
1127-
INNER JOIN pg_namespace npkcONpkc.connamespace=npkc.oid)
1128-
ONcon.confrelid=pkc.conrelid
1129-
AND _pg_keysequal(con.confkey,pkc.conkey)
1130-
1131-
WHEREc.relkind='r'
1132-
ANDcon.contype='f'
1133-
AND (pkc.contypeIN ('p','u')ORpkc.contype ISNULL)
1134-
AND (pg_has_role(c.relowner,'USAGE')
1135-
-- SELECT privilege omitted, per SQL standard
1136-
OR has_table_privilege(c.oid,'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1137-
OR has_any_column_privilege(c.oid,'INSERT, UPDATE, REFERENCES') );
1124+
INNER JOIN pg_class cONcon.conrelid=c.oidANDcon.contype='f')
1125+
LEFT JOIN pg_depend d1-- find constraint's dependency on an index
1126+
ONd1.objid=con.oidANDd1.classid='pg_constraint'::regclass
1127+
ANDd1.refclassid='pg_class'::regclassANDd1.refobjsubid=0
1128+
LEFT JOIN pg_depend d2-- find pkey/unique constraint for that index
1129+
ONd2.refclassid='pg_constraint'::regclass
1130+
ANDd2.classid='pg_class'::regclass
1131+
ANDd2.objid=d1.refobjidANDd2.objsubid=0
1132+
ANDd2.deptype='i'
1133+
LEFT JOIN pg_constraint pkcONpkc.oid=d2.refobjid
1134+
ANDpkc.contypeIN ('p','u')
1135+
ANDpkc.conrelid=con.confrelid
1136+
LEFT JOIN pg_namespace npkcONpkc.connamespace=npkc.oid
1137+
1138+
WHERE pg_has_role(c.relowner,'USAGE')
1139+
-- SELECT privilege omitted, per SQL standard
1140+
OR has_table_privilege(c.oid,'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1141+
OR has_any_column_privilege(c.oid,'INSERT, UPDATE, REFERENCES') ;
11381142

11391143
GRANTSELECTON referential_constraints TO PUBLIC;
11401144

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp