Thread: findoidjoins patch (was Re: [HACKERS] findoidjoins)

findoidjoins patch (was Re: [HACKERS] findoidjoins)

From

Joe Conway

Date:

05 September 2002, 04:27:07

Tom Lane wrote:
> I'd be inclined to reproduce the original behavior.  findoidjoins is
> pretty slow already, and I don't much want to slow it down more in order
> to provide info that's useless for the primary purpose.

Here's take two. It produces results similar to the previous version,
but using libpq and schema aware queries.


> use it for checking the stuff in pg_catalog.  So I'm not at all sure why
> I made that note.  Do you get a plausible set of joins out of your
> version?

Looks reasonable to me. I attached the outputs of findoidjoins and
make_oidjoins_check for review as well.

Please review and commit, or kick back to me if more work is needed.

Thanks,

Joe

Index: contrib/findoidjoins/Makefile
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/Makefile,v
retrieving revision 1.13
diff -c -r1.13 Makefile
*** contrib/findoidjoins/Makefile    6 Sep 2001 10:49:29 -0000    1.13
--- contrib/findoidjoins/Makefile    4 Sep 2002 23:36:27 -0000
***************
*** 1,5 ****
- # $Header: /opt/src/cvs/pgsql-server/contrib/findoidjoins/Makefile,v 1.13 2001/09/06 10:49:29 petere Exp $
-
  subdir = contrib/findoidjoins
  top_builddir = ../..
  include $(top_builddir)/src/Makefile.global
--- 1,3 ----
***************
*** 7,17 ****
  PROGRAM = findoidjoins
  OBJS    = findoidjoins.o

! libpgeasy_srcdir = $(top_srcdir)/src/interfaces/libpgeasy
! libpgeasy_builddir = $(top_builddir)/src/interfaces/libpgeasy
!
! PG_CPPFLAGS = -I$(libpgeasy_srcdir) -I$(libpq_srcdir)
! PG_LIBS = -L$(libpgeasy_builddir) -lpgeasy $(libpq)

  SCRIPTS = make_oidjoins_check
  DOCS = README.findoidjoins
--- 5,12 ----
  PROGRAM = findoidjoins
  OBJS    = findoidjoins.o

! PG_CPPFLAGS = -I$(libpq_srcdir)
! PG_LIBS = $(libpq)

  SCRIPTS = make_oidjoins_check
  DOCS = README.findoidjoins
Index: contrib/findoidjoins/README.findoidjoins
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/README.findoidjoins,v
retrieving revision 1.5
diff -c -r1.5 README.findoidjoins
*** contrib/findoidjoins/README.findoidjoins    25 Apr 2002 02:56:55 -0000    1.5
--- contrib/findoidjoins/README.findoidjoins    5 Sep 2002 04:42:21 -0000
***************
*** 1,24 ****

                       findoidjoins

