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

Commit0134608

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 parent7c64c9f commit0134608

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
@@ -1158,20 +1158,24 @@ CREATE VIEW referential_constraints AS
11581158

11591159
FROM (pg_namespace ncon
11601160
INNER JOIN pg_constraint conONncon.oid=con.connamespace
1161-
INNER JOIN pg_class cONcon.conrelid=c.oid)
1162-
LEFT JOIN
1163-
(pg_constraint pkc
1164-
INNER JOIN pg_namespace npkcONpkc.connamespace=npkc.oid)
1165-
ONcon.confrelid=pkc.conrelid
1166-
AND _pg_keysequal(con.confkey,pkc.conkey)
1167-
1168-
WHEREc.relkind='r'
1169-
ANDcon.contype='f'
1170-
AND (pkc.contypeIN ('p','u')ORpkc.contype ISNULL)
1171-
AND (pg_has_role(c.relowner,'USAGE')
1172-
-- SELECT privilege omitted, per SQL standard
1173-
OR has_table_privilege(c.oid,'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1174-
OR has_any_column_privilege(c.oid,'INSERT, UPDATE, REFERENCES') );
1161+
INNER JOIN pg_class cONcon.conrelid=c.oidANDcon.contype='f')
1162+
LEFT JOIN pg_depend d1-- find constraint's dependency on an index
1163+
ONd1.objid=con.oidANDd1.classid='pg_constraint'::regclass
1164+
ANDd1.refclassid='pg_class'::regclassANDd1.refobjsubid=0
1165+
LEFT JOIN pg_depend d2-- find pkey/unique constraint for that index
1166+
ONd2.refclassid='pg_constraint'::regclass
1167+
ANDd2.classid='pg_class'::regclass
1168+
ANDd2.objid=d1.refobjidANDd2.objsubid=0
1169+
ANDd2.deptype='i'
1170+
LEFT JOIN pg_constraint pkcONpkc.oid=d2.refobjid
1171+
ANDpkc.contypeIN ('p','u')
1172+
ANDpkc.conrelid=con.confrelid
1173+
LEFT JOIN pg_namespace npkcONpkc.connamespace=npkc.oid
1174+
1175+
WHERE pg_has_role(c.relowner,'USAGE')
1176+
-- SELECT privilege omitted, per SQL standard
1177+
OR has_table_privilege(c.oid,'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1178+
OR has_any_column_privilege(c.oid,'INSERT, UPDATE, REFERENCES') ;
11751179

11761180
GRANTSELECTON referential_constraints TO PUBLIC;
11771181

0 commit comments

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