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

Commitd26e1eb

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 parente6858e6 commitd26e1eb

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

11771177
FROM (pg_namespace ncon
11781178
INNER JOIN pg_constraint conONncon.oid=con.connamespace
1179-
INNER JOIN pg_class cONcon.conrelid=c.oid)
1180-
LEFT JOIN
1181-
(pg_constraint pkc
1182-
INNER JOIN pg_namespace npkcONpkc.connamespace=npkc.oid)
1183-
ONcon.confrelid=pkc.conrelid
1184-
AND _pg_keysequal(con.confkey,pkc.conkey)
1185-
1186-
WHEREc.relkind='r'
1187-
ANDcon.contype='f'
1188-
AND (pkc.contypeIN ('p','u')ORpkc.contype ISNULL)
1189-
AND (pg_has_role(c.relowner,'USAGE')
1190-
-- SELECT privilege omitted, per SQL standard
1191-
OR has_table_privilege(c.oid,'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1192-
OR has_any_column_privilege(c.oid,'INSERT, UPDATE, REFERENCES') );
1179+
INNER JOIN pg_class cONcon.conrelid=c.oidANDcon.contype='f')
1180+
LEFT JOIN pg_depend d1-- find constraint's dependency on an index
1181+
ONd1.objid=con.oidANDd1.classid='pg_constraint'::regclass
1182+
ANDd1.refclassid='pg_class'::regclassANDd1.refobjsubid=0
1183+
LEFT JOIN pg_depend d2-- find pkey/unique constraint for that index
1184+
ONd2.refclassid='pg_constraint'::regclass
1185+
ANDd2.classid='pg_class'::regclass
1186+
ANDd2.objid=d1.refobjidANDd2.objsubid=0
1187+
ANDd2.deptype='i'
1188+
LEFT JOIN pg_constraint pkcONpkc.oid=d2.refobjid
1189+
ANDpkc.contypeIN ('p','u')
1190+
ANDpkc.conrelid=con.confrelid
1191+
LEFT JOIN pg_namespace npkcONpkc.connamespace=npkc.oid
1192+
1193+
WHERE pg_has_role(c.relowner,'USAGE')
1194+
-- SELECT privilege omitted, per SQL standard
1195+
OR has_table_privilege(c.oid,'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1196+
OR has_any_column_privilege(c.oid,'INSERT, UPDATE, REFERENCES') ;
11931197

11941198
GRANTSELECTON referential_constraints TO PUBLIC;
11951199

0 commit comments

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