! This program scans a database, and prints oid fields (also regproc, regclass
! and regtype fields) and the tables they join to.  CAUTION: it is ver-r-r-y
! slow on a large database, or even a not-so-large one.  We don't really
! recommend running it on anything but an empty database, such as template1.
!
! Uses pgeasy library.

  Run on an empty database, it returns the system join relationships (shown
! below for 7.2).  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 7.2, the *only* field that should join to more
! than one target is pg_description.objoid.  (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 REGPROC 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"
--- 1,22 ----

                       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.

  Run on an empty database, it returns the system join relationships (shown
! below for 7.3).  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 7.3, the *only* fields that should join to more
! than one target are pg_description.objoid, pg_depend.objid, and
! pg_depend.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* columns 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"
***************
*** 27,43 ****
  (Ideally we'd just regenerate the script as part of the regression
  tests themselves, but that seems too slow...)

! NOTE: in 7.2, make_oidjoins_check produces one bogus join check, for
  pg_class.relfilenode => pg_class.oid.  This is an artifact and should not
  be added to the oidjoins regress test.

  ---------------------------------------------------------------------------
!
  Join pg_aggregate.aggtransfn => pg_proc.oid
  Join pg_aggregate.aggfinalfn => pg_proc.oid
- Join pg_aggregate.aggbasetype => pg_type.oid
  Join pg_aggregate.aggtranstype => pg_type.oid
- Join pg_aggregate.aggfinaltype => pg_type.oid
  Join pg_am.amgettuple => pg_proc.oid
  Join pg_am.aminsert => pg_proc.oid
  Join pg_am.ambeginscan => pg_proc.oid
--- 25,39 ----
  (Ideally we'd just regenerate the script as part of the regression
  tests themselves, but that seems too slow...)

! NOTE: in 7.3, make_oidjoins_check produces one bogus join check, for
  pg_class.relfilenode => pg_class.oid.  This is an artifact and should not
  be added to the oidjoins regress test.

  ---------------------------------------------------------------------------
! Join pg_aggregate.aggfnoid => pg_proc.oid
  Join pg_aggregate.aggtransfn => pg_proc.oid
  Join pg_aggregate.aggfinalfn => pg_proc.oid
  Join pg_aggregate.aggtranstype => pg_type.oid
  Join pg_am.amgettuple => pg_proc.oid
  Join pg_am.aminsert => pg_proc.oid
  Join pg_am.ambeginscan => pg_proc.oid
***************
*** 54,68 ****
--- 50,95 ----
  Join pg_amproc.amproc => pg_proc.oid
  Join pg_attribute.attrelid => pg_class.oid
  Join pg_attribute.atttypid => pg_type.oid
+ Join pg_cast.castsource => pg_type.oid
+ Join pg_cast.casttarget => pg_type.oid
+ Join pg_cast.castfunc => pg_proc.oid
+ Join pg_class.relnamespace => pg_namespace.oid
  Join pg_class.reltype => pg_type.oid
  Join pg_class.relam => pg_am.oid
+ Join pg_class.relfilenode => pg_class.oid
  Join pg_class.reltoastrelid => pg_class.oid
  Join pg_class.reltoastidxid => pg_class.oid
+ Join pg_conversion.connamespace => pg_namespace.oid
+ Join pg_conversion.conproc => pg_proc.oid
+ Join pg_database.datlastsysoid => pg_conversion.oid
+ Join pg_depend.classid => pg_class.oid
+ Join pg_depend.objid => pg_conversion.oid
+ Join pg_depend.objid => pg_rewrite.oid
+ Join pg_depend.objid => pg_type.oid
+ Join pg_depend.refclassid => pg_class.oid
+ Join pg_depend.refobjid => pg_cast.oid
+ Join pg_depend.refobjid => pg_class.oid
+ Join pg_depend.refobjid => pg_language.oid
+ Join pg_depend.refobjid => pg_namespace.oid
+ Join pg_depend.refobjid => pg_opclass.oid
+ Join pg_depend.refobjid => pg_operator.oid
+ Join pg_depend.refobjid => pg_proc.oid
+ Join pg_depend.refobjid => pg_trigger.oid
+ Join pg_depend.refobjid => pg_type.oid
+ Join pg_description.objoid => pg_am.oid
+ Join pg_description.objoid => pg_database.oid
+ Join pg_description.objoid => pg_language.oid
+ Join pg_description.objoid => pg_namespace.oid
+ Join pg_description.objoid => pg_proc.oid
+ Join pg_description.objoid => pg_type.oid
  Join pg_description.classoid => pg_class.oid
  Join pg_index.indexrelid => pg_class.oid
  Join pg_index.indrelid => pg_class.oid
+ Join pg_language.lanvalidator => pg_proc.oid
  Join pg_opclass.opcamid => pg_am.oid
+ Join pg_opclass.opcnamespace => pg_namespace.oid
  Join pg_opclass.opcintype => pg_type.oid
+ Join pg_operator.oprnamespace => pg_namespace.oid
  Join pg_operator.oprleft => pg_type.oid
  Join pg_operator.oprright => pg_type.oid
  Join pg_operator.oprresult => pg_type.oid
***************
*** 70,94 ****
  Join pg_operator.oprnegate => pg_operator.oid
  Join pg_operator.oprlsortop => pg_operator.oid
  Join pg_operator.oprrsortop => pg_operator.oid
  Join pg_operator.oprcode => pg_proc.oid
  Join pg_operator.oprrest => pg_proc.oid
  Join pg_operator.oprjoin => pg_proc.oid
  Join pg_proc.prolang => pg_language.oid
  Join pg_proc.prorettype => pg_type.oid
  Join pg_rewrite.ev_class => pg_class.oid
- Join pg_statistic.starelid => pg_class.oid
- Join pg_statistic.staop1 => pg_operator.oid
- Join pg_statistic.staop2 => pg_operator.oid
- Join pg_statistic.staop3 => pg_operator.oid
  Join pg_trigger.tgrelid => pg_class.oid
  Join pg_trigger.tgfoid => pg_proc.oid
  Join pg_type.typrelid => pg_class.oid
  Join pg_type.typelem => pg_type.oid
  Join pg_type.typinput => pg_proc.oid
  Join pg_type.typoutput => pg_proc.oid
- Join pg_type.typreceive => pg_proc.oid
- Join pg_type.typsend => pg_proc.oid
-
  ---------------------------------------------------------------------------

  Bruce Momjian (root@candle.pha.pa.us)
--- 97,119 ----
  Join pg_operator.oprnegate => pg_operator.oid
  Join pg_operator.oprlsortop => pg_operator.oid
  Join pg_operator.oprrsortop => pg_operator.oid
+ Join pg_operator.oprltcmpop => pg_operator.oid
+ Join pg_operator.oprgtcmpop => pg_operator.oid
  Join pg_operator.oprcode => pg_proc.oid
  Join pg_operator.oprrest => pg_proc.oid
  Join pg_operator.oprjoin => pg_proc.oid
+ Join pg_proc.pronamespace => pg_namespace.oid
  Join pg_proc.prolang => pg_language.oid
  Join pg_proc.prorettype => pg_type.oid
  Join pg_rewrite.ev_class => pg_class.oid
  Join pg_trigger.tgrelid => pg_class.oid
  Join pg_trigger.tgfoid => pg_proc.oid
+ Join pg_type.typnamespace => pg_namespace.oid
  Join pg_type.typrelid => pg_class.oid
  Join pg_type.typelem => pg_type.oid
  Join pg_type.typinput => pg_proc.oid
  Join pg_type.typoutput => pg_proc.oid
  ---------------------------------------------------------------------------

  Bruce Momjian (root@candle.pha.pa.us)
+ Updated for 7.3 by Joe Conway (mail@joeconway.com)
Index: contrib/findoidjoins/findoidjoins.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/findoidjoins.c,v
retrieving revision 1.17
diff -c -r1.17 findoidjoins.c
*** contrib/findoidjoins/findoidjoins.c    4 Sep 2002 20:31:06 -0000    1.17
--- contrib/findoidjoins/findoidjoins.c    5 Sep 2002 04:51:16 -0000
***************
*** 1,109 ****
  /*
!  * findoidjoins.c, requires src/interfaces/libpgeasy
   *
   */
- #include "postgres_fe.h"

! #include "libpq-fe.h"
! #include "halt.h"
! #include "libpgeasy.h"

! PGresult   *attres,
!            *relres;

  int
  main(int argc, char **argv)
  {
!     char        query[4000];
!     char        relname[256];
!     char        relname2[256];
!     char        attname[256];
!     char        typname[256];
!     int            count;
!     char        optstr[256];

      if (argc != 2)
!         halt("Usage:  %s database\n", argv[0]);

!     snprintf(optstr, 256, "dbname=%s", argv[1]);
!     connectdb(optstr);

!     on_error_continue();
!     on_error_stop();

!     doquery("BEGIN WORK");
!     doquery("\
!         DECLARE c_attributes BINARY CURSOR FOR \
!         SELECT typname, relname, a.attname \
!         FROM pg_class c, pg_attribute a, pg_type t \
!         WHERE a.attnum > 0 AND \
!               relkind = 'r' AND \
!               (typname = 'oid' OR \
!                typname = 'regproc' OR \
!                typname = 'regclass' OR \
!                typname = 'regtype') AND \
!               a.attrelid = c.oid AND \
!               a.atttypid = t.oid \
!         ORDER BY 2, a.attnum ; \
!         ");
!     doquery("FETCH ALL IN c_attributes");
!     attres = get_result();
!
!     doquery("\
!         DECLARE c_relations BINARY CURSOR FOR \
!         SELECT relname \
!         FROM pg_class c \
!         WHERE relkind = 'r' AND relhasoids \
!         ORDER BY 1; \
!         ");
!     doquery("FETCH ALL IN c_relations");
!     relres = get_result();

!     set_result(attres);
!     while (fetch(typname, relname, attname) != END_OF_TUPLES)
      {
!         set_result(relres);
!         reset_fetch();
!         while (fetch(relname2) != END_OF_TUPLES)
!         {
!             unset_result(relres);
!             if (strcmp(typname, "oid") == 0)
!                 snprintf(query, 4000, "\
!                     DECLARE c_matches BINARY CURSOR FOR \
!                     SELECT    count(*)::int4 \
!                         FROM \"%s\" t1, \"%s\" t2 \
!                     WHERE t1.\"%s\" = t2.oid ",
!                          relname, relname2, attname);
!             else
!                 sprintf(query, 4000, "\
!                     DECLARE c_matches BINARY CURSOR FOR \
!                     SELECT    count(*)::int4 \
!                         FROM \"%s\" t1, \"%s\" t2 \
!                     WHERE t1.\"%s\"::oid = t2.oid ",
!                         relname, relname2, attname);
!
!             doquery(query);
!             doquery("FETCH ALL IN c_matches");
!             fetch(&count);
!             if (count != 0)
!                 printf("Join %s.%s => %s.oid\n", relname, attname, relname2);
!             doquery("CLOSE c_matches");
!             set_result(relres);
!         }
!         set_result(attres);
      }

!     set_result(relres);
!     doquery("CLOSE c_relations");
!     PQclear(relres);
!
!     set_result(attres);
!     doquery("CLOSE c_attributes");
!     PQclear(attres);
!     unset_result(attres);

!     doquery("COMMIT WORK");

!     disconnectdb();
!     return 0;
  }
--- 1,152 ----
  /*
!  * findoidjoins
!  *
!  * Copyright 2002 by PostgreSQL Global Development Group
!  *
!  * Permission to use, copy, modify, and distribute this software and its
!  * documentation for any purpose, without fee, and without a written agreement
!  * is hereby granted, provided that the above copyright notice and this
!  * paragraph and the following two paragraphs appear in all copies.
!  *
!  * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
!  * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
!  * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
!  * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
!  * POSSIBILITY OF SUCH DAMAGE.
!  *
!  * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
!  * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
!  * AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS
!  * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
!  * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
   *
   */

! #include <stdlib.h>

! #include "postgres_fe.h"
! #include "libpq-fe.h"
! #include "pqexpbuffer.h"

  int
  main(int argc, char **argv)
  {
!     PGconn               *conn;
!     PQExpBufferData        sql;
!     PGresult           *res;
!     PGresult           *pkrel_res;
!     PGresult           *fkrel_res;
!     char               *fk_relname;
!     char               *fk_nspname;
!     char               *fk_attname;
!     char               *fk_typname;
!     char               *pk_relname;
!     char               *pk_nspname;
!     int                    fk, pk;        /* loop counters */

      if (argc != 2)
!     {
!         fprintf(stderr, "Usage:  %s database\n", argv[0]);
!         exit(EXIT_FAILURE);
!     }

!     initPQExpBuffer(&sql);
!     appendPQExpBuffer(&sql, "dbname=%s", argv[1]);

!     conn = PQconnectdb(sql.data);
!     if (PQstatus(conn) == CONNECTION_BAD)
!     {
!         fprintf(stderr, "connection error:  %s\n", PQerrorMessage(conn));
!         exit(EXIT_FAILURE);
!     }
!
!     termPQExpBuffer(&sql);
!     initPQExpBuffer(&sql);

!     appendPQExpBuffer(&sql, "%s",
!         "SELECT c.relname, (SELECT nspname FROM "
!         "pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
!         "FROM pg_catalog.pg_class c "
!         "WHERE c.relkind = 'r' "
!         "AND c.relhasoids "
!         "ORDER BY nspname, c.relname"
!         );

!     res = PQexec(conn, sql.data);
!     if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
      {
!         fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
!         exit(EXIT_FAILURE);
      }
+     pkrel_res = res;
+
+     termPQExpBuffer(&sql);
+     initPQExpBuffer(&sql);

!     appendPQExpBuffer(&sql, "%s",
!         "SELECT c.relname, "
!         "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
!         "a.attname, "
!         "t.typname "
!         "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, pg_catalog.pg_type t "
!         "WHERE a.attnum > 0 AND c.relkind = 'r' "
!         "AND t.typnamespace IN (SELECT n.oid FROM pg_catalog.pg_namespace n WHERE nspname LIKE 'pg\\_%') "
!         "AND (t.typname = 'oid' OR t.typname LIKE 'reg%') "
!         "AND a.attrelid = c.oid "
!         "AND a.atttypid = t.oid "
!         "ORDER BY nspname, c.relname, a.attnum"
!         );

!     res = PQexec(conn, sql.data);
!     if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
!     {
!         fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
!         exit(EXIT_FAILURE);
!     }
!     fkrel_res = res;
!
!     termPQExpBuffer(&sql);
!     initPQExpBuffer(&sql);
!
!     for (fk = 0; fk < PQntuples(fkrel_res); fk++)
!     {
!         fk_relname = PQgetvalue(fkrel_res, fk, 0);
!         fk_nspname = PQgetvalue(fkrel_res, fk, 1);
!         fk_attname = PQgetvalue(fkrel_res, fk, 2);
!         fk_typname = PQgetvalue(fkrel_res, fk, 3);
!
!         for (pk = 0; pk < PQntuples(pkrel_res); pk++)
!         {
!             pk_relname = PQgetvalue(pkrel_res, pk, 0);
!             pk_nspname = PQgetvalue(pkrel_res, pk, 1);
!
!             appendPQExpBuffer(&sql,
!                 "SELECT    1 "
!                 "FROM \"%s\".\"%s\" t1, "
!                 "\"%s\".\"%s\" t2 "
!                 "WHERE t1.\"%s\"::oid = t2.oid",
!                 fk_nspname, fk_relname, pk_nspname, pk_relname, fk_attname);
!
!             res = PQexec(conn, sql.data);
!             if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
!             {
!                 fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
!                 exit(EXIT_FAILURE);
!             }
!
!             if (PQntuples(res) != 0)
!                 printf("Join %s.%s => %s.oid\n",
!                         fk_relname, fk_attname, pk_relname);
!
!             PQclear(res);
!
!             termPQExpBuffer(&sql);
!             initPQExpBuffer(&sql);
!         }
!     }
!     PQclear(pkrel_res);
!     PQclear(fkrel_res);
!     PQfinish(conn);

!     exit(EXIT_SUCCESS);
  }
Join pg_aggregate.aggfnoid => pg_proc.oid
Join pg_aggregate.aggtransfn => pg_proc.oid
Join pg_aggregate.aggfinalfn => pg_proc.oid
Join pg_aggregate.aggtranstype => pg_type.oid
Join pg_am.amgettuple => pg_proc.oid
Join pg_am.aminsert => pg_proc.oid
Join pg_am.ambeginscan => pg_proc.oid
Join pg_am.amrescan => pg_proc.oid
Join pg_am.amendscan => pg_proc.oid
Join pg_am.ammarkpos => pg_proc.oid
Join pg_am.amrestrpos => pg_proc.oid
Join pg_am.ambuild => pg_proc.oid
Join pg_am.ambulkdelete => pg_proc.oid
Join pg_am.amcostestimate => pg_proc.oid
Join pg_amop.amopclaid => pg_opclass.oid
Join pg_amop.amopopr => pg_operator.oid
Join pg_amproc.amopclaid => pg_opclass.oid
Join pg_amproc.amproc => pg_proc.oid
Join pg_attribute.attrelid => pg_class.oid
Join pg_attribute.atttypid => pg_type.oid
Join pg_cast.castsource => pg_type.oid
Join pg_cast.casttarget => pg_type.oid
Join pg_cast.castfunc => pg_proc.oid
Join pg_class.relnamespace => pg_namespace.oid
Join pg_class.reltype => pg_type.oid
Join pg_class.relam => pg_am.oid
Join pg_class.relfilenode => pg_class.oid
Join pg_class.reltoastrelid => pg_class.oid
Join pg_class.reltoastidxid => pg_class.oid
Join pg_conversion.connamespace => pg_namespace.oid
Join pg_conversion.conproc => pg_proc.oid
Join pg_database.datlastsysoid => pg_conversion.oid
Join pg_depend.classid => pg_class.oid
Join pg_depend.objid => pg_conversion.oid
Join pg_depend.objid => pg_rewrite.oid
Join pg_depend.objid => pg_type.oid
Join pg_depend.refclassid => pg_class.oid
Join pg_depend.refobjid => pg_cast.oid
Join pg_depend.refobjid => pg_class.oid
Join pg_depend.refobjid => pg_language.oid
Join pg_depend.refobjid => pg_namespace.oid
Join pg_depend.refobjid => pg_opclass.oid
Join pg_depend.refobjid => pg_operator.oid
Join pg_depend.refobjid => pg_proc.oid
Join pg_depend.refobjid => pg_trigger.oid
Join pg_depend.refobjid => pg_type.oid
Join pg_description.objoid => pg_am.oid
Join pg_description.objoid => pg_database.oid
Join pg_description.objoid => pg_language.oid
Join pg_description.objoid => pg_namespace.oid
Join pg_description.objoid => pg_proc.oid
Join pg_description.objoid => pg_type.oid
Join pg_description.classoid => pg_class.oid
Join pg_index.indexrelid => pg_class.oid
Join pg_index.indrelid => pg_class.oid
Join pg_language.lanvalidator => pg_proc.oid
Join pg_opclass.opcamid => pg_am.oid
Join pg_opclass.opcnamespace => pg_namespace.oid
Join pg_opclass.opcintype => pg_type.oid
Join pg_operator.oprnamespace => pg_namespace.oid
Join pg_operator.oprleft => pg_type.oid
Join pg_operator.oprright => pg_type.oid
Join pg_operator.oprresult => pg_type.oid
Join pg_operator.oprcom => pg_operator.oid
Join pg_operator.oprnegate => pg_operator.oid
Join pg_operator.oprlsortop => pg_operator.oid
Join pg_operator.oprrsortop => pg_operator.oid
Join pg_operator.oprltcmpop => pg_operator.oid
Join pg_operator.oprgtcmpop => pg_operator.oid
Join pg_operator.oprcode => pg_proc.oid
Join pg_operator.oprrest => pg_proc.oid
Join pg_operator.oprjoin => pg_proc.oid
Join pg_proc.pronamespace => pg_namespace.oid
Join pg_proc.prolang => pg_language.oid
Join pg_proc.prorettype => pg_type.oid
Join pg_rewrite.ev_class => pg_class.oid
Join pg_trigger.tgrelid => pg_class.oid
Join pg_trigger.tgfoid => pg_proc.oid
Join pg_type.typnamespace => pg_namespace.oid
Join pg_type.typrelid => pg_class.oid
Join pg_type.typelem => pg_type.oid
Join pg_type.typinput => pg_proc.oid
Join pg_type.typoutput => pg_proc.oid
--
-- This is created by pgsql/contrib/findoidjoins/make_oidjoin_check
--
SELECT    ctid, pg_aggregate.aggfnoid
FROM    pg_aggregate
WHERE    pg_aggregate.aggfnoid != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfnoid);
SELECT    ctid, pg_aggregate.aggtransfn
FROM    pg_aggregate
WHERE    pg_aggregate.aggtransfn != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggtransfn);
SELECT    ctid, pg_aggregate.aggfinalfn
FROM    pg_aggregate
WHERE    pg_aggregate.aggfinalfn != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfinalfn);
SELECT    ctid, pg_aggregate.aggtranstype
FROM    pg_aggregate
WHERE    pg_aggregate.aggtranstype != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggtranstype);
SELECT    ctid, pg_am.amgettuple
FROM    pg_am
WHERE    pg_am.amgettuple != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amgettuple);
SELECT    ctid, pg_am.aminsert
FROM    pg_am
WHERE    pg_am.aminsert != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.aminsert);
SELECT    ctid, pg_am.ambeginscan
FROM    pg_am
WHERE    pg_am.ambeginscan != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambeginscan);
SELECT    ctid, pg_am.amrescan
FROM    pg_am
WHERE    pg_am.amrescan != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrescan);
SELECT    ctid, pg_am.amendscan
FROM    pg_am
WHERE    pg_am.amendscan != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amendscan);
SELECT    ctid, pg_am.ammarkpos
FROM    pg_am
WHERE    pg_am.ammarkpos != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ammarkpos);
SELECT    ctid, pg_am.amrestrpos
FROM    pg_am
WHERE    pg_am.amrestrpos != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrestrpos);
SELECT    ctid, pg_am.ambuild
FROM    pg_am
WHERE    pg_am.ambuild != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambuild);
SELECT    ctid, pg_am.ambulkdelete
FROM    pg_am
WHERE    pg_am.ambulkdelete != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambulkdelete);
SELECT    ctid, pg_am.amcostestimate
FROM    pg_am
WHERE    pg_am.amcostestimate != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amcostestimate);
SELECT    ctid, pg_amop.amopclaid
FROM    pg_amop
WHERE    pg_amop.amopclaid != 0 AND
    NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amop.amopclaid);
