From e3f67a5a1732321dfa094e14c083dc482ad066b4 Mon Sep 17 00:00:00 2001 From: Tom Lane <tgl@sss.pgh.pa.us> Date: Mon, 15 May 2017 14:04:03 -0400 Subject: [PATCH] Update oidjoins regression test for v10. --- src/test/regress/expected/oidjoins.out | 72 ++++++++++++++++++++++++++ src/test/regress/sql/oidjoins.sql | 36 +++++++++++++ src/tools/findoidjoins/README | 31 +++++++++-- 3 files changed, 136 insertions(+), 3 deletions(-) diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out index 5fe630777f..234b44fdf2 100644 --- a/src/test/regress/expected/oidjoins.out +++ b/src/test/regress/expected/oidjoins.out @@ -217,6 +217,30 @@ WHERE attcollation != 0 AND ------+-------------- (0 rows) +SELECT ctid, roleid +FROM pg_catalog.pg_auth_members fk +WHERE roleid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.roleid); + ctid | roleid +------+-------- +(0 rows) + +SELECT ctid, member +FROM pg_catalog.pg_auth_members fk +WHERE member != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.member); + ctid | member +------+-------- +(0 rows) + +SELECT ctid, grantor +FROM pg_catalog.pg_auth_members fk +WHERE grantor != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.grantor); + ctid | grantor +------+--------- +(0 rows) + SELECT ctid, castsource FROM pg_catalog.pg_cast fk WHERE castsource != 0 AND @@ -721,6 +745,14 @@ WHERE opfowner != 0 AND ------+---------- (0 rows) +SELECT ctid, partrelid +FROM pg_catalog.pg_partitioned_table fk +WHERE partrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.partrelid); + ctid | partrelid +------+----------- +(0 rows) + SELECT ctid, polrelid FROM pg_catalog.pg_policy fk WHERE polrelid != 0 AND @@ -833,6 +865,22 @@ WHERE ev_class != 0 AND ------+---------- (0 rows) +SELECT ctid, seqrelid +FROM pg_catalog.pg_sequence fk +WHERE seqrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.seqrelid); + ctid | seqrelid +------+---------- +(0 rows) + +SELECT ctid, seqtypid +FROM pg_catalog.pg_sequence fk +WHERE seqtypid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.seqtypid); + ctid | seqtypid +------+---------- +(0 rows) + SELECT ctid, refclassid FROM pg_catalog.pg_shdepend fk WHERE refclassid != 0 AND @@ -897,6 +945,30 @@ WHERE staop5 != 0 AND ------+-------- (0 rows) +SELECT ctid, stxrelid +FROM pg_catalog.pg_statistic_ext fk +WHERE stxrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.stxrelid); + ctid | stxrelid +------+---------- +(0 rows) + +SELECT ctid, stxnamespace +FROM pg_catalog.pg_statistic_ext fk +WHERE stxnamespace != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_namespace pk WHERE pk.oid = fk.stxnamespace); + ctid | stxnamespace +------+-------------- +(0 rows) + +SELECT ctid, stxowner +FROM pg_catalog.pg_statistic_ext fk +WHERE stxowner != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.stxowner); + ctid | stxowner +------+---------- +(0 rows) + SELECT ctid, spcowner FROM pg_catalog.pg_tablespace fk WHERE spcowner != 0 AND diff --git a/src/test/regress/sql/oidjoins.sql b/src/test/regress/sql/oidjoins.sql index ef749936eb..fcf9990f6b 100644 --- a/src/test/regress/sql/oidjoins.sql +++ b/src/test/regress/sql/oidjoins.sql @@ -109,6 +109,18 @@ SELECT ctid, attcollation FROM pg_catalog.pg_attribute fk WHERE attcollation != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_collation pk WHERE pk.oid = fk.attcollation); +SELECT ctid, roleid +FROM pg_catalog.pg_auth_members fk +WHERE roleid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.roleid); +SELECT ctid, member +FROM pg_catalog.pg_auth_members fk +WHERE member != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.member); +SELECT ctid, grantor +FROM pg_catalog.pg_auth_members fk +WHERE grantor != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.grantor); SELECT ctid, castsource FROM pg_catalog.pg_cast fk WHERE castsource != 0 AND @@ -361,6 +373,10 @@ SELECT ctid, opfowner FROM pg_catalog.pg_opfamily fk WHERE opfowner != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.opfowner); +SELECT ctid, partrelid +FROM pg_catalog.pg_partitioned_table fk +WHERE partrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.partrelid); SELECT ctid, polrelid FROM pg_catalog.pg_policy fk WHERE polrelid != 0 AND @@ -417,6 +433,14 @@ SELECT ctid, ev_class FROM pg_catalog.pg_rewrite fk WHERE ev_class != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.ev_class); +SELECT ctid, seqrelid +FROM pg_catalog.pg_sequence fk +WHERE seqrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.seqrelid); +SELECT ctid, seqtypid +FROM pg_catalog.pg_sequence fk +WHERE seqtypid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.seqtypid); SELECT ctid, refclassid FROM pg_catalog.pg_shdepend fk WHERE refclassid != 0 AND @@ -449,6 +473,18 @@ SELECT ctid, staop5 FROM pg_catalog.pg_statistic fk WHERE staop5 != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.staop5); +SELECT ctid, stxrelid +FROM pg_catalog.pg_statistic_ext fk +WHERE stxrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.stxrelid); +SELECT ctid, stxnamespace +FROM pg_catalog.pg_statistic_ext fk +WHERE stxnamespace != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_namespace pk WHERE pk.oid = fk.stxnamespace); +SELECT ctid, stxowner +FROM pg_catalog.pg_statistic_ext fk +WHERE stxowner != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.stxowner); SELECT ctid, spcowner FROM pg_catalog.pg_tablespace fk WHERE spcowner != 0 AND diff --git a/src/tools/findoidjoins/README b/src/tools/findoidjoins/README index af8c99eb6a..7c5a5cf4b9 100644 --- a/src/tools/findoidjoins/README +++ b/src/tools/findoidjoins/README @@ -5,7 +5,7 @@ findoidjoins This program scans a database and prints oid fields (also reg* fields) and the tables they join to. It is normally used to check the system -catalog join relationships (shown below for 9.6devel as of 2016-01-16). +catalog join relationships (shown below for 10devel as of 2017-05-15). Historically this has been run against an empty database such as template1, but there's a problem with that approach: some of the catalogs are empty @@ -16,7 +16,7 @@ catalogs in interesting ways. Note that unexpected matches may indicate bogus entries in system tables; don't accept a peculiar match without question. In particular, a field shown as joining to more than one target table is probably messed up. -In 9.6, the *only* fields that should join to more than one target +In v10, the *only* fields that should join to more than one target table are: pg_description.objoid, pg_depend.objid, pg_depend.refobjid, pg_shdescription.objoid, pg_shdepend.objid, pg_shdepend.refobjid, @@ -35,13 +35,29 @@ regression test. The oidjoins test should be updated after any revision in the patterns of cross-links between system tables. (Typically we update it at the end of each development cycle.) -NOTE: as of 9.6, make_oidjoins_check produces two bogus join checks: +NOTE: as of v10, make_oidjoins_check produces two bogus join checks: Join pg_catalog.pg_class.relfilenode => pg_catalog.pg_class.oid Join pg_catalog.pg_database.datlastsysoid => pg_catalog.pg_database.oid These are artifacts and should not be added to the oidjoins regression test. You might also get output for pg_shdepend.refobjid and pg_shdescription.objoid, neither of which should be added to the regression test. +In short, the procedure is: + +1. make installcheck in src/test/regress +2. cd here, make +3. ./findoidjoins regression >foj.out +4. ./make_oidjoins_check foj.out >oidjoins.sql +5. paste foj.out below, removing the entries reported as duplicative + by make_oidjoins_check or described as bogus above +6. remove bogus tests in oidjoins.sql as per above +7. copy oidjoins.sql to src/test/regress/sql/, + and update oidjoins.out to match. +8. Review diffs to ensure they correspond to new catalog relationships, + then commit. (Sometimes, a pre-existing catalog relationship might + become newly visible here as a result of the regression tests populating + a catalog they didn't before. That's OK too.) + --------------------------------------------------------------------------- Join pg_catalog.pg_aggregate.aggfnoid => pg_catalog.pg_proc.oid @@ -71,6 +87,9 @@ Join pg_catalog.pg_attrdef.adrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_attribute.attrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_attribute.atttypid => pg_catalog.pg_type.oid Join pg_catalog.pg_attribute.attcollation => pg_catalog.pg_collation.oid +Join pg_catalog.pg_auth_members.roleid => pg_catalog.pg_authid.oid +Join pg_catalog.pg_auth_members.member => pg_catalog.pg_authid.oid +Join pg_catalog.pg_auth_members.grantor => pg_catalog.pg_authid.oid Join pg_catalog.pg_cast.castsource => pg_catalog.pg_type.oid Join pg_catalog.pg_cast.casttarget => pg_catalog.pg_type.oid Join pg_catalog.pg_cast.castfunc => pg_catalog.pg_proc.oid @@ -134,6 +153,7 @@ Join pg_catalog.pg_operator.oprjoin => pg_catalog.pg_proc.oid Join pg_catalog.pg_opfamily.opfmethod => pg_catalog.pg_am.oid Join pg_catalog.pg_opfamily.opfnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_opfamily.opfowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_partitioned_table.partrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_policy.polrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_proc.pronamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_proc.proowner => pg_catalog.pg_authid.oid @@ -148,6 +168,8 @@ Join pg_catalog.pg_range.rngsubopc => pg_catalog.pg_opclass.oid Join pg_catalog.pg_range.rngcanonical => pg_catalog.pg_proc.oid Join pg_catalog.pg_range.rngsubdiff => pg_catalog.pg_proc.oid Join pg_catalog.pg_rewrite.ev_class => pg_catalog.pg_class.oid +Join pg_catalog.pg_sequence.seqrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_sequence.seqtypid => pg_catalog.pg_type.oid Join pg_catalog.pg_shdepend.refclassid => pg_catalog.pg_class.oid Join pg_catalog.pg_shdescription.classoid => pg_catalog.pg_class.oid Join pg_catalog.pg_statistic.starelid => pg_catalog.pg_class.oid @@ -156,6 +178,9 @@ Join pg_catalog.pg_statistic.staop2 => pg_catalog.pg_operator.oid Join pg_catalog.pg_statistic.staop3 => pg_catalog.pg_operator.oid Join pg_catalog.pg_statistic.staop4 => pg_catalog.pg_operator.oid Join pg_catalog.pg_statistic.staop5 => pg_catalog.pg_operator.oid +Join pg_catalog.pg_statistic_ext.stxrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_statistic_ext.stxnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_statistic_ext.stxowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_tablespace.spcowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_transform.trftype => pg_catalog.pg_type.oid Join pg_catalog.pg_transform.trflang => pg_catalog.pg_language.oid -- 2.50.1