From cbb5e23bfa92973bddf11935448a9d42c32f7d42 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 16 Apr 2014 14:28:59 -0400 Subject: [PATCH] Update oidjoins regression test for 9.4. Now that we're pretty much feature-frozen, it's time to update the checks on system catalog foreign-key references. (It looks like we missed doing this altogether for 9.3. Sigh.) --- src/test/regress/expected/oidjoins.out | 56 ++++++++++++++++++++++++++ src/test/regress/sql/oidjoins.sql | 28 +++++++++++++ src/tools/findoidjoins/.gitignore | 1 + src/tools/findoidjoins/README | 17 +++++--- 4 files changed, 97 insertions(+), 5 deletions(-) create mode 100644 src/tools/findoidjoins/.gitignore diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out index 6c5cb5a873..bee76ce8f2 100644 --- a/src/test/regress/expected/oidjoins.out +++ b/src/test/regress/expected/oidjoins.out @@ -25,6 +25,30 @@ WHERE aggfinalfn != 0 AND ------+------------ (0 rows) +SELECT ctid, aggmtransfn +FROM pg_catalog.pg_aggregate fk +WHERE aggmtransfn != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.aggmtransfn); + ctid | aggmtransfn +------+------------- +(0 rows) + +SELECT ctid, aggminvtransfn +FROM pg_catalog.pg_aggregate fk +WHERE aggminvtransfn != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.aggminvtransfn); + ctid | aggminvtransfn +------+---------------- +(0 rows) + +SELECT ctid, aggmfinalfn +FROM pg_catalog.pg_aggregate fk +WHERE aggmfinalfn != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.aggmfinalfn); + ctid | aggmfinalfn +------+------------- +(0 rows) + SELECT ctid, aggsortop FROM pg_catalog.pg_aggregate fk WHERE aggsortop != 0 AND @@ -41,6 +65,14 @@ WHERE aggtranstype != 0 AND ------+-------------- (0 rows) +SELECT ctid, aggmtranstype +FROM pg_catalog.pg_aggregate fk +WHERE aggmtranstype != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.aggmtranstype); + ctid | aggmtranstype +------+--------------- +(0 rows) + SELECT ctid, amkeytype FROM pg_catalog.pg_am fk WHERE amkeytype != 0 AND @@ -145,6 +177,14 @@ WHERE amvacuumcleanup != 0 AND ------+----------------- (0 rows) +SELECT ctid, amcanreturn +FROM pg_catalog.pg_am fk +WHERE amcanreturn != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amcanreturn); + ctid | amcanreturn +------+------------- +(0 rows) + SELECT ctid, amcostestimate FROM pg_catalog.pg_am fk WHERE amcostestimate != 0 AND @@ -881,6 +921,22 @@ WHERE staop3 != 0 AND ------+-------- (0 rows) +SELECT ctid, staop4 +FROM pg_catalog.pg_statistic fk +WHERE staop4 != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.staop4); + ctid | staop4 +------+-------- +(0 rows) + +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); + ctid | staop5 +------+-------- +(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 9b91683ea5..651068b964 100644 --- a/src/test/regress/sql/oidjoins.sql +++ b/src/test/regress/sql/oidjoins.sql @@ -13,6 +13,18 @@ SELECT ctid, aggfinalfn FROM pg_catalog.pg_aggregate fk WHERE aggfinalfn != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.aggfinalfn); +SELECT ctid, aggmtransfn +FROM pg_catalog.pg_aggregate fk +WHERE aggmtransfn != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.aggmtransfn); +SELECT ctid, aggminvtransfn +FROM pg_catalog.pg_aggregate fk +WHERE aggminvtransfn != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.aggminvtransfn); +SELECT ctid, aggmfinalfn +FROM pg_catalog.pg_aggregate fk +WHERE aggmfinalfn != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.aggmfinalfn); SELECT ctid, aggsortop FROM pg_catalog.pg_aggregate fk WHERE aggsortop != 0 AND @@ -21,6 +33,10 @@ SELECT ctid, aggtranstype FROM pg_catalog.pg_aggregate fk WHERE aggtranstype != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.aggtranstype); +SELECT ctid, aggmtranstype +FROM pg_catalog.pg_aggregate fk +WHERE aggmtranstype != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.aggmtranstype); SELECT ctid, amkeytype FROM pg_catalog.pg_am fk WHERE amkeytype != 0 AND @@ -73,6 +89,10 @@ SELECT ctid, amvacuumcleanup FROM pg_catalog.pg_am fk WHERE amvacuumcleanup != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amvacuumcleanup); +SELECT ctid, amcanreturn +FROM pg_catalog.pg_am fk +WHERE amcanreturn != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amcanreturn); SELECT ctid, amcostestimate FROM pg_catalog.pg_am fk WHERE amcostestimate != 0 AND @@ -441,6 +461,14 @@ SELECT ctid, staop3 FROM pg_catalog.pg_statistic fk WHERE staop3 != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.staop3); +SELECT ctid, staop4 +FROM pg_catalog.pg_statistic fk +WHERE staop4 != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.staop4); +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, spcowner FROM pg_catalog.pg_tablespace fk WHERE spcowner != 0 AND diff --git a/src/tools/findoidjoins/.gitignore b/src/tools/findoidjoins/.gitignore new file mode 100644 index 0000000000..21ec074fd9 --- /dev/null +++ b/src/tools/findoidjoins/.gitignore @@ -0,0 +1 @@ +/findoidjoins diff --git a/src/tools/findoidjoins/README b/src/tools/findoidjoins/README index e3e8a2adde..c46f9fc0f8 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.2devel as of 2011-11-14). +catalog join relationships (shown below for 9.4devel as of 2014-04-16). 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.2devel, the *only* fields that should join to more than one target +In 9.4devel, 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, and pg_shdepend.refobjid. (Running make_oidjoins_check is an easy way to spot fields joining to more @@ -33,20 +33,24 @@ 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.2devel, make_oidjoins_check produces two bogus join checks: +NOTE: as of 9.4devel, 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 regress test. +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 regress test. +neither of which should be added to the regression test. --------------------------------------------------------------------------- Join pg_catalog.pg_aggregate.aggfnoid => pg_catalog.pg_proc.oid Join pg_catalog.pg_aggregate.aggtransfn => pg_catalog.pg_proc.oid Join pg_catalog.pg_aggregate.aggfinalfn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggmtransfn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggminvtransfn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggmfinalfn => pg_catalog.pg_proc.oid Join pg_catalog.pg_aggregate.aggsortop => pg_catalog.pg_operator.oid Join pg_catalog.pg_aggregate.aggtranstype => pg_catalog.pg_type.oid +Join pg_catalog.pg_aggregate.aggmtranstype => pg_catalog.pg_type.oid Join pg_catalog.pg_am.amkeytype => pg_catalog.pg_type.oid Join pg_catalog.pg_am.aminsert => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.ambeginscan => pg_catalog.pg_proc.oid @@ -60,6 +64,7 @@ Join pg_catalog.pg_am.ambuild => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.ambuildempty => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.ambulkdelete => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.amvacuumcleanup => pg_catalog.pg_proc.oid +Join pg_catalog.pg_am.amcanreturn => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.amcostestimate => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.amoptions => pg_catalog.pg_proc.oid Join pg_catalog.pg_amop.amopfamily => pg_catalog.pg_opfamily.oid @@ -152,6 +157,8 @@ Join pg_catalog.pg_statistic.starelid => pg_catalog.pg_class.oid Join pg_catalog.pg_statistic.staop1 => pg_catalog.pg_operator.oid 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_tablespace.spcowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_trigger.tgrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_trigger.tgfoid => pg_catalog.pg_proc.oid -- 2.40.0