src/tools/findoidjoins/README 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.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 and so their joining columns won't show up in the output. Current practice is to run it against the regression-test database, which populates the 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.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 than one table, BTW.) The shell script make_oidjoins_check converts findoidjoins' output into an SQL script that checks for dangling links (entries in an OID or REG* column that don't match any row in the expected table). Note that fields joining to more than one table are NOT processed, just reported as linking to more than one table. The result of make_oidjoins_check should be installed as the "oidjoins" 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.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 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. --------------------------------------------------------------------------- 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 Join pg_catalog.pg_am.amgettuple => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.amgetbitmap => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.amrescan => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.amendscan => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.ammarkpos => pg_catalog.pg_proc.oid Join pg_catalog.pg_am.amrestrpos => pg_catalog.pg_proc.oid 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 Join pg_catalog.pg_amop.amoplefttype => pg_catalog.pg_type.oid Join pg_catalog.pg_amop.amoprighttype => pg_catalog.pg_type.oid Join pg_catalog.pg_amop.amopopr => pg_catalog.pg_operator.oid Join pg_catalog.pg_amop.amopmethod => pg_catalog.pg_am.oid Join pg_catalog.pg_amop.amopsortfamily => pg_catalog.pg_opfamily.oid Join pg_catalog.pg_amproc.amprocfamily => pg_catalog.pg_opfamily.oid Join pg_catalog.pg_amproc.amproclefttype => pg_catalog.pg_type.oid Join pg_catalog.pg_amproc.amprocrighttype => pg_catalog.pg_type.oid Join pg_catalog.pg_amproc.amproc => pg_catalog.pg_proc.oid 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_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 Join pg_catalog.pg_class.relnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_class.reltype => pg_catalog.pg_type.oid Join pg_catalog.pg_class.reloftype => pg_catalog.pg_type.oid Join pg_catalog.pg_class.relowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_class.relam => pg_catalog.pg_am.oid Join pg_catalog.pg_class.reltablespace => pg_catalog.pg_tablespace.oid Join pg_catalog.pg_class.reltoastrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_collation.collnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_collation.collowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_constraint.connamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_constraint.conrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_constraint.contypid => pg_catalog.pg_type.oid Join pg_catalog.pg_constraint.conindid => pg_catalog.pg_class.oid Join pg_catalog.pg_constraint.confrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_conversion.connamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_conversion.conowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_conversion.conproc => pg_catalog.pg_proc.oid Join pg_catalog.pg_database.datdba => pg_catalog.pg_authid.oid Join pg_catalog.pg_database.dattablespace => pg_catalog.pg_tablespace.oid Join pg_catalog.pg_db_role_setting.setdatabase => pg_catalog.pg_database.oid Join pg_catalog.pg_depend.classid => pg_catalog.pg_class.oid Join pg_catalog.pg_depend.refclassid => pg_catalog.pg_class.oid Join pg_catalog.pg_description.classoid => pg_catalog.pg_class.oid Join pg_catalog.pg_enum.enumtypid => pg_catalog.pg_type.oid Join pg_catalog.pg_extension.extowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_extension.extnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_index.indexrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_index.indrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_inherits.inhrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_inherits.inhparent => pg_catalog.pg_class.oid Join pg_catalog.pg_language.lanowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_language.lanplcallfoid => pg_catalog.pg_proc.oid Join pg_catalog.pg_language.laninline => pg_catalog.pg_proc.oid Join pg_catalog.pg_language.lanvalidator => pg_catalog.pg_proc.oid Join pg_catalog.pg_namespace.nspowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_opclass.opcmethod => pg_catalog.pg_am.oid Join pg_catalog.pg_opclass.opcnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_opclass.opcowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_opclass.opcfamily => pg_catalog.pg_opfamily.oid Join pg_catalog.pg_opclass.opcintype => pg_catalog.pg_type.oid Join pg_catalog.pg_opclass.opckeytype => pg_catalog.pg_type.oid Join pg_catalog.pg_operator.oprnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_operator.oprowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_operator.oprleft => pg_catalog.pg_type.oid Join pg_catalog.pg_operator.oprright => pg_catalog.pg_type.oid Join pg_catalog.pg_operator.oprresult => pg_catalog.pg_type.oid Join pg_catalog.pg_operator.oprcom => pg_catalog.pg_operator.oid Join pg_catalog.pg_operator.oprnegate => pg_catalog.pg_operator.oid Join pg_catalog.pg_operator.oprcode => pg_catalog.pg_proc.oid Join pg_catalog.pg_operator.oprrest => pg_catalog.pg_proc.oid 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_proc.pronamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_proc.proowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_proc.prolang => pg_catalog.pg_language.oid Join pg_catalog.pg_proc.provariadic => pg_catalog.pg_type.oid Join pg_catalog.pg_proc.protransform => pg_catalog.pg_proc.oid Join pg_catalog.pg_proc.prorettype => pg_catalog.pg_type.oid Join pg_catalog.pg_range.rngtypid => pg_catalog.pg_type.oid Join pg_catalog.pg_range.rngsubtype => pg_catalog.pg_type.oid Join pg_catalog.pg_range.rngcollation => pg_catalog.pg_collation.oid 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_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 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 Join pg_catalog.pg_trigger.tgconstrrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_trigger.tgconstrindid => pg_catalog.pg_class.oid Join pg_catalog.pg_trigger.tgconstraint => pg_catalog.pg_constraint.oid Join pg_catalog.pg_ts_config.cfgnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_ts_config.cfgowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_ts_config.cfgparser => pg_catalog.pg_ts_parser.oid Join pg_catalog.pg_ts_config_map.mapcfg => pg_catalog.pg_ts_config.oid Join pg_catalog.pg_ts_config_map.mapdict => pg_catalog.pg_ts_dict.oid Join pg_catalog.pg_ts_dict.dictnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_ts_dict.dictowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_ts_dict.dicttemplate => pg_catalog.pg_ts_template.oid Join pg_catalog.pg_ts_parser.prsnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_ts_parser.prsstart => pg_catalog.pg_proc.oid Join pg_catalog.pg_ts_parser.prstoken => pg_catalog.pg_proc.oid Join pg_catalog.pg_ts_parser.prsend => pg_catalog.pg_proc.oid Join pg_catalog.pg_ts_parser.prsheadline => pg_catalog.pg_proc.oid Join pg_catalog.pg_ts_parser.prslextype => pg_catalog.pg_proc.oid Join pg_catalog.pg_ts_template.tmplnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_ts_template.tmplinit => pg_catalog.pg_proc.oid Join pg_catalog.pg_ts_template.tmpllexize => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_type.typowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_type.typrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_type.typelem => pg_catalog.pg_type.oid Join pg_catalog.pg_type.typarray => pg_catalog.pg_type.oid Join pg_catalog.pg_type.typinput => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typoutput => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typreceive => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typsend => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typmodin => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typmodout => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typanalyze => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typbasetype => pg_catalog.pg_type.oid Join pg_catalog.pg_type.typcollation => pg_catalog.pg_collation.oid Join pg_catalog.pg_constraint.conpfeqop []=> pg_catalog.pg_operator.oid Join pg_catalog.pg_constraint.conppeqop []=> pg_catalog.pg_operator.oid Join pg_catalog.pg_constraint.conffeqop []=> pg_catalog.pg_operator.oid Join pg_catalog.pg_constraint.conexclop []=> pg_catalog.pg_operator.oid Join pg_catalog.pg_proc.proallargtypes []=> pg_catalog.pg_type.oid --------------------------------------------------------------------------- Bruce Momjian (bruce@momjian.us) Updated for 7.3 by Joe Conway (mail@joeconway.com)