Move findoidjoins out of contrib and into src/tools, which is a more

logical place for it since it is of no use to users.  Per recent
discussions on cleaning up contrib.

4 files changed, 359 insertions, 0 deletions

diff --git a/src/tools/findoidjoins/Makefile b/src/tools/findoidjoins/Makefile
new file mode 100644
index 00000000000..5a11276bf41
--- /dev/null
+++ b/src/tools/findoidjoins/Makefile
@@ -0,0 +1,25 @@
+#-------------------------------------------------------------------------
+#
+# Makefile for src/tools/findoidjoins
+#
+# Copyright (c) 2003-2005, PostgreSQL Global Development Group
+#
+# $PostgreSQL: pgsql/src/tools/findoidjoins/Makefile,v 1.1 2005/06/23 02:33:28 tgl Exp $
+#
+#-------------------------------------------------------------------------
+
+subdir = src/tools/findoidjoins
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+
+override CPPFLAGS := -DFRONTEND -I$(libpq_srcdir) $(CPPFLAGS)
+
+OBJS= findoidjoins.o
+
+all: submake-libpq submake-libpgport findoidjoins
+
+findoidjoins: findoidjoins.o $(libpq_builddir)/libpq.a
+	$(CC) $(CFLAGS) findoidjoins.o $(libpq_pgport) $(LDFLAGS) $(LIBS) -o $@$(X)
+
+clean distclean maintainer-clean:
+	rm -f findoidjoins$(X) $(OBJS)
diff --git a/src/tools/findoidjoins/README b/src/tools/findoidjoins/README
new file mode 100644
index 00000000000..ce31b194728
--- /dev/null
+++ b/src/tools/findoidjoins/README
@@ -0,0 +1,119 @@
+
+   			      findoidjoins
+
+This program scans a database and prints oid fields (also reg* fields)
+and the tables they join to.  We don't really recommend running it on
+anything but an empty database, such as template1; else it's likely to
+be very slow.
+
+Run on an empty database, it returns the system join relationships (shown
+below for 8.1).  Note that unexpected matches may indicate bogus entries
+in system tables --- don't accept a peculiar match without question.
+In particular, a field shown as joining to more than one target table is
+probably messed up.  In 8.1, 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* column that don't match any row in the expected table).
+Note that fields joining to more than one table are NOT processed.
+
+The result of make_oidjoins_check should be installed as the "oidjoins"
+regression test.  The oidjoins test should be updated after any
+revision in the patterns of cross-links between system tables.
+(Ideally we'd just regenerate the script as part of the regression
+tests themselves, but that seems too slow...)
+
+NOTE: in 8.1, make_oidjoins_check produces two bogus join checks:
+Join pg_catalog.pg_class.relfilenode => pg_catalog.pg_class.oid
+Join pg_catalog.pg_database.datlastsysoid => pg_catalog.pg_database.oid
+These are artifacts and should not be added to the oidjoins regress test.
+
+---------------------------------------------------------------------------
+
+Join pg_catalog.pg_aggregate.aggfnoid => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_aggregate.aggtransfn => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_aggregate.aggfinalfn => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_aggregate.aggsortop => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_aggregate.aggtranstype => pg_catalog.pg_type.oid
+Join pg_catalog.pg_am.aminsert => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.ambeginscan => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.amgettuple => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.amgetmulti => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.amrescan => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.amendscan => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.ammarkpos => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.amrestrpos => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.ambuild => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.ambulkdelete => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.amvacuumcleanup => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_am.amcostestimate => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_amop.amopclaid => pg_catalog.pg_opclass.oid
+Join pg_catalog.pg_amop.amopsubtype => pg_catalog.pg_type.oid
+Join pg_catalog.pg_amop.amopopr => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_amproc.amopclaid => pg_catalog.pg_opclass.oid
+Join pg_catalog.pg_amproc.amprocsubtype => pg_catalog.pg_type.oid
+Join pg_catalog.pg_amproc.amproc => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_attribute.attrelid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_attribute.atttypid => pg_catalog.pg_type.oid
+Join pg_catalog.pg_cast.castsource => pg_catalog.pg_type.oid
+Join pg_catalog.pg_cast.casttarget => pg_catalog.pg_type.oid
+Join pg_catalog.pg_cast.castfunc => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_class.relnamespace => pg_catalog.pg_namespace.oid
+Join pg_catalog.pg_class.reltype => pg_catalog.pg_type.oid
+Join pg_catalog.pg_class.relam => pg_catalog.pg_am.oid
+Join pg_catalog.pg_class.reltablespace => pg_catalog.pg_tablespace.oid
+Join pg_catalog.pg_class.reltoastrelid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_class.reltoastidxid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_constraint.connamespace => pg_catalog.pg_namespace.oid
+Join pg_catalog.pg_constraint.contypid => pg_catalog.pg_type.oid
+Join pg_catalog.pg_conversion.connamespace => pg_catalog.pg_namespace.oid
+Join pg_catalog.pg_conversion.conproc => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_database.dattablespace => pg_catalog.pg_tablespace.oid
+Join pg_catalog.pg_depend.classid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_depend.refclassid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_description.classoid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_index.indexrelid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_index.indrelid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_language.lanvalidator => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_opclass.opcamid => pg_catalog.pg_am.oid
+Join pg_catalog.pg_opclass.opcnamespace => pg_catalog.pg_namespace.oid
+Join pg_catalog.pg_opclass.opcintype => pg_catalog.pg_type.oid
+Join pg_catalog.pg_operator.oprnamespace => pg_catalog.pg_namespace.oid
+Join pg_catalog.pg_operator.oprleft => pg_catalog.pg_type.oid
+Join pg_catalog.pg_operator.oprright => pg_catalog.pg_type.oid
+Join pg_catalog.pg_operator.oprresult => pg_catalog.pg_type.oid
+Join pg_catalog.pg_operator.oprcom => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_operator.oprnegate => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_operator.oprlsortop => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_operator.oprrsortop => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_operator.oprltcmpop => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_operator.oprgtcmpop => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_operator.oprcode => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_operator.oprrest => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_operator.oprjoin => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_proc.pronamespace => pg_catalog.pg_namespace.oid
+Join pg_catalog.pg_proc.prolang => pg_catalog.pg_language.oid
+Join pg_catalog.pg_proc.prorettype => pg_catalog.pg_type.oid
+Join pg_catalog.pg_rewrite.ev_class => pg_catalog.pg_class.oid
+Join pg_catalog.pg_statistic.starelid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_statistic.staop1 => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_statistic.staop2 => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_statistic.staop3 => pg_catalog.pg_operator.oid
+Join pg_catalog.pg_trigger.tgrelid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_trigger.tgfoid => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_type.typnamespace => pg_catalog.pg_namespace.oid
+Join pg_catalog.pg_type.typrelid => pg_catalog.pg_class.oid
+Join pg_catalog.pg_type.typelem => pg_catalog.pg_type.oid
+Join pg_catalog.pg_type.typinput => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_type.typoutput => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_type.typreceive => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_type.typsend => pg_catalog.pg_proc.oid
+Join pg_catalog.pg_type.typbasetype => pg_catalog.pg_type.oid
+
+---------------------------------------------------------------------------
+
+Bruce Momjian (root@candle.pha.pa.us)
+Updated for 7.3 by Joe Conway (mail@joeconway.com)
diff --git a/src/tools/findoidjoins/findoidjoins.c b/src/tools/findoidjoins/findoidjoins.c
new file mode 100644
index 00000000000..db1fc12460c
--- /dev/null
+++ b/src/tools/findoidjoins/findoidjoins.c
@@ -0,0 +1,147 @@
+/*
+ * findoidjoins.c
+ *
+ * Copyright (c) 2002-2005, PostgreSQL Global Development Group
+ *
+ * $PostgreSQL: pgsql/src/tools/findoidjoins/findoidjoins.c,v 1.1 2005/06/23 02:33:28 tgl Exp $
+ */
+#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	   *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);
+	}
+
+	/* Get a list of relations that have OIDs */
+
+	resetPQExpBuffer(&sql);
+
+	appendPQExpBuffer(&sql, "%s",
+					  "SET search_path = public;"
+					  "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;
+
+	/* Get a list of columns of OID type (or any OID-alias type) */
+
+	resetPQExpBuffer(&sql);
+
+	appendPQExpBuffer(&sql, "%s",
+					  "SELECT c.relname, "
+					  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
+					  "a.attname "
+				 "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
+					  "WHERE a.attnum > 0 AND c.relkind = 'r' "
+					  "AND a.attrelid = c.oid "
+					  "AND a.atttypid IN ('pg_catalog.oid'::regtype, "
+					  " 'pg_catalog.regclass'::regtype, "
+					  " 'pg_catalog.regoper'::regtype, "
+					  " 'pg_catalog.regoperator'::regtype, "
+					  " 'pg_catalog.regproc'::regtype, "
+					  " 'pg_catalog.regprocedure'::regtype, "
+					  " 'pg_catalog.regtype'::regtype) "
+					  "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;
+
+	/*
+	 * For each column and each relation-having-OIDs, look to see if the
+	 * column contains any values matching entries in the relation.
+	 */
+
+	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);
+
+		for (pk = 0; pk < PQntuples(pkrel_res); pk++)
+		{
+			pk_relname = PQgetvalue(pkrel_res, pk, 0);
+			pk_nspname = PQgetvalue(pkrel_res, pk, 1);
+
+			resetPQExpBuffer(&sql);
+
+			appendPQExpBuffer(&sql,
+							  "SELECT	1 "
+							  "FROM \"%s\".\"%s\" t1, "
+							  "\"%s\".\"%s\" t2 "
+							  "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
+							  "LIMIT 1",
+			 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 => %s.%s.oid\n",
+					   fk_nspname, fk_relname, fk_attname,
+					   pk_nspname, pk_relname);
+
+			PQclear(res);
+		}
+	}
+
+	PQclear(pkrel_res);
+	PQclear(fkrel_res);
+	PQfinish(conn);
+
+	termPQExpBuffer(&sql);
+
+	exit(EXIT_SUCCESS);
+}
diff --git a/src/tools/findoidjoins/make_oidjoins_check b/src/tools/findoidjoins/make_oidjoins_check
new file mode 100755
index 00000000000..b6a720a5048
--- /dev/null
+++ b/src/tools/findoidjoins/make_oidjoins_check
@@ -0,0 +1,68 @@
+#! /bin/sh
+
+# You first run findoidjoins on the template1 database, and send that
+# output into this script to generate a list of SQL statements.
+
+# NOTE: any field that findoidjoins thinks joins to more than one table
+# will NOT be checked by the output of this script.  You should be
+# suspicious of multiple entries in findoidjoins' output.
+
+# Caution: you may need to use GNU awk.
+AWK=${AWK:-awk}
+
+TMP="${TMPDIR:-/tmp}/make_oidjoins_check.$$"
+trap "rm -rf $TMP" 0 1 2 3 15
+
+# Create a temporary directory with the proper permissions so no one can
+# intercept our temporary files and cause a security breach.
+OMASK="`umask`"
+umask 077
+if ! mkdir $TMP
+then	echo "Can't create temporary directory $TMP." 1>&2
+	exit 1
+fi
+umask "$OMASK"
+unset OMASK
+
+INPUTFILE="$TMP/a"
+DUPSFILE="$TMP/b"
+NONDUPSFILE="$TMP/c"
+
+# Read input
+cat "$@" >$INPUTFILE
+
+# Look for fields with multiple references.
+cat $INPUTFILE | cut -d' ' -f2 | sort | uniq -d >$DUPSFILE
+if [ -s $DUPSFILE ] ; then
+	echo "Ignoring these fields that link to multiple tables:" 1>&2
+	cat $DUPSFILE 1>&2
+fi
+
+# Get the non-multiply-referenced fields.
+cat $INPUTFILE | while read LINE
+do
+	set -- $LINE
+	grep "^$2\$" $DUPSFILE >/dev/null 2>&1 || echo $LINE
+done >$NONDUPSFILE
+
+# Generate the output.
+cat $NONDUPSFILE |
+$AWK -F'[ \.]' '\
+	BEGIN \
+	{
+		printf "\
+--\n\
+-- This is created by pgsql/contrib/findoidjoins/make_oidjoin_check\n\
+--\n";
+	}
+	{
+		printf "\
+SELECT	ctid, %s \n\
+FROM	%s.%s fk \n\
+WHERE	%s != 0 AND \n\
+	NOT EXISTS(SELECT 1 FROM %s.%s pk WHERE pk.oid = fk.%s);\n",
+	$4, $2, $3, $4,
+	$6, $7, $4;
+	}'
+
+exit 0