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

Commit9a743ff

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 parent7a0c584 commit9a743ff

File tree

1 file changed

+15
-11
lines changed

1 file changed

+15
-11
lines changed

‎src/backend/catalog/information_schema.sql

Lines changed: 15 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1099,17 +1099,21 @@ CREATE VIEW referential_constraints AS
10991099

11001100
FROM (pg_namespace ncon
11011101
INNER JOIN pg_constraint conONncon.oid=con.connamespace
1102-
INNER JOIN pg_class cONcon.conrelid=c.oid)
1103-
LEFT JOIN
1104-
(pg_constraint pkc
1105-
INNER JOIN pg_namespace npkcONpkc.connamespace=npkc.oid)
1106-
ONcon.confrelid=pkc.conrelid
1107-
AND _pg_keysequal(con.confkey,pkc.conkey)
1108-
1109-
WHEREc.relkind='r'
1110-
ANDcon.contype='f'
1111-
AND (pkc.contypeIN ('p','u')ORpkc.contype ISNULL)
1112-
AND pg_has_role(c.relowner,'USAGE');
1102+
INNER JOIN pg_class cONcon.conrelid=c.oidANDcon.contype='f')
1103+
LEFT JOIN pg_depend d1-- find constraint's dependency on an index
1104+
ONd1.objid=con.oidANDd1.classid='pg_constraint'::regclass
1105+
ANDd1.refclassid='pg_class'::regclassANDd1.refobjsubid=0
1106+
LEFT JOIN pg_depend d2-- find pkey/unique constraint for that index
1107+
ONd2.refclassid='pg_constraint'::regclass
1108+
ANDd2.classid='pg_class'::regclass
1109+
ANDd2.objid=d1.refobjidANDd2.objsubid=0
1110+
ANDd2.deptype='i'
1111+
LEFT JOIN pg_constraint pkcONpkc.oid=d2.refobjid
1112+
ANDpkc.contypeIN ('p','u')
1113+
ANDpkc.conrelid=con.confrelid
1114+
LEFT JOIN pg_namespace npkcONpkc.connamespace=npkc.oid
1115+
1116+
WHERE pg_has_role(c.relowner,'USAGE');
11131117

11141118
GRANTSELECTON referential_constraints TO PUBLIC;
11151119

0 commit comments

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