SELECT    ctid, pg_amop.amopopr
FROM    pg_amop
WHERE    pg_amop.amopopr != 0 AND
    NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_amop.amopopr);
SELECT    ctid, pg_amproc.amopclaid
FROM    pg_amproc
WHERE    pg_amproc.amopclaid != 0 AND
    NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amproc.amopclaid);
SELECT    ctid, pg_amproc.amproc
FROM    pg_amproc
WHERE    pg_amproc.amproc != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_amproc.amproc);
SELECT    ctid, pg_attribute.attrelid
FROM    pg_attribute
WHERE    pg_attribute.attrelid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_attribute.attrelid);
SELECT    ctid, pg_attribute.atttypid
FROM    pg_attribute
WHERE    pg_attribute.atttypid != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_attribute.atttypid);
SELECT    ctid, pg_cast.castsource
FROM    pg_cast
WHERE    pg_cast.castsource != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_cast.castsource);
SELECT    ctid, pg_cast.casttarget
FROM    pg_cast
WHERE    pg_cast.casttarget != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_cast.casttarget);
SELECT    ctid, pg_cast.castfunc
FROM    pg_cast
WHERE    pg_cast.castfunc != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_cast.castfunc);
SELECT    ctid, pg_class.relnamespace
FROM    pg_class
WHERE    pg_class.relnamespace != 0 AND
    NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_class.relnamespace);
SELECT    ctid, pg_class.reltype
FROM    pg_class
WHERE    pg_class.reltype != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_class.reltype);
SELECT    ctid, pg_class.relam
FROM    pg_class
WHERE    pg_class.relam != 0 AND
    NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_class.relam);
SELECT    ctid, pg_class.relfilenode
FROM    pg_class
WHERE    pg_class.relfilenode != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.relfilenode);
SELECT    ctid, pg_class.reltoastrelid
FROM    pg_class
WHERE    pg_class.reltoastrelid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastrelid);
SELECT    ctid, pg_class.reltoastidxid
FROM    pg_class
WHERE    pg_class.reltoastidxid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastidxid);
SELECT    ctid, pg_conversion.connamespace
FROM    pg_conversion
WHERE    pg_conversion.connamespace != 0 AND
    NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_conversion.connamespace);
SELECT    ctid, pg_conversion.conproc
FROM    pg_conversion
WHERE    pg_conversion.conproc != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_conversion.conproc);
SELECT    ctid, pg_database.datlastsysoid
FROM    pg_database
WHERE    pg_database.datlastsysoid != 0 AND
    NOT EXISTS(SELECT * FROM pg_conversion AS t1 WHERE t1.oid = pg_database.datlastsysoid);
SELECT    ctid, pg_depend.classid
FROM    pg_depend
WHERE    pg_depend.classid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_depend.classid);
SELECT    ctid, pg_depend.refclassid
FROM    pg_depend
WHERE    pg_depend.refclassid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_depend.refclassid);
SELECT    ctid, pg_description.classoid
FROM    pg_description
WHERE    pg_description.classoid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_description.classoid);
SELECT    ctid, pg_index.indexrelid
FROM    pg_index
WHERE    pg_index.indexrelid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indexrelid);
SELECT    ctid, pg_index.indrelid
FROM    pg_index
WHERE    pg_index.indrelid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indrelid);
SELECT    ctid, pg_language.lanvalidator
FROM    pg_language
WHERE    pg_language.lanvalidator != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_language.lanvalidator);
SELECT    ctid, pg_opclass.opcamid
FROM    pg_opclass
WHERE    pg_opclass.opcamid != 0 AND
    NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_opclass.opcamid);
SELECT    ctid, pg_opclass.opcnamespace
FROM    pg_opclass
WHERE    pg_opclass.opcnamespace != 0 AND
    NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_opclass.opcnamespace);
SELECT    ctid, pg_opclass.opcintype
FROM    pg_opclass
WHERE    pg_opclass.opcintype != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcintype);
SELECT    ctid, pg_operator.oprnamespace
FROM    pg_operator
WHERE    pg_operator.oprnamespace != 0 AND
    NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_operator.oprnamespace);
SELECT    ctid, pg_operator.oprleft
FROM    pg_operator
WHERE    pg_operator.oprleft != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprleft);
SELECT    ctid, pg_operator.oprright
FROM    pg_operator
WHERE    pg_operator.oprright != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprright);
SELECT    ctid, pg_operator.oprresult
FROM    pg_operator
WHERE    pg_operator.oprresult != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprresult);
SELECT    ctid, pg_operator.oprcom
FROM    pg_operator
WHERE    pg_operator.oprcom != 0 AND
    NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprcom);
SELECT    ctid, pg_operator.oprnegate
FROM    pg_operator
WHERE    pg_operator.oprnegate != 0 AND
    NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprnegate);
SELECT    ctid, pg_operator.oprlsortop
FROM    pg_operator
WHERE    pg_operator.oprlsortop != 0 AND
    NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprlsortop);
SELECT    ctid, pg_operator.oprrsortop
FROM    pg_operator
WHERE    pg_operator.oprrsortop != 0 AND
    NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprrsortop);
SELECT    ctid, pg_operator.oprltcmpop
FROM    pg_operator
WHERE    pg_operator.oprltcmpop != 0 AND
    NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprltcmpop);
SELECT    ctid, pg_operator.oprgtcmpop
FROM    pg_operator
WHERE    pg_operator.oprgtcmpop != 0 AND
    NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprgtcmpop);
SELECT    ctid, pg_operator.oprcode
FROM    pg_operator
WHERE    pg_operator.oprcode != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprcode);
SELECT    ctid, pg_operator.oprrest
FROM    pg_operator
WHERE    pg_operator.oprrest != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprrest);
SELECT    ctid, pg_operator.oprjoin
FROM    pg_operator
WHERE    pg_operator.oprjoin != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprjoin);
SELECT    ctid, pg_proc.pronamespace
FROM    pg_proc
WHERE    pg_proc.pronamespace != 0 AND
    NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_proc.pronamespace);
SELECT    ctid, pg_proc.prolang
FROM    pg_proc
WHERE    pg_proc.prolang != 0 AND
    NOT EXISTS(SELECT * FROM pg_language AS t1 WHERE t1.oid = pg_proc.prolang);
SELECT    ctid, pg_proc.prorettype
FROM    pg_proc
WHERE    pg_proc.prorettype != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_proc.prorettype);
SELECT    ctid, pg_rewrite.ev_class
FROM    pg_rewrite
WHERE    pg_rewrite.ev_class != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_rewrite.ev_class);
SELECT    ctid, pg_trigger.tgrelid
FROM    pg_trigger
WHERE    pg_trigger.tgrelid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_trigger.tgrelid);
SELECT    ctid, pg_trigger.tgfoid
FROM    pg_trigger
WHERE    pg_trigger.tgfoid != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_trigger.tgfoid);
SELECT    ctid, pg_type.typnamespace
FROM    pg_type
WHERE    pg_type.typnamespace != 0 AND
    NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_type.typnamespace);
SELECT    ctid, pg_type.typrelid
FROM    pg_type
WHERE    pg_type.typrelid != 0 AND
    NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_type.typrelid);
SELECT    ctid, pg_type.typelem
FROM    pg_type
WHERE    pg_type.typelem != 0 AND
    NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_type.typelem);
SELECT    ctid, pg_type.typinput
FROM    pg_type
WHERE    pg_type.typinput != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typinput);
SELECT    ctid, pg_type.typoutput
FROM    pg_type
WHERE    pg_type.typoutput != 0 AND
    NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typoutput);

Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)

From

Bruce Momjian

Date:

11 September 2002, 01:33:06

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Joe Conway wrote:
> Tom Lane wrote:
> > I'd be inclined to reproduce the original behavior.  findoidjoins is
> > pretty slow already, and I don't much want to slow it down more in order
> > to provide info that's useless for the primary purpose.
>
> Here's take two. It produces results similar to the previous version,
> but using libpq and schema aware queries.
>
>
> > use it for checking the stuff in pg_catalog.  So I'm not at all sure why
> > I made that note.  Do you get a plausible set of joins out of your
> > version?
>
> Looks reasonable to me. I attached the outputs of findoidjoins and
> make_oidjoins_check for review as well.
>
> Please review and commit, or kick back to me if more work is needed.
>
> Thanks,
>
> Joe
>

