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

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us)
> + Updated for 7.3 by Joe Conway (mail(at)joeconway(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073