src/tools/findoidjoins/README

   1 $PostgreSQL$
   2
   3 findoidjoins
   4 ============
   5
   6 This program scans a database and prints oid fields (also reg* fields)
   7 and the tables they join to.  We don't really recommend running it on
   8 anything but an empty database, such as template1; else it's likely to
   9 be very slow.
  10
  11 Run on an empty database, it returns the system join relationships (shown
  12 below for 8.4devel).  Note that unexpected matches may indicate bogus entries
  13 in system tables --- don't accept a peculiar match without question.
  14 In particular, a field shown as joining to more than one target table is
  15 probably messed up.  In 8.4devel, the *only* fields that should join to more
  16 than one target are pg_description.objoid, pg_depend.objid,
  17 pg_depend.refobjid, pg_shdescription.objoid, pg_shdepend.objid, and
  18 pg_shdepend.refobjid.  (Running make_oidjoins_check is an easy way to spot
  19 fields joining to more than one table, BTW.)  NOTE: in an empty database,
  20 findoidjoins may not report joins for pg_shdescription and pg_shdepend for
  21 lack of any entries there.
  22
  23 The shell script make_oidjoins_check converts findoidjoins' output
  24 into an SQL script that checks for dangling links (entries in an
  25 OID or REG* column that don't match any row in the expected table).
  26 Note that fields joining to more than one table are NOT processed.
  27
  28 The result of make_oidjoins_check should be installed as the "oidjoins"
  29 regression test.  The oidjoins test should be updated after any
  30 revision in the patterns of cross-links between system tables.
  31 (Ideally we'd just regenerate the script as part of the regression
  32 tests themselves, but that seems too slow...)
  33
  34 NOTE: in 8.4devel, make_oidjoins_check produces two bogus join checks:
  35 Join pg_catalog.pg_class.relfilenode => pg_catalog.pg_class.oid
  36 Join pg_catalog.pg_database.datlastsysoid => pg_catalog.pg_database.oid
  37 These are artifacts and should not be added to the oidjoins regress test.
  38 You might also get output for pg_shdepend.refobjid and pg_shdescription.objoid,
  39 neither of which should be added.
  40
  41 ---------------------------------------------------------------------------
  42
  43 Join pg_catalog.pg_aggregate.aggfnoid => pg_catalog.pg_proc.oid
  44 Join pg_catalog.pg_aggregate.aggtransfn => pg_catalog.pg_proc.oid
  45 Join pg_catalog.pg_aggregate.aggfinalfn => pg_catalog.pg_proc.oid
  46 Join pg_catalog.pg_aggregate.aggsortop => pg_catalog.pg_operator.oid
  47 Join pg_catalog.pg_aggregate.aggtranstype => pg_catalog.pg_type.oid
  48 Join pg_catalog.pg_am.amkeytype => pg_catalog.pg_type.oid
  49 Join pg_catalog.pg_am.aminsert => pg_catalog.pg_proc.oid
  50 Join pg_catalog.pg_am.ambeginscan => pg_catalog.pg_proc.oid
  51 Join pg_catalog.pg_am.amgettuple => pg_catalog.pg_proc.oid
  52 Join pg_catalog.pg_am.amgetbitmap => pg_catalog.pg_proc.oid
  53 Join pg_catalog.pg_am.amrescan => pg_catalog.pg_proc.oid
  54 Join pg_catalog.pg_am.amendscan => pg_catalog.pg_proc.oid
  55 Join pg_catalog.pg_am.ammarkpos => pg_catalog.pg_proc.oid
  56 Join pg_catalog.pg_am.amrestrpos => pg_catalog.pg_proc.oid
  57 Join pg_catalog.pg_am.ambuild => pg_catalog.pg_proc.oid
  58 Join pg_catalog.pg_am.ambulkdelete => pg_catalog.pg_proc.oid
  59 Join pg_catalog.pg_am.amvacuumcleanup => pg_catalog.pg_proc.oid
  60 Join pg_catalog.pg_am.amcostestimate => pg_catalog.pg_proc.oid
  61 Join pg_catalog.pg_am.amoptions => pg_catalog.pg_proc.oid
  62 Join pg_catalog.pg_amop.amopfamily => pg_catalog.pg_opfamily.oid
  63 Join pg_catalog.pg_amop.amoplefttype => pg_catalog.pg_type.oid
  64 Join pg_catalog.pg_amop.amoprighttype => pg_catalog.pg_type.oid
  65 Join pg_catalog.pg_amop.amopopr => pg_catalog.pg_operator.oid
  66 Join pg_catalog.pg_amop.amopmethod => pg_catalog.pg_am.oid
  67 Join pg_catalog.pg_amproc.amprocfamily => pg_catalog.pg_opfamily.oid
  68 Join pg_catalog.pg_amproc.amproclefttype => pg_catalog.pg_type.oid
  69 Join pg_catalog.pg_amproc.amprocrighttype => pg_catalog.pg_type.oid
  70 Join pg_catalog.pg_amproc.amproc => pg_catalog.pg_proc.oid
  71 Join pg_catalog.pg_attribute.attrelid => pg_catalog.pg_class.oid
  72 Join pg_catalog.pg_attribute.atttypid => pg_catalog.pg_type.oid
  73 Join pg_catalog.pg_cast.castsource => pg_catalog.pg_type.oid
  74 Join pg_catalog.pg_cast.casttarget => pg_catalog.pg_type.oid
  75 Join pg_catalog.pg_cast.castfunc => pg_catalog.pg_proc.oid
  76 Join pg_catalog.pg_class.relnamespace => pg_catalog.pg_namespace.oid
  77 Join pg_catalog.pg_class.reltype => pg_catalog.pg_type.oid
  78 Join pg_catalog.pg_class.relowner => pg_catalog.pg_authid.oid
  79 Join pg_catalog.pg_class.relam => pg_catalog.pg_am.oid
  80 Join pg_catalog.pg_class.reltablespace => pg_catalog.pg_tablespace.oid
  81 Join pg_catalog.pg_class.reltoastrelid => pg_catalog.pg_class.oid
  82 Join pg_catalog.pg_class.reltoastidxid => pg_catalog.pg_class.oid
  83 Join pg_catalog.pg_constraint.connamespace => pg_catalog.pg_namespace.oid
  84 Join pg_catalog.pg_constraint.contypid => pg_catalog.pg_type.oid
  85 Join pg_catalog.pg_conversion.connamespace => pg_catalog.pg_namespace.oid
  86 Join pg_catalog.pg_conversion.conowner => pg_catalog.pg_authid.oid
  87 Join pg_catalog.pg_conversion.conproc => pg_catalog.pg_proc.oid
  88 Join pg_catalog.pg_database.datdba => pg_catalog.pg_authid.oid
  89 Join pg_catalog.pg_database.dattablespace => pg_catalog.pg_tablespace.oid
  90 Join pg_catalog.pg_depend.classid => pg_catalog.pg_class.oid
  91 Join pg_catalog.pg_depend.refclassid => pg_catalog.pg_class.oid
  92 Join pg_catalog.pg_description.classoid => pg_catalog.pg_class.oid
  93 Join pg_catalog.pg_index.indexrelid => pg_catalog.pg_class.oid
  94 Join pg_catalog.pg_index.indrelid => pg_catalog.pg_class.oid
  95 Join pg_catalog.pg_language.lanowner => pg_catalog.pg_authid.oid
  96 Join pg_catalog.pg_language.lanvalidator => pg_catalog.pg_proc.oid
  97 Join pg_catalog.pg_namespace.nspowner => pg_catalog.pg_authid.oid
  98 Join pg_catalog.pg_opclass.opcmethod => pg_catalog.pg_am.oid
  99 Join pg_catalog.pg_opclass.opcnamespace => pg_catalog.pg_namespace.oid
 100 Join pg_catalog.pg_opclass.opcowner => pg_catalog.pg_authid.oid
 101 Join pg_catalog.pg_opclass.opcfamily => pg_catalog.pg_opfamily.oid
 102 Join pg_catalog.pg_opclass.opcintype => pg_catalog.pg_type.oid
 103 Join pg_catalog.pg_opclass.opckeytype => pg_catalog.pg_type.oid
 104 Join pg_catalog.pg_operator.oprnamespace => pg_catalog.pg_namespace.oid
 105 Join pg_catalog.pg_operator.oprowner => pg_catalog.pg_authid.oid
 106 Join pg_catalog.pg_operator.oprleft => pg_catalog.pg_type.oid
 107 Join pg_catalog.pg_operator.oprright => pg_catalog.pg_type.oid
 108 Join pg_catalog.pg_operator.oprresult => pg_catalog.pg_type.oid
 109 Join pg_catalog.pg_operator.oprcom => pg_catalog.pg_operator.oid
 110 Join pg_catalog.pg_operator.oprnegate => pg_catalog.pg_operator.oid
 111 Join pg_catalog.pg_operator.oprcode => pg_catalog.pg_proc.oid
 112 Join pg_catalog.pg_operator.oprrest => pg_catalog.pg_proc.oid
 113 Join pg_catalog.pg_operator.oprjoin => pg_catalog.pg_proc.oid
 114 Join pg_catalog.pg_opfamily.opfmethod => pg_catalog.pg_am.oid
 115 Join pg_catalog.pg_opfamily.opfnamespace => pg_catalog.pg_namespace.oid
 116 Join pg_catalog.pg_opfamily.opfowner => pg_catalog.pg_authid.oid
 117 Join pg_catalog.pg_proc.pronamespace => pg_catalog.pg_namespace.oid
 118 Join pg_catalog.pg_proc.proowner => pg_catalog.pg_authid.oid
 119 Join pg_catalog.pg_proc.prolang => pg_catalog.pg_language.oid
 120 Join pg_catalog.pg_proc.prorettype => pg_catalog.pg_type.oid
 121 Join pg_catalog.pg_rewrite.ev_class => pg_catalog.pg_class.oid
 122 Join pg_catalog.pg_shdepend.refclassid => pg_catalog.pg_class.oid
 123 Join pg_catalog.pg_shdescription.classoid => pg_catalog.pg_class.oid
 124 Join pg_catalog.pg_statistic.starelid => pg_catalog.pg_class.oid
 125 Join pg_catalog.pg_statistic.staop1 => pg_catalog.pg_operator.oid
 126 Join pg_catalog.pg_statistic.staop2 => pg_catalog.pg_operator.oid
 127 Join pg_catalog.pg_statistic.staop3 => pg_catalog.pg_operator.oid
 128 Join pg_catalog.pg_tablespace.spcowner => pg_catalog.pg_authid.oid
 129 Join pg_catalog.pg_trigger.tgrelid => pg_catalog.pg_class.oid
 130 Join pg_catalog.pg_trigger.tgfoid => pg_catalog.pg_proc.oid
 131 Join pg_catalog.pg_ts_config.cfgnamespace => pg_catalog.pg_namespace.oid
 132 Join pg_catalog.pg_ts_config.cfgowner => pg_catalog.pg_authid.oid
 133 Join pg_catalog.pg_ts_config.cfgparser => pg_catalog.pg_ts_parser.oid
 134 Join pg_catalog.pg_ts_config_map.mapcfg => pg_catalog.pg_ts_config.oid
 135 Join pg_catalog.pg_ts_config_map.mapdict => pg_catalog.pg_ts_dict.oid
 136 Join pg_catalog.pg_ts_dict.dictnamespace => pg_catalog.pg_namespace.oid
 137 Join pg_catalog.pg_ts_dict.dictowner => pg_catalog.pg_authid.oid
 138 Join pg_catalog.pg_ts_dict.dicttemplate => pg_catalog.pg_ts_template.oid
 139 Join pg_catalog.pg_ts_parser.prsnamespace => pg_catalog.pg_namespace.oid
 140 Join pg_catalog.pg_ts_parser.prsstart => pg_catalog.pg_proc.oid
 141 Join pg_catalog.pg_ts_parser.prstoken => pg_catalog.pg_proc.oid
 142 Join pg_catalog.pg_ts_parser.prsend => pg_catalog.pg_proc.oid
 143 Join pg_catalog.pg_ts_parser.prsheadline => pg_catalog.pg_proc.oid
 144 Join pg_catalog.pg_ts_parser.prslextype => pg_catalog.pg_proc.oid
 145 Join pg_catalog.pg_ts_template.tmplnamespace => pg_catalog.pg_namespace.oid
 146 Join pg_catalog.pg_ts_template.tmplinit => pg_catalog.pg_proc.oid
 147 Join pg_catalog.pg_ts_template.tmpllexize => pg_catalog.pg_proc.oid
 148 Join pg_catalog.pg_type.typnamespace => pg_catalog.pg_namespace.oid
 149 Join pg_catalog.pg_type.typowner => pg_catalog.pg_authid.oid
 150 Join pg_catalog.pg_type.typrelid => pg_catalog.pg_class.oid
 151 Join pg_catalog.pg_type.typelem => pg_catalog.pg_type.oid
 152 Join pg_catalog.pg_type.typarray => pg_catalog.pg_type.oid
 153 Join pg_catalog.pg_type.typinput => pg_catalog.pg_proc.oid
 154 Join pg_catalog.pg_type.typoutput => pg_catalog.pg_proc.oid
 155 Join pg_catalog.pg_type.typreceive => pg_catalog.pg_proc.oid
 156 Join pg_catalog.pg_type.typsend => pg_catalog.pg_proc.oid
 157 Join pg_catalog.pg_type.typmodin => pg_catalog.pg_proc.oid
 158 Join pg_catalog.pg_type.typmodout => pg_catalog.pg_proc.oid
 159 Join pg_catalog.pg_type.typanalyze => pg_catalog.pg_proc.oid
 160 Join pg_catalog.pg_type.typbasetype => pg_catalog.pg_type.oid
 161
 162 ---------------------------------------------------------------------------
 163
 164 Bruce Momjian (root@candle.pha.pa.us)
 165 Updated for 7.3 by Joe Conway (mail@joeconway.com)