> Index: contrib/findoidjoins/Makefile
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/Makefile,v
> retrieving revision 1.13
> diff -c -r1.13 Makefile
> *** contrib/findoidjoins/Makefile    6 Sep 2001 10:49:29 -0000    1.13
> --- contrib/findoidjoins/Makefile    4 Sep 2002 23:36:27 -0000
> ***************
> *** 1,5 ****
> - # $Header: /opt/src/cvs/pgsql-server/contrib/findoidjoins/Makefile,v 1.13 2001/09/06 10:49:29 petere Exp $
> -
>   subdir = contrib/findoidjoins
>   top_builddir = ../..
>   include $(top_builddir)/src/Makefile.global
> --- 1,3 ----
> ***************
> *** 7,17 ****
>   PROGRAM = findoidjoins
>   OBJS    = findoidjoins.o
>
> ! libpgeasy_srcdir = $(top_srcdir)/src/interfaces/libpgeasy
> ! libpgeasy_builddir = $(top_builddir)/src/interfaces/libpgeasy
> !
> ! PG_CPPFLAGS = -I$(libpgeasy_srcdir) -I$(libpq_srcdir)
> ! PG_LIBS = -L$(libpgeasy_builddir) -lpgeasy $(libpq)
>
>   SCRIPTS = make_oidjoins_check
>   DOCS = README.findoidjoins
> --- 5,12 ----
>   PROGRAM = findoidjoins
>   OBJS    = findoidjoins.o
>
> ! PG_CPPFLAGS = -I$(libpq_srcdir)
> ! PG_LIBS = $(libpq)
>
>   SCRIPTS = make_oidjoins_check
>   DOCS = README.findoidjoins
> Index: contrib/findoidjoins/README.findoidjoins
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/README.findoidjoins,v
> retrieving revision 1.5
> diff -c -r1.5 README.findoidjoins
> *** contrib/findoidjoins/README.findoidjoins    25 Apr 2002 02:56:55 -0000    1.5
> --- contrib/findoidjoins/README.findoidjoins    5 Sep 2002 04:42:21 -0000
> ***************
> *** 1,24 ****
>
>                        findoidjoins
>
> ! This program scans a database, and prints oid fields (also regproc, regclass
> ! and regtype fields) and the tables they join to.  CAUTION: it is ver-r-r-y
> ! slow on a large database, or even a not-so-large one.  We don't really
> ! recommend running it on anything but an empty database, such as template1.
> !
> ! Uses pgeasy library.
>
>   Run on an empty database, it returns the system join relationships (shown
> ! below for 7.2).  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 7.2, the *only* field that should join to more
> ! than one target is pg_description.objoid.  (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 REGPROC 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"
> --- 1,22 ----
>
>                        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.
>
>   Run on an empty database, it returns the system join relationships (shown
> ! below for 7.3).  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 7.3, the *only* fields that should join to more
> ! than one target are pg_description.objoid, pg_depend.objid, and
> ! pg_depend.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* columns 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"
> ***************
> *** 27,43 ****
>   (Ideally we'd just regenerate the script as part of the regression
>   tests themselves, but that seems too slow...)
>
> ! NOTE: in 7.2, make_oidjoins_check produces one bogus join check, for
>   pg_class.relfilenode => pg_class.oid.  This is an artifact and should not
>   be added to the oidjoins regress test.
>
>   ---------------------------------------------------------------------------
> !
>   Join pg_aggregate.aggtransfn => pg_proc.oid
>   Join pg_aggregate.aggfinalfn => pg_proc.oid
> - Join pg_aggregate.aggbasetype => pg_type.oid
>   Join pg_aggregate.aggtranstype => pg_type.oid
> - Join pg_aggregate.aggfinaltype => pg_type.oid
>   Join pg_am.amgettuple => pg_proc.oid
>   Join pg_am.aminsert => pg_proc.oid
>   Join pg_am.ambeginscan => pg_proc.oid
> --- 25,39 ----
>   (Ideally we'd just regenerate the script as part of the regression
>   tests themselves, but that seems too slow...)
>
> ! NOTE: in 7.3, make_oidjoins_check produces one bogus join check, for
>   pg_class.relfilenode => pg_class.oid.  This is an artifact and should not
>   be added to the oidjoins regress test.
>
>   ---------------------------------------------------------------------------
> ! Join pg_aggregate.aggfnoid => pg_proc.oid
>   Join pg_aggregate.aggtransfn => pg_proc.oid
>   Join pg_aggregate.aggfinalfn => pg_proc.oid
>   Join pg_aggregate.aggtranstype => pg_type.oid
>   Join pg_am.amgettuple => pg_proc.oid
>   Join pg_am.aminsert => pg_proc.oid
>   Join pg_am.ambeginscan => pg_proc.oid
> ***************
> *** 54,68 ****
> --- 50,95 ----
>   Join pg_amproc.amproc => pg_proc.oid
>   Join pg_attribute.attrelid => pg_class.oid
>   Join pg_attribute.atttypid => pg_type.oid
> + Join pg_cast.castsource => pg_type.oid
> + Join pg_cast.casttarget => pg_type.oid
> + Join pg_cast.castfunc => pg_proc.oid
> + Join pg_class.relnamespace => pg_namespace.oid
>   Join pg_class.reltype => pg_type.oid
>   Join pg_class.relam => pg_am.oid
> + Join pg_class.relfilenode => pg_class.oid
>   Join pg_class.reltoastrelid => pg_class.oid
>   Join pg_class.reltoastidxid => pg_class.oid
> + Join pg_conversion.connamespace => pg_namespace.oid
> + Join pg_conversion.conproc => pg_proc.oid
> + Join pg_database.datlastsysoid => pg_conversion.oid
> + Join pg_depend.classid => pg_class.oid
> + Join pg_depend.objid => pg_conversion.oid
> + Join pg_depend.objid => pg_rewrite.oid
> + Join pg_depend.objid => pg_type.oid
> + Join pg_depend.refclassid => pg_class.oid
> + Join pg_depend.refobjid => pg_cast.oid
> + Join pg_depend.refobjid => pg_class.oid
> + Join pg_depend.refobjid => pg_language.oid
> + Join pg_depend.refobjid => pg_namespace.oid
> + Join pg_depend.refobjid => pg_opclass.oid
> + Join pg_depend.refobjid => pg_operator.oid
> + Join pg_depend.refobjid => pg_proc.oid
> + Join pg_depend.refobjid => pg_trigger.oid
> + Join pg_depend.refobjid => pg_type.oid
> + Join pg_description.objoid => pg_am.oid
> + Join pg_description.objoid => pg_database.oid
> + Join pg_description.objoid => pg_language.oid
> + Join pg_description.objoid => pg_namespace.oid
> + Join pg_description.objoid => pg_proc.oid
> + Join pg_description.objoid => pg_type.oid
>   Join pg_description.classoid => pg_class.oid
>   Join pg_index.indexrelid => pg_class.oid
>   Join pg_index.indrelid => pg_class.oid
> + Join pg_language.lanvalidator => pg_proc.oid
>   Join pg_opclass.opcamid => pg_am.oid
> + Join pg_opclass.opcnamespace => pg_namespace.oid
>   Join pg_opclass.opcintype => pg_type.oid
> + Join pg_operator.oprnamespace => pg_namespace.oid
>   Join pg_operator.oprleft => pg_type.oid
>   Join pg_operator.oprright => pg_type.oid
>   Join pg_operator.oprresult => pg_type.oid
> ***************
> *** 70,94 ****
>   Join pg_operator.oprnegate => pg_operator.oid
>   Join pg_operator.oprlsortop => pg_operator.oid
>   Join pg_operator.oprrsortop => pg_operator.oid
>   Join pg_operator.oprcode => pg_proc.oid
>   Join pg_operator.oprrest => pg_proc.oid
>   Join pg_operator.oprjoin => pg_proc.oid
>   Join pg_proc.prolang => pg_language.oid
>   Join pg_proc.prorettype => pg_type.oid
>   Join pg_rewrite.ev_class => pg_class.oid
> - Join pg_statistic.starelid => pg_class.oid
> - Join pg_statistic.staop1 => pg_operator.oid
> - Join pg_statistic.staop2 => pg_operator.oid
> - Join pg_statistic.staop3 => pg_operator.oid
>   Join pg_trigger.tgrelid => pg_class.oid
>   Join pg_trigger.tgfoid => pg_proc.oid
>   Join pg_type.typrelid => pg_class.oid
>   Join pg_type.typelem => pg_type.oid
>   Join pg_type.typinput => pg_proc.oid
>   Join pg_type.typoutput => pg_proc.oid
> - Join pg_type.typreceive => pg_proc.oid
> - Join pg_type.typsend => pg_proc.oid
> -
>   ---------------------------------------------------------------------------
>
>   Bruce Momjian (root@candle.pha.pa.us)
> --- 97,119 ----
>   Join pg_operator.oprnegate => pg_operator.oid
>   Join pg_operator.oprlsortop => pg_operator.oid
>   Join pg_operator.oprrsortop => pg_operator.oid
> + Join pg_operator.oprltcmpop => pg_operator.oid
> + Join pg_operator.oprgtcmpop => pg_operator.oid
>   Join pg_operator.oprcode => pg_proc.oid
>   Join pg_operator.oprrest => pg_proc.oid
>   Join pg_operator.oprjoin => pg_proc.oid
> + Join pg_proc.pronamespace => pg_namespace.oid
>   Join pg_proc.prolang => pg_language.oid
>   Join pg_proc.prorettype => pg_type.oid
>   Join pg_rewrite.ev_class => pg_class.oid
>   Join pg_trigger.tgrelid => pg_class.oid
>   Join pg_trigger.tgfoid => pg_proc.oid
> + Join pg_type.typnamespace => pg_namespace.oid
>   Join pg_type.typrelid => pg_class.oid
>   Join pg_type.typelem => pg_type.oid
>   Join pg_type.typinput => pg_proc.oid
>   Join pg_type.typoutput => pg_proc.oid
>   ---------------------------------------------------------------------------
>
>   Bruce Momjian (root@candle.pha.pa.us)
> + Updated for 7.3 by Joe Conway (mail@joeconway.com)
> Index: contrib/findoidjoins/findoidjoins.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/findoidjoins.c,v
> retrieving revision 1.17
> diff -c -r1.17 findoidjoins.c
> *** contrib/findoidjoins/findoidjoins.c    4 Sep 2002 20:31:06 -0000    1.17
> --- contrib/findoidjoins/findoidjoins.c    5 Sep 2002 04:51:16 -0000
> ***************
> *** 1,109 ****
>   /*
> !  * findoidjoins.c, requires src/interfaces/libpgeasy
>    *
>    */
> - #include "postgres_fe.h"
>
> ! #include "libpq-fe.h"
> ! #include "halt.h"
> ! #include "libpgeasy.h"
>
> ! PGresult   *attres,
> !            *relres;
>
>   int
>   main(int argc, char **argv)
>   {
> !     char        query[4000];
> !     char        relname[256];
> !     char        relname2[256];
> !     char        attname[256];
> !     char        typname[256];
> !     int            count;
> !     char        optstr[256];
>
>       if (argc != 2)
> !         halt("Usage:  %s database\n", argv[0]);
>
> !     snprintf(optstr, 256, "dbname=%s", argv[1]);
> !     connectdb(optstr);
>
> !     on_error_continue();
> !     on_error_stop();
>
> !     doquery("BEGIN WORK");
> !     doquery("\
> !         DECLARE c_attributes BINARY CURSOR FOR \
> !         SELECT typname, relname, a.attname \
> !         FROM pg_class c, pg_attribute a, pg_type t \
> !         WHERE a.attnum > 0 AND \
> !               relkind = 'r' AND \
> !               (typname = 'oid' OR \
> !                typname = 'regproc' OR \
> !                typname = 'regclass' OR \
> !                typname = 'regtype') AND \
> !               a.attrelid = c.oid AND \
> !               a.atttypid = t.oid \
> !         ORDER BY 2, a.attnum ; \
> !         ");
> !     doquery("FETCH ALL IN c_attributes");
> !     attres = get_result();
> !
> !     doquery("\
> !         DECLARE c_relations BINARY CURSOR FOR \
> !         SELECT relname \
> !         FROM pg_class c \
> !         WHERE relkind = 'r' AND relhasoids \
> !         ORDER BY 1; \
> !         ");
> !     doquery("FETCH ALL IN c_relations");
> !     relres = get_result();
>
> !     set_result(attres);
> !     while (fetch(typname, relname, attname) != END_OF_TUPLES)
>       {
> !         set_result(relres);
> !         reset_fetch();
> !         while (fetch(relname2) != END_OF_TUPLES)
> !         {
> !             unset_result(relres);
> !             if (strcmp(typname, "oid") == 0)
> !                 snprintf(query, 4000, "\
> !                     DECLARE c_matches BINARY CURSOR FOR \
> !                     SELECT    count(*)::int4 \
> !                         FROM \"%s\" t1, \"%s\" t2 \
> !                     WHERE t1.\"%s\" = t2.oid ",
> !                          relname, relname2, attname);
> !             else
> !                 sprintf(query, 4000, "\
> !                     DECLARE c_matches BINARY CURSOR FOR \
> !                     SELECT    count(*)::int4 \
> !                         FROM \"%s\" t1, \"%s\" t2 \
> !                     WHERE t1.\"%s\"::oid = t2.oid ",
> !                         relname, relname2, attname);
> !
> !             doquery(query);
> !             doquery("FETCH ALL IN c_matches");
> !             fetch(&count);
> !             if (count != 0)
> !                 printf("Join %s.%s => %s.oid\n", relname, attname, relname2);
> !             doquery("CLOSE c_matches");
> !             set_result(relres);
> !         }
> !         set_result(attres);
>       }
>
> !     set_result(relres);
> !     doquery("CLOSE c_relations");
> !     PQclear(relres);
> !
> !     set_result(attres);
> !     doquery("CLOSE c_attributes");
> !     PQclear(attres);
> !     unset_result(attres);
>
> !     doquery("COMMIT WORK");
>
> !     disconnectdb();
> !     return 0;
>   }
> --- 1,152 ----
>   /*
> !  * findoidjoins
> !  *
> !  * Copyright 2002 by PostgreSQL Global Development Group
> !  *
> !  * Permission to use, copy, modify, and distribute this software and its
> !  * documentation for any purpose, without fee, and without a written agreement
> !  * is hereby granted, provided that the above copyright notice and this
> !  * paragraph and the following two paragraphs appear in all copies.
> !  *
> !  * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> !  * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> !  * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> !  * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> !  * POSSIBILITY OF SUCH DAMAGE.
> !  *
> !  * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> !  * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> !  * AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS
> !  * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> !  * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
>    *
>    */
>
> ! #include <stdlib.h>
>
> ! #include "postgres_fe.h"
> ! #include "libpq-fe.h"
> ! #include "pqexpbuffer.h"
>
>   int
>   main(int argc, char **argv)
>   {
> !     PGconn               *conn;
> !     PQExpBufferData        sql;
> !     PGresult           *res;
> !     PGresult           *pkrel_res;
> !     PGresult           *fkrel_res;
> !     char               *fk_relname;
> !     char               *fk_nspname;
> !     char               *fk_attname;
> !     char               *fk_typname;
> !     char               *pk_relname;
> !     char               *pk_nspname;
> !     int                    fk, pk;        /* loop counters */
>
>       if (argc != 2)
> !     {
> !         fprintf(stderr, "Usage:  %s database\n", argv[0]);
> !         exit(EXIT_FAILURE);
> !     }
>
> !     initPQExpBuffer(&sql);
> !     appendPQExpBuffer(&sql, "dbname=%s", argv[1]);
>
> !     conn = PQconnectdb(sql.data);
> !     if (PQstatus(conn) == CONNECTION_BAD)
> !     {
> !         fprintf(stderr, "connection error:  %s\n", PQerrorMessage(conn));
> !         exit(EXIT_FAILURE);
> !     }
> !
> !     termPQExpBuffer(&sql);
> !     initPQExpBuffer(&sql);
>
> !     appendPQExpBuffer(&sql, "%s",
> !         "SELECT c.relname, (SELECT nspname FROM "
> !         "pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
> !         "FROM pg_catalog.pg_class c "
> !         "WHERE c.relkind = 'r' "
> !         "AND c.relhasoids "
> !         "ORDER BY nspname, c.relname"
> !         );
>
> !     res = PQexec(conn, sql.data);
> !     if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
>       {
> !         fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
> !         exit(EXIT_FAILURE);
>       }
> +     pkrel_res = res;
> +
> +     termPQExpBuffer(&sql);
> +     initPQExpBuffer(&sql);
>
> !     appendPQExpBuffer(&sql, "%s",
> !         "SELECT c.relname, "
> !         "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
> !         "a.attname, "
> !         "t.typname "
> !         "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, pg_catalog.pg_type t "
> !         "WHERE a.attnum > 0 AND c.relkind = 'r' "
> !         "AND t.typnamespace IN (SELECT n.oid FROM pg_catalog.pg_namespace n WHERE nspname LIKE 'pg\\_%') "
> !         "AND (t.typname = 'oid' OR t.typname LIKE 'reg%') "
> !         "AND a.attrelid = c.oid "
> !         "AND a.atttypid = t.oid "
> !         "ORDER BY nspname, c.relname, a.attnum"
> !         );
>
> !     res = PQexec(conn, sql.data);
> !     if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
> !     {
> !         fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
> !         exit(EXIT_FAILURE);
> !     }
> !     fkrel_res = res;
> !
> !     termPQExpBuffer(&sql);
> !     initPQExpBuffer(&sql);
> !
> !     for (fk = 0; fk < PQntuples(fkrel_res); fk++)
> !     {
> !         fk_relname = PQgetvalue(fkrel_res, fk, 0);
> !         fk_nspname = PQgetvalue(fkrel_res, fk, 1);
> !         fk_attname = PQgetvalue(fkrel_res, fk, 2);
> !         fk_typname = PQgetvalue(fkrel_res, fk, 3);
> !
> !         for (pk = 0; pk < PQntuples(pkrel_res); pk++)
> !         {
> !             pk_relname = PQgetvalue(pkrel_res, pk, 0);
> !             pk_nspname = PQgetvalue(pkrel_res, pk, 1);
> !
> !             appendPQExpBuffer(&sql,
> !                 "SELECT    1 "
> !                 "FROM \"%s\".\"%s\" t1, "
> !                 "\"%s\".\"%s\" t2 "
> !                 "WHERE t1.\"%s\"::oid = t2.oid",
> !                 fk_nspname, fk_relname, pk_nspname, pk_relname, fk_attname);
> !
> !             res = PQexec(conn, sql.data);
> !             if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
> !             {
> !                 fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
> !                 exit(EXIT_FAILURE);
> !             }
> !
> !             if (PQntuples(res) != 0)
> !                 printf("Join %s.%s => %s.oid\n",
> !                         fk_relname, fk_attname, pk_relname);
> !
> !             PQclear(res);
> !
> !             termPQExpBuffer(&sql);
> !             initPQExpBuffer(&sql);
> !         }
> !     }
> !     PQclear(pkrel_res);
> !     PQclear(fkrel_res);
> !     PQfinish(conn);
>
> !     exit(EXIT_SUCCESS);
>   }

> Join pg_aggregate.aggfnoid => pg_proc.oid
> Join pg_aggregate.aggtransfn => pg_proc.oid
> Join pg_aggregate.aggfinalfn => pg_proc.oid
> Join pg_aggregate.aggtranstype => pg_type.oid
> Join pg_am.amgettuple => pg_proc.oid
> Join pg_am.aminsert => pg_proc.oid
> Join pg_am.ambeginscan => pg_proc.oid
> Join pg_am.amrescan => pg_proc.oid
> Join pg_am.amendscan => pg_proc.oid
> Join pg_am.ammarkpos => pg_proc.oid
> Join pg_am.amrestrpos => pg_proc.oid
> Join pg_am.ambuild => pg_proc.oid
> Join pg_am.ambulkdelete => pg_proc.oid
> Join pg_am.amcostestimate => pg_proc.oid
> Join pg_amop.amopclaid => pg_opclass.oid
> Join pg_amop.amopopr => pg_operator.oid
> Join pg_amproc.amopclaid => pg_opclass.oid
> Join pg_amproc.amproc => pg_proc.oid
> Join pg_attribute.attrelid => pg_class.oid
> Join pg_attribute.atttypid => pg_type.oid
> Join pg_cast.castsource => pg_type.oid
> Join pg_cast.casttarget => pg_type.oid
> Join pg_cast.castfunc => pg_proc.oid
> Join pg_class.relnamespace => pg_namespace.oid
> Join pg_class.reltype => pg_type.oid
> Join pg_class.relam => pg_am.oid
> Join pg_class.relfilenode => pg_class.oid
> Join pg_class.reltoastrelid => pg_class.oid
> Join pg_class.reltoastidxid => pg_class.oid
> Join pg_conversion.connamespace => pg_namespace.oid
> Join pg_conversion.conproc => pg_proc.oid
> Join pg_database.datlastsysoid => pg_conversion.oid
> Join pg_depend.classid => pg_class.oid
> Join pg_depend.objid => pg_conversion.oid
> Join pg_depend.objid => pg_rewrite.oid
> Join pg_depend.objid => pg_type.oid
> Join pg_depend.refclassid => pg_class.oid
> Join pg_depend.refobjid => pg_cast.oid
> Join pg_depend.refobjid => pg_class.oid
> Join pg_depend.refobjid => pg_language.oid
> Join pg_depend.refobjid => pg_namespace.oid
> Join pg_depend.refobjid => pg_opclass.oid
> Join pg_depend.refobjid => pg_operator.oid
> Join pg_depend.refobjid => pg_proc.oid
> Join pg_depend.refobjid => pg_trigger.oid
> Join pg_depend.refobjid => pg_type.oid
> Join pg_description.objoid => pg_am.oid
> Join pg_description.objoid => pg_database.oid
> Join pg_description.objoid => pg_language.oid
> Join pg_description.objoid => pg_namespace.oid
> Join pg_description.objoid => pg_proc.oid
> Join pg_description.objoid => pg_type.oid
> Join pg_description.classoid => pg_class.oid
> Join pg_index.indexrelid => pg_class.oid
> Join pg_index.indrelid => pg_class.oid
> Join pg_language.lanvalidator => pg_proc.oid
> Join pg_opclass.opcamid => pg_am.oid
> Join pg_opclass.opcnamespace => pg_namespace.oid
> Join pg_opclass.opcintype => pg_type.oid
> Join pg_operator.oprnamespace => pg_namespace.oid
> Join pg_operator.oprleft => pg_type.oid
> Join pg_operator.oprright => pg_type.oid
> Join pg_operator.oprresult => pg_type.oid
> Join pg_operator.oprcom => pg_operator.oid
> Join pg_operator.oprnegate => pg_operator.oid
> Join pg_operator.oprlsortop => pg_operator.oid
> Join pg_operator.oprrsortop => pg_operator.oid
> Join pg_operator.oprltcmpop => pg_operator.oid
> Join pg_operator.oprgtcmpop => pg_operator.oid
> Join pg_operator.oprcode => pg_proc.oid
> Join pg_operator.oprrest => pg_proc.oid
> Join pg_operator.oprjoin => pg_proc.oid
> Join pg_proc.pronamespace => pg_namespace.oid
> Join pg_proc.prolang => pg_language.oid
> Join pg_proc.prorettype => pg_type.oid
> Join pg_rewrite.ev_class => pg_class.oid
> Join pg_trigger.tgrelid => pg_class.oid
> Join pg_trigger.tgfoid => pg_proc.oid
> Join pg_type.typnamespace => pg_namespace.oid
> Join pg_type.typrelid => pg_class.oid
> Join pg_type.typelem => pg_type.oid
> Join pg_type.typinput => pg_proc.oid
> Join pg_type.typoutput => pg_proc.oid

> --
> -- This is created by pgsql/contrib/findoidjoins/make_oidjoin_check
> --
> SELECT    ctid, pg_aggregate.aggfnoid
> FROM    pg_aggregate
> WHERE    pg_aggregate.aggfnoid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfnoid);
> SELECT    ctid, pg_aggregate.aggtransfn
> FROM    pg_aggregate
> WHERE    pg_aggregate.aggtransfn != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggtransfn);
> SELECT    ctid, pg_aggregate.aggfinalfn
> FROM    pg_aggregate
> WHERE    pg_aggregate.aggfinalfn != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfinalfn);
> SELECT    ctid, pg_aggregate.aggtranstype
> FROM    pg_aggregate
> WHERE    pg_aggregate.aggtranstype != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggtranstype);
> SELECT    ctid, pg_am.amgettuple
> FROM    pg_am
> WHERE    pg_am.amgettuple != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amgettuple);
> SELECT    ctid, pg_am.aminsert
> FROM    pg_am
> WHERE    pg_am.aminsert != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.aminsert);
> SELECT    ctid, pg_am.ambeginscan
> FROM    pg_am
> WHERE    pg_am.ambeginscan != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambeginscan);
> SELECT    ctid, pg_am.amrescan
> FROM    pg_am
> WHERE    pg_am.amrescan != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrescan);
> SELECT    ctid, pg_am.amendscan
> FROM    pg_am
> WHERE    pg_am.amendscan != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amendscan);
> SELECT    ctid, pg_am.ammarkpos
> FROM    pg_am
> WHERE    pg_am.ammarkpos != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ammarkpos);
> SELECT    ctid, pg_am.amrestrpos
> FROM    pg_am
> WHERE    pg_am.amrestrpos != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrestrpos);
> SELECT    ctid, pg_am.ambuild
> FROM    pg_am
> WHERE    pg_am.ambuild != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambuild);
> SELECT    ctid, pg_am.ambulkdelete
> FROM    pg_am
> WHERE    pg_am.ambulkdelete != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambulkdelete);
> SELECT    ctid, pg_am.amcostestimate
> FROM    pg_am
> WHERE    pg_am.amcostestimate != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amcostestimate);
> SELECT    ctid, pg_amop.amopclaid
> FROM    pg_amop
> WHERE    pg_amop.amopclaid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amop.amopclaid);
> SELECT    ctid, pg_amop.amopopr
> FROM    pg_amop
> WHERE    pg_amop.amopopr != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_amop.amopopr);
> SELECT    ctid, pg_amproc.amopclaid
> FROM    pg_amproc
> WHERE    pg_amproc.amopclaid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amproc.amopclaid);
> SELECT    ctid, pg_amproc.amproc
> FROM    pg_amproc
> WHERE    pg_amproc.amproc != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_amproc.amproc);
> SELECT    ctid, pg_attribute.attrelid
> FROM    pg_attribute
> WHERE    pg_attribute.attrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_attribute.attrelid);
> SELECT    ctid, pg_attribute.atttypid
> FROM    pg_attribute
> WHERE    pg_attribute.atttypid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_attribute.atttypid);
> SELECT    ctid, pg_cast.castsource
> FROM    pg_cast
> WHERE    pg_cast.castsource != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_cast.castsource);
> SELECT    ctid, pg_cast.casttarget
> FROM    pg_cast
> WHERE    pg_cast.casttarget != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_cast.casttarget);
> SELECT    ctid, pg_cast.castfunc
> FROM    pg_cast
> WHERE    pg_cast.castfunc != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_cast.castfunc);
> SELECT    ctid, pg_class.relnamespace
> FROM    pg_class
> WHERE    pg_class.relnamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_class.relnamespace);
> SELECT    ctid, pg_class.reltype
> FROM    pg_class
> WHERE    pg_class.reltype != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_class.reltype);
> SELECT    ctid, pg_class.relam
> FROM    pg_class
> WHERE    pg_class.relam != 0 AND
>     NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_class.relam);
> SELECT    ctid, pg_class.relfilenode
> FROM    pg_class
> WHERE    pg_class.relfilenode != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.relfilenode);
> SELECT    ctid, pg_class.reltoastrelid
> FROM    pg_class
> WHERE    pg_class.reltoastrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastrelid);
> SELECT    ctid, pg_class.reltoastidxid
> FROM    pg_class
> WHERE    pg_class.reltoastidxid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastidxid);
> SELECT    ctid, pg_conversion.connamespace
> FROM    pg_conversion
> WHERE    pg_conversion.connamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_conversion.connamespace);
> SELECT    ctid, pg_conversion.conproc
> FROM    pg_conversion
> WHERE    pg_conversion.conproc != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_conversion.conproc);
> SELECT    ctid, pg_database.datlastsysoid
> FROM    pg_database
> WHERE    pg_database.datlastsysoid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_conversion AS t1 WHERE t1.oid = pg_database.datlastsysoid);
> SELECT    ctid, pg_depend.classid
> FROM    pg_depend
> WHERE    pg_depend.classid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_depend.classid);
> SELECT    ctid, pg_depend.refclassid
> FROM    pg_depend
> WHERE    pg_depend.refclassid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_depend.refclassid);
> SELECT    ctid, pg_description.classoid
> FROM    pg_description
> WHERE    pg_description.classoid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_description.classoid);
> SELECT    ctid, pg_index.indexrelid
> FROM    pg_index
> WHERE    pg_index.indexrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indexrelid);
> SELECT    ctid, pg_index.indrelid
> FROM    pg_index
> WHERE    pg_index.indrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indrelid);
> SELECT    ctid, pg_language.lanvalidator
> FROM    pg_language
> WHERE    pg_language.lanvalidator != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_language.lanvalidator);
> SELECT    ctid, pg_opclass.opcamid
> FROM    pg_opclass
> WHERE    pg_opclass.opcamid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_opclass.opcamid);
> SELECT    ctid, pg_opclass.opcnamespace
> FROM    pg_opclass
> WHERE    pg_opclass.opcnamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_opclass.opcnamespace);
> SELECT    ctid, pg_opclass.opcintype
> FROM    pg_opclass
> WHERE    pg_opclass.opcintype != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcintype);
> SELECT    ctid, pg_operator.oprnamespace
> FROM    pg_operator
> WHERE    pg_operator.oprnamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_operator.oprnamespace);
> SELECT    ctid, pg_operator.oprleft
> FROM    pg_operator
> WHERE    pg_operator.oprleft != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprleft);
> SELECT    ctid, pg_operator.oprright
> FROM    pg_operator
> WHERE    pg_operator.oprright != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprright);
> SELECT    ctid, pg_operator.oprresult
> FROM    pg_operator
> WHERE    pg_operator.oprresult != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprresult);
> SELECT    ctid, pg_operator.oprcom
> FROM    pg_operator
> WHERE    pg_operator.oprcom != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprcom);
> SELECT    ctid, pg_operator.oprnegate
> FROM    pg_operator
> WHERE    pg_operator.oprnegate != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprnegate);
> SELECT    ctid, pg_operator.oprlsortop
> FROM    pg_operator
> WHERE    pg_operator.oprlsortop != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprlsortop);
> SELECT    ctid, pg_operator.oprrsortop
> FROM    pg_operator
> WHERE    pg_operator.oprrsortop != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprrsortop);
> SELECT    ctid, pg_operator.oprltcmpop
> FROM    pg_operator
> WHERE    pg_operator.oprltcmpop != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprltcmpop);
> SELECT    ctid, pg_operator.oprgtcmpop
> FROM    pg_operator
> WHERE    pg_operator.oprgtcmpop != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprgtcmpop);
> SELECT    ctid, pg_operator.oprcode
> FROM    pg_operator
> WHERE    pg_operator.oprcode != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprcode);
> SELECT    ctid, pg_operator.oprrest
> FROM    pg_operator
> WHERE    pg_operator.oprrest != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprrest);
> SELECT    ctid, pg_operator.oprjoin
> FROM    pg_operator
> WHERE    pg_operator.oprjoin != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprjoin);
> SELECT    ctid, pg_proc.pronamespace
> FROM    pg_proc
> WHERE    pg_proc.pronamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_proc.pronamespace);
> SELECT    ctid, pg_proc.prolang
> FROM    pg_proc
> WHERE    pg_proc.prolang != 0 AND
>     NOT EXISTS(SELECT * FROM pg_language AS t1 WHERE t1.oid = pg_proc.prolang);
> SELECT    ctid, pg_proc.prorettype
> FROM    pg_proc
> WHERE    pg_proc.prorettype != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_proc.prorettype);
> SELECT    ctid, pg_rewrite.ev_class
> FROM    pg_rewrite
> WHERE    pg_rewrite.ev_class != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_rewrite.ev_class);
> SELECT    ctid, pg_trigger.tgrelid
> FROM    pg_trigger
> WHERE    pg_trigger.tgrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_trigger.tgrelid);
> SELECT    ctid, pg_trigger.tgfoid
> FROM    pg_trigger
> WHERE    pg_trigger.tgfoid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_trigger.tgfoid);
> SELECT    ctid, pg_type.typnamespace
> FROM    pg_type
> WHERE    pg_type.typnamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_type.typnamespace);
> SELECT    ctid, pg_type.typrelid
> FROM    pg_type
> WHERE    pg_type.typrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_type.typrelid);
> SELECT    ctid, pg_type.typelem
> FROM    pg_type
> WHERE    pg_type.typelem != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_type.typelem);
> SELECT    ctid, pg_type.typinput
> FROM    pg_type
> WHERE    pg_type.typinput != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typinput);
> SELECT    ctid, pg_type.typoutput
> FROM    pg_type
> WHERE    pg_type.typoutput != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typoutput);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)

