findoidjoins patch (was Re: [HACKERS] findoidjoins) - Mailing list pgsql-patches

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);