Update findoidjoins for 6.5: remove workaround for long-dead bug,

tglsfdc · tglsfdc · commit 85e9e03e0b43 · 1999-03-26T07:21:58.000Z
use NOT EXISTS() which is a lot faster than NOT IN (),
update documentation.
diff --git a/contrib/findoidjoins/Makefile b/contrib/findoidjoins/Makefile
@@ -4,7 +4,7 @@
 #
 PGINTERFACE = ../pginterface/pginterface.o ../pginterface/halt.o # these have to be in your library search path
 TARGET = findoidjoins
-CFLAGS = -g -Wall -I. -I../../src/interfaces/libpq  -I/usr/local/pgsql/include
+CFLAGS = -g -Wall -I. -I../pginterface -I../../src/interfaces/libpq  -I/usr/local/pgsql/include
 LDFLAGS = -L/usr/local/pgsql/lib -lpq
 
 all : $(TARGET)
diff --git a/contrib/findoidjoins/README b/contrib/findoidjoins/README
@@ -1,27 +1,36 @@
 
-
    			      findoidjoins
 
-This program scans the a database, and prints oid fields, and the tables
-they join to.  PostgreSQL version 6.3.2 crashes with aggregates on
-views, so I have removed the view pg_user from the list of relations to
-examine.
+This program scans a database, and prints oid 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.
+
+It requires pgsql/contrib/pginterface to be compiled first.
 
-It requires /pgsql/contrib/pginterface to be compiled first.
+Run on an empty database, it returns the system join relationships
+(shown below for 6.5).  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.
 
-Run on am empty database, it returns the system join relationships:
+The shell script make_oidjoins_check converts findoidjoins' output
+into an SQL script that checks for dangling links (entries in an
+OID column that don't match any row in the expected table).
+The result of this script 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...)
 
 ---------------------------------------------------------------------------
 
 Join pg_aggregate.aggtransfn1 => pg_proc.oid
 Join pg_aggregate.aggtransfn2 => pg_proc.oid
 Join pg_aggregate.aggfinalfn => pg_proc.oid
-Join pg_aggregate.aggbasetype => pg_proc.oid
 Join pg_aggregate.aggbasetype => pg_type.oid
-Join pg_aggregate.aggtranstype1 => pg_proc.oid
 Join pg_aggregate.aggtranstype1 => pg_type.oid
 Join pg_aggregate.aggtranstype2 => pg_type.oid
-Join pg_aggregate.aggfinaltype => pg_proc.oid
 Join pg_aggregate.aggfinaltype => pg_type.oid
 Join pg_am.amgettuple => pg_proc.oid
 Join pg_am.aminsert => pg_proc.oid
@@ -35,14 +44,10 @@ Join pg_am.ambuild => pg_proc.oid
 Join pg_amop.amopid => pg_am.oid
 Join pg_amop.amopclaid => pg_opclass.oid
 Join pg_amop.amopopr => pg_operator.oid
-Join pg_amop.amopopr => pg_proc.oid
 Join pg_amop.amopselect => pg_proc.oid
 Join pg_amop.amopnpages => pg_proc.oid
 Join pg_amproc.amid => pg_am.oid
 Join pg_amproc.amopclaid => pg_opclass.oid
-Join pg_amproc.amopclaid => pg_operator.oid
-Join pg_amproc.amopclaid => pg_proc.oid
-Join pg_amproc.amproc => pg_operator.oid
 Join pg_amproc.amproc => pg_proc.oid
 Join pg_attribute.attrelid => pg_class.oid
 Join pg_attribute.atttypid => pg_type.oid
@@ -70,15 +75,12 @@ Join pg_proc.prolang => pg_language.oid
 Join pg_proc.prorettype => pg_type.oid
 Join pg_rewrite.ev_class => pg_class.oid
 Join pg_type.typrelid => pg_class.oid
-Join pg_type.typelem => pg_operator.oid
-Join pg_type.typelem => pg_proc.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)
diff --git a/contrib/findoidjoins/findoidjoins.c b/contrib/findoidjoins/findoidjoins.c
@@ -50,8 +50,7 @@ main(int argc, char **argv)
 		SELECT relname \
 		FROM pg_class c \
 		WHERE relkind = 'r' AND \
-			  relhasrules = 'f' AND \
-			  relname != 'pg_user' \
+			  relhasrules = 'f' \
 		ORDER BY 1; \
 		");
 	doquery("FETCH ALL IN c_relations");
diff --git a/contrib/findoidjoins/make_oidjoins_check b/contrib/findoidjoins/make_oidjoins_check
@@ -1,17 +1,37 @@
-:
+#! /bin/sh
+
 # You first run findoidjoins on the template1 database, and send that
 # output into this file 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}
+
 trap "rm -f /tmp/$$ /tmp/$$a /tmp/$$b" 0 1 2 3 15
 
+# Read input
 cat "$@" >/tmp/$$
+
+# Look for fields with multiple references.
 cat /tmp/$$ | cut -d' ' -f2 | sort | uniq -d >/tmp/$$a
+if [ -s /tmp/$$a ] ; then
+	echo "Ignoring these fields that link to multiple tables:" 1>&2
+	cat /tmp/$$a 1>&2
+fi
+
+# Get the non-multiply-referenced fields.
 cat /tmp/$$ | while read LINE
 do
 	set -- $LINE
 	grep "$2" /tmp/$$a >/dev/null 2>&1 || echo $LINE
 done >/tmp/$$b
+
+# Generate the output.
 cat /tmp/$$b |
-awk -F'[ \.]' '\
+$AWK -F'[ \.]' '\
 	BEGIN \
 	{
 		printf "\
@@ -23,13 +43,11 @@ awk -F'[ \.]' '\
 		printf "\
 SELECT	oid, %s.%s \n\
 FROM	%s \n\
-WHERE	%s%s.%s%s NOT IN (SELECT oid FROM %s) AND \n\
-	%s%s.%s%s != 0;\n", 	$2, $3, $2,
-				($5 == "pg_proc") ? "RegprocToOid(" : "",
-				$2, $3,
-				($5 == "pg_proc") ? ")" : "",
-				$5,
-				($5 == "pg_proc") ? "RegprocToOid(" : "",
-				$2, $3,
-				($5 == "pg_proc") ? ")" : "";
+WHERE	%s.%s != 0 AND \n\
+	NOT EXISTS(SELECT * FROM %s AS t1 WHERE t1.oid = %s.%s);\n",
+	$2, $3, $2,
+	$2, $3,
+	$5, $2, $3;
 	}'
+
+exit 0