From

Joe Conway

Date:

11 September 2002, 01:56:09

Bruce Momjian wrote:
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
>     http://candle.pha.pa.us/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.
>

I think I saw a commit message from Tom applying this already...yup:

http://developer.postgresql.org/cvsweb.cgi/pgsql-server/contrib/findoidjoins/findoidjoins.c

Joe

Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)

From

Bruce Momjian

Date:

11 September 2002, 01:57:57

Patch already applied by Tom.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > I'd be inclined to reproduce the original behavior.  findoidjoins is
> > pretty slow already, and I don't much want to slow it down more in order
> > to provide info that's useless for the primary purpose.
>
> Here's take two. It produces results similar to the previous version,
> but using libpq and schema aware queries.
>
>
> > use it for checking the stuff in pg_catalog.  So I'm not at all sure why
> > I made that note.  Do you get a plausible set of joins out of your
> > version?
>
> Looks reasonable to me. I attached the outputs of findoidjoins and
> make_oidjoins_check for review as well.
>
> Please review and commit, or kick back to me if more work is needed.
>
> Thanks,
>
> Joe
>

> Index: contrib/findoidjoins/Makefile
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/Makefile,v
> retrieving revision 1.13
> diff -c -r1.13 Makefile
> *** contrib/findoidjoins/Makefile    6 Sep 2001 10:49:29 -0000    1.13
> --- contrib/findoidjoins/Makefile    4 Sep 2002 23:36:27 -0000
> ***************
> *** 1,5 ****
> - # $Header: /opt/src/cvs/pgsql-server/contrib/findoidjoins/Makefile,v 1.13 2001/09/06 10:49:29 petere Exp $
> -
>   subdir = contrib/findoidjoins
>   top_builddir = ../..
>   include $(top_builddir)/src/Makefile.global
> --- 1,3 ----
> ***************
> *** 7,17 ****
>   PROGRAM = findoidjoins
>   OBJS    = findoidjoins.o
>
> ! libpgeasy_srcdir = $(top_srcdir)/src/interfaces/libpgeasy
> ! libpgeasy_builddir = $(top_builddir)/src/interfaces/libpgeasy
> !
> ! PG_CPPFLAGS = -I$(libpgeasy_srcdir) -I$(libpq_srcdir)
> ! PG_LIBS = -L$(libpgeasy_builddir) -lpgeasy $(libpq)
>
>   SCRIPTS = make_oidjoins_check
>   DOCS = README.findoidjoins
> --- 5,12 ----
>   PROGRAM = findoidjoins
>   OBJS    = findoidjoins.o
>
> ! PG_CPPFLAGS = -I$(libpq_srcdir)
> ! PG_LIBS = $(libpq)
>
>   SCRIPTS = make_oidjoins_check
>   DOCS = README.findoidjoins
> Index: contrib/findoidjoins/README.findoidjoins
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/README.findoidjoins,v
> retrieving revision 1.5
> diff -c -r1.5 README.findoidjoins
> *** contrib/findoidjoins/README.findoidjoins    25 Apr 2002 02:56:55 -0000    1.5
> --- contrib/findoidjoins/README.findoidjoins    5 Sep 2002 04:42:21 -0000
> ***************
> *** 1,24 ****
>
>                        findoidjoins
>
> ! This program scans a database, and prints oid fields (also regproc, regclass
> ! and regtype fields) and the tables they join to.  CAUTION: it is ver-r-r-y
> ! slow on a large database, or even a not-so-large one.  We don't really
> ! recommend running it on anything but an empty database, such as template1.
> !
> ! Uses pgeasy library.
>
>   Run on an empty database, it returns the system join relationships (shown
> ! below for 7.2).  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 7.2, the *only* field that should join to more
> ! than one target is pg_description.objoid.  (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 REGPROC 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"
> --- 1,22 ----
>
>                        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.
>
>   Run on an empty database, it returns the system join relationships (shown
> ! below for 7.3).  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 7.3, the *only* fields that should join to more
> ! than one target are pg_description.objoid, pg_depend.objid, and
> ! pg_depend.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* columns 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"
> ***************
> *** 27,43 ****
>   (Ideally we'd just regenerate the script as part of the regression
>   tests themselves, but that seems too slow...)
>
> ! NOTE: in 7.2, make_oidjoins_check produces one bogus join check, for
>   pg_class.relfilenode => pg_class.oid.  This is an artifact and should not
>   be added to the oidjoins regress test.
>
>   ---------------------------------------------------------------------------
> !
>   Join pg_aggregate.aggtransfn => pg_proc.oid
>   Join pg_aggregate.aggfinalfn => pg_proc.oid
> - Join pg_aggregate.aggbasetype => pg_type.oid
>   Join pg_aggregate.aggtranstype => pg_type.oid
> - Join pg_aggregate.aggfinaltype => pg_type.oid
>   Join pg_am.amgettuple => pg_proc.oid
>   Join pg_am.aminsert => pg_proc.oid
>   Join pg_am.ambeginscan => pg_proc.oid
> --- 25,39 ----
>   (Ideally we'd just regenerate the script as part of the regression
>   tests themselves, but that seems too slow...)
>
> ! NOTE: in 7.3, make_oidjoins_check produces one bogus join check, for
>   pg_class.relfilenode => pg_class.oid.  This is an artifact and should not
>   be added to the oidjoins regress test.
>
>   ---------------------------------------------------------------------------
> ! Join pg_aggregate.aggfnoid => pg_proc.oid
>   Join pg_aggregate.aggtransfn => pg_proc.oid
>   Join pg_aggregate.aggfinalfn => pg_proc.oid
>   Join pg_aggregate.aggtranstype => pg_type.oid
>   Join pg_am.amgettuple => pg_proc.oid
>   Join pg_am.aminsert => pg_proc.oid
>   Join pg_am.ambeginscan => pg_proc.oid
> ***************
> *** 54,68 ****
> --- 50,95 ----
>   Join pg_amproc.amproc => pg_proc.oid
>   Join pg_attribute.attrelid => pg_class.oid
>   Join pg_attribute.atttypid => pg_type.oid
> + Join pg_cast.castsource => pg_type.oid
> + Join pg_cast.casttarget => pg_type.oid
> + Join pg_cast.castfunc => pg_proc.oid
> + Join pg_class.relnamespace => pg_namespace.oid
>   Join pg_class.reltype => pg_type.oid
>   Join pg_class.relam => pg_am.oid
> + Join pg_class.relfilenode => pg_class.oid
>   Join pg_class.reltoastrelid => pg_class.oid
>   Join pg_class.reltoastidxid => pg_class.oid
> + Join pg_conversion.connamespace => pg_namespace.oid
> + Join pg_conversion.conproc => pg_proc.oid
> + Join pg_database.datlastsysoid => pg_conversion.oid
> + Join pg_depend.classid => pg_class.oid
> + Join pg_depend.objid => pg_conversion.oid
> + Join pg_depend.objid => pg_rewrite.oid
> + Join pg_depend.objid => pg_type.oid
> + Join pg_depend.refclassid => pg_class.oid
> + Join pg_depend.refobjid => pg_cast.oid
> + Join pg_depend.refobjid => pg_class.oid
> + Join pg_depend.refobjid => pg_language.oid
> + Join pg_depend.refobjid => pg_namespace.oid
> + Join pg_depend.refobjid => pg_opclass.oid
> + Join pg_depend.refobjid => pg_operator.oid
> + Join pg_depend.refobjid => pg_proc.oid
> + Join pg_depend.refobjid => pg_trigger.oid
> + Join pg_depend.refobjid => pg_type.oid
> + Join pg_description.objoid => pg_am.oid
> + Join pg_description.objoid => pg_database.oid
> + Join pg_description.objoid => pg_language.oid
> + Join pg_description.objoid => pg_namespace.oid
> + Join pg_description.objoid => pg_proc.oid
> + Join pg_description.objoid => pg_type.oid
>   Join pg_description.classoid => pg_class.oid
>   Join pg_index.indexrelid => pg_class.oid
>   Join pg_index.indrelid => pg_class.oid
> + Join pg_language.lanvalidator => pg_proc.oid
>   Join pg_opclass.opcamid => pg_am.oid
> + Join pg_opclass.opcnamespace => pg_namespace.oid
>   Join pg_opclass.opcintype => pg_type.oid
> + Join pg_operator.oprnamespace => pg_namespace.oid
>   Join pg_operator.oprleft => pg_type.oid
>   Join pg_operator.oprright => pg_type.oid
>   Join pg_operator.oprresult => pg_type.oid
> ***************
> *** 70,94 ****
>   Join pg_operator.oprnegate => pg_operator.oid
>   Join pg_operator.oprlsortop => pg_operator.oid
>   Join pg_operator.oprrsortop => pg_operator.oid
>   Join pg_operator.oprcode => pg_proc.oid
>   Join pg_operator.oprrest => pg_proc.oid
>   Join pg_operator.oprjoin => pg_proc.oid
>   Join pg_proc.prolang => pg_language.oid
>   Join pg_proc.prorettype => pg_type.oid
>   Join pg_rewrite.ev_class => pg_class.oid
> - Join pg_statistic.starelid => pg_class.oid
> - Join pg_statistic.staop1 => pg_operator.oid
> - Join pg_statistic.staop2 => pg_operator.oid
> - Join pg_statistic.staop3 => pg_operator.oid
>   Join pg_trigger.tgrelid => pg_class.oid
>   Join pg_trigger.tgfoid => pg_proc.oid
>   Join pg_type.typrelid => pg_class.oid
>   Join pg_type.typelem => pg_type.oid
>   Join pg_type.typinput => pg_proc.oid
>   Join pg_type.typoutput => pg_proc.oid
> - Join pg_type.typreceive => pg_proc.oid
> - Join pg_type.typsend => pg_proc.oid
> -
>   ---------------------------------------------------------------------------
>
>   Bruce Momjian (root@candle.pha.pa.us)
> --- 97,119 ----
>   Join pg_operator.oprnegate => pg_operator.oid
>   Join pg_operator.oprlsortop => pg_operator.oid
>   Join pg_operator.oprrsortop => pg_operator.oid
> + Join pg_operator.oprltcmpop => pg_operator.oid
> + Join pg_operator.oprgtcmpop => pg_operator.oid
>   Join pg_operator.oprcode => pg_proc.oid
>   Join pg_operator.oprrest => pg_proc.oid
>   Join pg_operator.oprjoin => pg_proc.oid
> + Join pg_proc.pronamespace => pg_namespace.oid
>   Join pg_proc.prolang => pg_language.oid
>   Join pg_proc.prorettype => pg_type.oid
>   Join pg_rewrite.ev_class => pg_class.oid
>   Join pg_trigger.tgrelid => pg_class.oid
>   Join pg_trigger.tgfoid => pg_proc.oid
> + Join pg_type.typnamespace => pg_namespace.oid
>   Join pg_type.typrelid => pg_class.oid
>   Join pg_type.typelem => pg_type.oid
>   Join pg_type.typinput => pg_proc.oid
>   Join pg_type.typoutput => pg_proc.oid
>   ---------------------------------------------------------------------------
>
>   Bruce Momjian (root@candle.pha.pa.us)
> + Updated for 7.3 by Joe Conway (mail@joeconway.com)
> Index: contrib/findoidjoins/findoidjoins.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/findoidjoins.c,v
> retrieving revision 1.17
> diff -c -r1.17 findoidjoins.c
> *** contrib/findoidjoins/findoidjoins.c    4 Sep 2002 20:31:06 -0000    1.17
> --- contrib/findoidjoins/findoidjoins.c    5 Sep 2002 04:51:16 -0000
> ***************
> *** 1,109 ****
>   /*
> !  * findoidjoins.c, requires src/interfaces/libpgeasy
>    *
>    */
> - #include "postgres_fe.h"
>
> ! #include "libpq-fe.h"
> ! #include "halt.h"
> ! #include "libpgeasy.h"
>
> ! PGresult   *attres,
> !            *relres;
>
>   int
>   main(int argc, char **argv)
>   {
> !     char        query[4000];
> !     char        relname[256];
> !     char        relname2[256];
> !     char        attname[256];
> !     char        typname[256];
> !     int            count;
> !     char        optstr[256];
>
>       if (argc != 2)
> !         halt("Usage:  %s database\n", argv[0]);
>
> !     snprintf(optstr, 256, "dbname=%s", argv[1]);
> !     connectdb(optstr);
>
> !     on_error_continue();
> !     on_error_stop();
>
> !     doquery("BEGIN WORK");
> !     doquery("\
> !         DECLARE c_attributes BINARY CURSOR FOR \
> !         SELECT typname, relname, a.attname \
> !         FROM pg_class c, pg_attribute a, pg_type t \
> !         WHERE a.attnum > 0 AND \
> !               relkind = 'r' AND \
> !               (typname = 'oid' OR \
> !                typname = 'regproc' OR \
> !                typname = 'regclass' OR \
> !                typname = 'regtype') AND \
> !               a.attrelid = c.oid AND \
> !               a.atttypid = t.oid \
> !         ORDER BY 2, a.attnum ; \
> !         ");
> !     doquery("FETCH ALL IN c_attributes");
> !     attres = get_result();
> !
> !     doquery("\
> !         DECLARE c_relations BINARY CURSOR FOR \
> !         SELECT relname \
> !         FROM pg_class c \
> !         WHERE relkind = 'r' AND relhasoids \
> !         ORDER BY 1; \
> !         ");
> !     doquery("FETCH ALL IN c_relations");
> !     relres = get_result();
>
> !     set_result(attres);
> !     while (fetch(typname, relname, attname) != END_OF_TUPLES)
>       {
> !         set_result(relres);
> !         reset_fetch();
> !         while (fetch(relname2) != END_OF_TUPLES)
> !         {
> !             unset_result(relres);
> !             if (strcmp(typname, "oid") == 0)
> !                 snprintf(query, 4000, "\
> !                     DECLARE c_matches BINARY CURSOR FOR \
> !                     SELECT    count(*)::int4 \
> !                         FROM \"%s\" t1, \"%s\" t2 \
> !                     WHERE t1.\"%s\" = t2.oid ",
> !                          relname, relname2, attname);
> !             else
> !                 sprintf(query, 4000, "\
> !                     DECLARE c_matches BINARY CURSOR FOR \
> !                     SELECT    count(*)::int4 \
> !                         FROM \"%s\" t1, \"%s\" t2 \
> !                     WHERE t1.\"%s\"::oid = t2.oid ",
> !                         relname, relname2, attname);
> !
> !             doquery(query);
> !             doquery("FETCH ALL IN c_matches");
> !             fetch(&count);
> !             if (count != 0)
> !                 printf("Join %s.%s => %s.oid\n", relname, attname, relname2);
> !             doquery("CLOSE c_matches");
> !             set_result(relres);
> !         }
> !         set_result(attres);
>       }
>
> !     set_result(relres);
> !     doquery("CLOSE c_relations");
> !     PQclear(relres);
> !
> !     set_result(attres);
> !     doquery("CLOSE c_attributes");
> !     PQclear(attres);
> !     unset_result(attres);
>
> !     doquery("COMMIT WORK");
>
> !     disconnectdb();
> !     return 0;
>   }
> --- 1,152 ----
>   /*
> !  * findoidjoins
> !  *
> !  * Copyright 2002 by PostgreSQL Global Development Group
> !  *
> !  * Permission to use, copy, modify, and distribute this software and its
> !  * documentation for any purpose, without fee, and without a written agreement
> !  * is hereby granted, provided that the above copyright notice and this
> !  * paragraph and the following two paragraphs appear in all copies.
> !  *
> !  * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
> !  * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> !  * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> !  * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
> !  * POSSIBILITY OF SUCH DAMAGE.
> !  *
> !  * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
> !  * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
> !  * AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS
> !  * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
> !  * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
>    *
>    */
>
> ! #include <stdlib.h>
>
> ! #include "postgres_fe.h"
> ! #include "libpq-fe.h"
> ! #include "pqexpbuffer.h"
>
>   int
>   main(int argc, char **argv)
>   {
> !     PGconn               *conn;
> !     PQExpBufferData        sql;
> !     PGresult           *res;
> !     PGresult           *pkrel_res;
> !     PGresult           *fkrel_res;
> !     char               *fk_relname;
> !     char               *fk_nspname;
> !     char               *fk_attname;
> !     char               *fk_typname;
> !     char               *pk_relname;
> !     char               *pk_nspname;
> !     int                    fk, pk;        /* loop counters */
>
>       if (argc != 2)
> !     {
> !         fprintf(stderr, "Usage:  %s database\n", argv[0]);
> !         exit(EXIT_FAILURE);
> !     }
>
> !     initPQExpBuffer(&sql);
> !     appendPQExpBuffer(&sql, "dbname=%s", argv[1]);
>
> !     conn = PQconnectdb(sql.data);
> !     if (PQstatus(conn) == CONNECTION_BAD)
> !     {
> !         fprintf(stderr, "connection error:  %s\n", PQerrorMessage(conn));
> !         exit(EXIT_FAILURE);
> !     }
> !
> !     termPQExpBuffer(&sql);
> !     initPQExpBuffer(&sql);
>
> !     appendPQExpBuffer(&sql, "%s",
> !         "SELECT c.relname, (SELECT nspname FROM "
> !         "pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
> !         "FROM pg_catalog.pg_class c "
> !         "WHERE c.relkind = 'r' "
> !         "AND c.relhasoids "
> !         "ORDER BY nspname, c.relname"
> !         );
>
> !     res = PQexec(conn, sql.data);
> !     if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
>       {
> !         fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
> !         exit(EXIT_FAILURE);
>       }
> +     pkrel_res = res;
> +
> +     termPQExpBuffer(&sql);
> +     initPQExpBuffer(&sql);
>
> !     appendPQExpBuffer(&sql, "%s",
> !         "SELECT c.relname, "
> !         "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
> !         "a.attname, "
> !         "t.typname "
> !         "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, pg_catalog.pg_type t "
> !         "WHERE a.attnum > 0 AND c.relkind = 'r' "
> !         "AND t.typnamespace IN (SELECT n.oid FROM pg_catalog.pg_namespace n WHERE nspname LIKE 'pg\\_%') "
> !         "AND (t.typname = 'oid' OR t.typname LIKE 'reg%') "
> !         "AND a.attrelid = c.oid "
> !         "AND a.atttypid = t.oid "
> !         "ORDER BY nspname, c.relname, a.attnum"
> !         );
>
> !     res = PQexec(conn, sql.data);
> !     if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
> !     {
> !         fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
> !         exit(EXIT_FAILURE);
> !     }
> !     fkrel_res = res;
> !
> !     termPQExpBuffer(&sql);
> !     initPQExpBuffer(&sql);
> !
> !     for (fk = 0; fk < PQntuples(fkrel_res); fk++)
> !     {
> !         fk_relname = PQgetvalue(fkrel_res, fk, 0);
> !         fk_nspname = PQgetvalue(fkrel_res, fk, 1);
> !         fk_attname = PQgetvalue(fkrel_res, fk, 2);
> !         fk_typname = PQgetvalue(fkrel_res, fk, 3);
> !
> !         for (pk = 0; pk < PQntuples(pkrel_res); pk++)
> !         {
> !             pk_relname = PQgetvalue(pkrel_res, pk, 0);
> !             pk_nspname = PQgetvalue(pkrel_res, pk, 1);
> !
> !             appendPQExpBuffer(&sql,
> !                 "SELECT    1 "
> !                 "FROM \"%s\".\"%s\" t1, "
> !                 "\"%s\".\"%s\" t2 "
> !                 "WHERE t1.\"%s\"::oid = t2.oid",
> !                 fk_nspname, fk_relname, pk_nspname, pk_relname, fk_attname);
> !
> !             res = PQexec(conn, sql.data);
> !             if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
> !             {
> !                 fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
> !                 exit(EXIT_FAILURE);
> !             }
> !
> !             if (PQntuples(res) != 0)
> !                 printf("Join %s.%s => %s.oid\n",
> !                         fk_relname, fk_attname, pk_relname);
> !
> !             PQclear(res);
> !
> !             termPQExpBuffer(&sql);
> !             initPQExpBuffer(&sql);
> !         }
> !     }
> !     PQclear(pkrel_res);
> !     PQclear(fkrel_res);
> !     PQfinish(conn);
>
> !     exit(EXIT_SUCCESS);
>   }

> Join pg_aggregate.aggfnoid => pg_proc.oid
> Join pg_aggregate.aggtransfn => pg_proc.oid
> Join pg_aggregate.aggfinalfn => pg_proc.oid
> Join pg_aggregate.aggtranstype => pg_type.oid
> Join pg_am.amgettuple => pg_proc.oid
> Join pg_am.aminsert => pg_proc.oid
> Join pg_am.ambeginscan => pg_proc.oid
> Join pg_am.amrescan => pg_proc.oid
> Join pg_am.amendscan => pg_proc.oid
> Join pg_am.ammarkpos => pg_proc.oid
> Join pg_am.amrestrpos => pg_proc.oid
> Join pg_am.ambuild => pg_proc.oid
> Join pg_am.ambulkdelete => pg_proc.oid
> Join pg_am.amcostestimate => pg_proc.oid
> Join pg_amop.amopclaid => pg_opclass.oid
> Join pg_amop.amopopr => pg_operator.oid
> Join pg_amproc.amopclaid => pg_opclass.oid
> Join pg_amproc.amproc => pg_proc.oid
> Join pg_attribute.attrelid => pg_class.oid
> Join pg_attribute.atttypid => pg_type.oid
> Join pg_cast.castsource => pg_type.oid
> Join pg_cast.casttarget => pg_type.oid
> Join pg_cast.castfunc => pg_proc.oid
> Join pg_class.relnamespace => pg_namespace.oid
> Join pg_class.reltype => pg_type.oid
> Join pg_class.relam => pg_am.oid
> Join pg_class.relfilenode => pg_class.oid
> Join pg_class.reltoastrelid => pg_class.oid
> Join pg_class.reltoastidxid => pg_class.oid
> Join pg_conversion.connamespace => pg_namespace.oid
> Join pg_conversion.conproc => pg_proc.oid
> Join pg_database.datlastsysoid => pg_conversion.oid
> Join pg_depend.classid => pg_class.oid
> Join pg_depend.objid => pg_conversion.oid
> Join pg_depend.objid => pg_rewrite.oid
> Join pg_depend.objid => pg_type.oid
> Join pg_depend.refclassid => pg_class.oid
> Join pg_depend.refobjid => pg_cast.oid
> Join pg_depend.refobjid => pg_class.oid
> Join pg_depend.refobjid => pg_language.oid
> Join pg_depend.refobjid => pg_namespace.oid
> Join pg_depend.refobjid => pg_opclass.oid
> Join pg_depend.refobjid => pg_operator.oid
> Join pg_depend.refobjid => pg_proc.oid
> Join pg_depend.refobjid => pg_trigger.oid
> Join pg_depend.refobjid => pg_type.oid
> Join pg_description.objoid => pg_am.oid
> Join pg_description.objoid => pg_database.oid
> Join pg_description.objoid => pg_language.oid
> Join pg_description.objoid => pg_namespace.oid
> Join pg_description.objoid => pg_proc.oid
> Join pg_description.objoid => pg_type.oid
> Join pg_description.classoid => pg_class.oid
> Join pg_index.indexrelid => pg_class.oid
> Join pg_index.indrelid => pg_class.oid
> Join pg_language.lanvalidator => pg_proc.oid
> Join pg_opclass.opcamid => pg_am.oid
> Join pg_opclass.opcnamespace => pg_namespace.oid
> Join pg_opclass.opcintype => pg_type.oid
> Join pg_operator.oprnamespace => pg_namespace.oid
> Join pg_operator.oprleft => pg_type.oid
> Join pg_operator.oprright => pg_type.oid
> Join pg_operator.oprresult => pg_type.oid
> Join pg_operator.oprcom => pg_operator.oid
> Join pg_operator.oprnegate => pg_operator.oid
> Join pg_operator.oprlsortop => pg_operator.oid
> Join pg_operator.oprrsortop => pg_operator.oid
> Join pg_operator.oprltcmpop => pg_operator.oid
> Join pg_operator.oprgtcmpop => pg_operator.oid
> Join pg_operator.oprcode => pg_proc.oid
> Join pg_operator.oprrest => pg_proc.oid
> Join pg_operator.oprjoin => pg_proc.oid
> Join pg_proc.pronamespace => pg_namespace.oid
> Join pg_proc.prolang => pg_language.oid
> Join pg_proc.prorettype => pg_type.oid
> Join pg_rewrite.ev_class => pg_class.oid
> Join pg_trigger.tgrelid => pg_class.oid
> Join pg_trigger.tgfoid => pg_proc.oid
> Join pg_type.typnamespace => pg_namespace.oid
> Join pg_type.typrelid => pg_class.oid
> Join pg_type.typelem => pg_type.oid
> Join pg_type.typinput => pg_proc.oid
> Join pg_type.typoutput => pg_proc.oid

> --
> -- This is created by pgsql/contrib/findoidjoins/make_oidjoin_check
> --
> SELECT    ctid, pg_aggregate.aggfnoid
> FROM    pg_aggregate
> WHERE    pg_aggregate.aggfnoid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfnoid);
> SELECT    ctid, pg_aggregate.aggtransfn
> FROM    pg_aggregate
> WHERE    pg_aggregate.aggtransfn != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggtransfn);
> SELECT    ctid, pg_aggregate.aggfinalfn
> FROM    pg_aggregate
> WHERE    pg_aggregate.aggfinalfn != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfinalfn);
> SELECT    ctid, pg_aggregate.aggtranstype
> FROM    pg_aggregate
> WHERE    pg_aggregate.aggtranstype != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggtranstype);
> SELECT    ctid, pg_am.amgettuple
> FROM    pg_am
> WHERE    pg_am.amgettuple != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amgettuple);
> SELECT    ctid, pg_am.aminsert
> FROM    pg_am
> WHERE    pg_am.aminsert != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.aminsert);
> SELECT    ctid, pg_am.ambeginscan
> FROM    pg_am
> WHERE    pg_am.ambeginscan != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambeginscan);
> SELECT    ctid, pg_am.amrescan
> FROM    pg_am
> WHERE    pg_am.amrescan != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrescan);
> SELECT    ctid, pg_am.amendscan
> FROM    pg_am
> WHERE    pg_am.amendscan != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amendscan);
> SELECT    ctid, pg_am.ammarkpos
> FROM    pg_am
> WHERE    pg_am.ammarkpos != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ammarkpos);
> SELECT    ctid, pg_am.amrestrpos
> FROM    pg_am
> WHERE    pg_am.amrestrpos != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrestrpos);
> SELECT    ctid, pg_am.ambuild
> FROM    pg_am
> WHERE    pg_am.ambuild != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambuild);
> SELECT    ctid, pg_am.ambulkdelete
> FROM    pg_am
> WHERE    pg_am.ambulkdelete != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambulkdelete);
> SELECT    ctid, pg_am.amcostestimate
> FROM    pg_am
> WHERE    pg_am.amcostestimate != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amcostestimate);
> SELECT    ctid, pg_amop.amopclaid
> FROM    pg_amop
> WHERE    pg_amop.amopclaid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amop.amopclaid);
> SELECT    ctid, pg_amop.amopopr
> FROM    pg_amop
> WHERE    pg_amop.amopopr != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_amop.amopopr);
> SELECT    ctid, pg_amproc.amopclaid
> FROM    pg_amproc
> WHERE    pg_amproc.amopclaid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amproc.amopclaid);
> SELECT    ctid, pg_amproc.amproc
> FROM    pg_amproc
> WHERE    pg_amproc.amproc != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_amproc.amproc);
> SELECT    ctid, pg_attribute.attrelid
> FROM    pg_attribute
> WHERE    pg_attribute.attrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_attribute.attrelid);
> SELECT    ctid, pg_attribute.atttypid
> FROM    pg_attribute
> WHERE    pg_attribute.atttypid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_attribute.atttypid);
> SELECT    ctid, pg_cast.castsource
> FROM    pg_cast
> WHERE    pg_cast.castsource != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_cast.castsource);
> SELECT    ctid, pg_cast.casttarget
> FROM    pg_cast
> WHERE    pg_cast.casttarget != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_cast.casttarget);
> SELECT    ctid, pg_cast.castfunc
> FROM    pg_cast
> WHERE    pg_cast.castfunc != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_cast.castfunc);
> SELECT    ctid, pg_class.relnamespace
> FROM    pg_class
> WHERE    pg_class.relnamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_class.relnamespace);
> SELECT    ctid, pg_class.reltype
> FROM    pg_class
> WHERE    pg_class.reltype != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_class.reltype);
> SELECT    ctid, pg_class.relam
> FROM    pg_class
> WHERE    pg_class.relam != 0 AND
>     NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_class.relam);
> SELECT    ctid, pg_class.relfilenode
> FROM    pg_class
> WHERE    pg_class.relfilenode != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.relfilenode);
> SELECT    ctid, pg_class.reltoastrelid
> FROM    pg_class
> WHERE    pg_class.reltoastrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastrelid);
> SELECT    ctid, pg_class.reltoastidxid
> FROM    pg_class
> WHERE    pg_class.reltoastidxid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastidxid);
> SELECT    ctid, pg_conversion.connamespace
> FROM    pg_conversion
> WHERE    pg_conversion.connamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_conversion.connamespace);
> SELECT    ctid, pg_conversion.conproc
> FROM    pg_conversion
> WHERE    pg_conversion.conproc != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_conversion.conproc);
> SELECT    ctid, pg_database.datlastsysoid
> FROM    pg_database
> WHERE    pg_database.datlastsysoid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_conversion AS t1 WHERE t1.oid = pg_database.datlastsysoid);
> SELECT    ctid, pg_depend.classid
> FROM    pg_depend
> WHERE    pg_depend.classid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_depend.classid);
> SELECT    ctid, pg_depend.refclassid
> FROM    pg_depend
> WHERE    pg_depend.refclassid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_depend.refclassid);
> SELECT    ctid, pg_description.classoid
> FROM    pg_description
> WHERE    pg_description.classoid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_description.classoid);
> SELECT    ctid, pg_index.indexrelid
> FROM    pg_index
> WHERE    pg_index.indexrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indexrelid);
> SELECT    ctid, pg_index.indrelid
> FROM    pg_index
> WHERE    pg_index.indrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indrelid);
> SELECT    ctid, pg_language.lanvalidator
> FROM    pg_language
> WHERE    pg_language.lanvalidator != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_language.lanvalidator);
> SELECT    ctid, pg_opclass.opcamid
> FROM    pg_opclass
> WHERE    pg_opclass.opcamid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_opclass.opcamid);
> SELECT    ctid, pg_opclass.opcnamespace
> FROM    pg_opclass
> WHERE    pg_opclass.opcnamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_opclass.opcnamespace);
> SELECT    ctid, pg_opclass.opcintype
> FROM    pg_opclass
> WHERE    pg_opclass.opcintype != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcintype);
> SELECT    ctid, pg_operator.oprnamespace
> FROM    pg_operator
> WHERE    pg_operator.oprnamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_operator.oprnamespace);
> SELECT    ctid, pg_operator.oprleft
> FROM    pg_operator
> WHERE    pg_operator.oprleft != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprleft);
> SELECT    ctid, pg_operator.oprright
> FROM    pg_operator
> WHERE    pg_operator.oprright != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprright);
> SELECT    ctid, pg_operator.oprresult
> FROM    pg_operator
> WHERE    pg_operator.oprresult != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprresult);
> SELECT    ctid, pg_operator.oprcom
> FROM    pg_operator
> WHERE    pg_operator.oprcom != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprcom);
> SELECT    ctid, pg_operator.oprnegate
> FROM    pg_operator
> WHERE    pg_operator.oprnegate != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprnegate);
> SELECT    ctid, pg_operator.oprlsortop
> FROM    pg_operator
> WHERE    pg_operator.oprlsortop != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprlsortop);
> SELECT    ctid, pg_operator.oprrsortop
> FROM    pg_operator
> WHERE    pg_operator.oprrsortop != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprrsortop);
> SELECT    ctid, pg_operator.oprltcmpop
> FROM    pg_operator
> WHERE    pg_operator.oprltcmpop != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprltcmpop);
> SELECT    ctid, pg_operator.oprgtcmpop
> FROM    pg_operator
> WHERE    pg_operator.oprgtcmpop != 0 AND
>     NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprgtcmpop);
> SELECT    ctid, pg_operator.oprcode
> FROM    pg_operator
> WHERE    pg_operator.oprcode != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprcode);
> SELECT    ctid, pg_operator.oprrest
> FROM    pg_operator
> WHERE    pg_operator.oprrest != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprrest);
> SELECT    ctid, pg_operator.oprjoin
> FROM    pg_operator
> WHERE    pg_operator.oprjoin != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprjoin);
> SELECT    ctid, pg_proc.pronamespace
> FROM    pg_proc
> WHERE    pg_proc.pronamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_proc.pronamespace);
> SELECT    ctid, pg_proc.prolang
> FROM    pg_proc
> WHERE    pg_proc.prolang != 0 AND
>     NOT EXISTS(SELECT * FROM pg_language AS t1 WHERE t1.oid = pg_proc.prolang);
> SELECT    ctid, pg_proc.prorettype
> FROM    pg_proc
> WHERE    pg_proc.prorettype != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_proc.prorettype);
> SELECT    ctid, pg_rewrite.ev_class
> FROM    pg_rewrite
> WHERE    pg_rewrite.ev_class != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_rewrite.ev_class);
> SELECT    ctid, pg_trigger.tgrelid
> FROM    pg_trigger
> WHERE    pg_trigger.tgrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_trigger.tgrelid);
> SELECT    ctid, pg_trigger.tgfoid
> FROM    pg_trigger
> WHERE    pg_trigger.tgfoid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_trigger.tgfoid);
> SELECT    ctid, pg_type.typnamespace
> FROM    pg_type
> WHERE    pg_type.typnamespace != 0 AND
>     NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_type.typnamespace);
> SELECT    ctid, pg_type.typrelid
> FROM    pg_type
> WHERE    pg_type.typrelid != 0 AND
>     NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_type.typrelid);
> SELECT    ctid, pg_type.typelem
> FROM    pg_type
> WHERE    pg_type.typelem != 0 AND
>     NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_type.typelem);
> SELECT    ctid, pg_type.typinput
> FROM    pg_type
> WHERE    pg_type.typinput != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typinput);
> SELECT    ctid, pg_type.typoutput
> FROM    pg_type
> WHERE    pg_type.typoutput != 0 AND
>     NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typoutput);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073