| $PostgreSQL: pgsql/src/tools/findoidjoins/README,v 1.6 2008/10/13 12:59:57 tgl Exp $ |
| |
| findoidjoins |
| ============ |
| |
| This program scans a database and prints oid fields (also reg* fields) |
| and the tables they join to. We don't really recommend running it on |
| anything but an empty database, such as template1; else it's likely to |
| be very slow. |
| |
| Run on an empty database, it returns the system join relationships (shown |
| below for 8.4devel). 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 8.4devel, the *only* fields that should join to more |
| than one target 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.) NOTE: in an empty database, |
| findoidjoins may not report joins for pg_shdescription and pg_shdepend for |
| lack of any entries there. |
| |
| 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. |
| |
| 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. |
| (Ideally we'd just regenerate the script as part of the regression |
| tests themselves, but that seems too slow...) |
| |
| NOTE: in 8.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. |
| You might also get output for pg_shdepend.refobjid and pg_shdescription.objoid, |
| neither of which should be added. |
| |
| --------------------------------------------------------------------------- |
| |
| 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.aggsortop => pg_catalog.pg_operator.oid |
| Join pg_catalog.pg_aggregate.aggtranstype => 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.ambulkdelete => pg_catalog.pg_proc.oid |
| Join pg_catalog.pg_am.amvacuumcleanup => 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_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_attribute.attrelid => pg_catalog.pg_class.oid |
| Join pg_catalog.pg_attribute.atttypid => pg_catalog.pg_type.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.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_class.reltoastidxid => pg_catalog.pg_class.oid |
| Join pg_catalog.pg_constraint.connamespace => pg_catalog.pg_namespace.oid |
| Join pg_catalog.pg_constraint.contypid => pg_catalog.pg_type.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_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_index.indexrelid => pg_catalog.pg_class.oid |
| Join pg_catalog.pg_index.indrelid => pg_catalog.pg_class.oid |
| Join pg_catalog.pg_language.lanowner => pg_catalog.pg_authid.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.prorettype => pg_catalog.pg_type.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_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_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 |
| |
| --------------------------------------------------------------------------- |
| |
| Bruce Momjian (root@candle.pha.pa.us) |
| Updated for 7.3 by Joe Conway (mail@joeconway.com) |