Improve findoidjoins to cover more cases.

Teach the program and script to deal with OID-array referencing columns,
which we now have several of.  Also, modify the recommended usage process
to specify that the program should be run against the regression database
rather than template1.  This lets it find numerous joins that cannot be
found in template1 because the relevant catalogs are entirely empty.

Together these changes add seventeen formerly-missed cases to the oidjoins
regression test.

1 files changed, 84 insertions, 11 deletions

diff --git a/src/tools/findoidjoins/findoidjoins.c b/src/tools/findoidjoins/findoidjoins.c
index 3af97c7a092..031a77fa70e 100644
--- a/src/tools/findoidjoins/findoidjoins.c
+++ b/src/tools/findoidjoins/findoidjoins.c
@@ -46,9 +46,7 @@ main(int argc, char **argv)
 
 	/* Get a list of relations that have OIDs */
 
-	resetPQExpBuffer(&sql);
-
-	appendPQExpBuffer(&sql, "%s",
+	printfPQExpBuffer(&sql, "%s",
 					  "SET search_path = public;"
 					  "SELECT c.relname, (SELECT nspname FROM "
 		"pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
@@ -68,9 +66,7 @@ main(int argc, char **argv)
 
 	/* Get a list of columns of OID type (or any OID-alias type) */
 
-	resetPQExpBuffer(&sql);
-
-	appendPQExpBuffer(&sql, "%s",
+	printfPQExpBuffer(&sql, "%s",
 					  "SELECT c.relname, "
 					  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
 					  "a.attname "
@@ -113,15 +109,15 @@ main(int argc, char **argv)
 			pk_relname = PQgetvalue(pkrel_res, pk, 0);
 			pk_nspname = PQgetvalue(pkrel_res, pk, 1);
 
-			resetPQExpBuffer(&sql);
-
-			appendPQExpBuffer(&sql,
+			printfPQExpBuffer(&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);
+							  fk_nspname, fk_relname,
+							  pk_nspname, pk_relname,
+							  fk_attname);
 
 			res = PQexec(conn, sql.data);
 			if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
@@ -139,8 +135,85 @@ main(int argc, char **argv)
 		}
 	}
 
-	PQclear(pkrel_res);
 	PQclear(fkrel_res);
+
+	/* Now, do the same for referencing columns that are arrays */
+
+	/* Get a list of columns of OID-array type (or any OID-alias type) */
+
+	printfPQExpBuffer(&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, "
+					  " 'pg_catalog.regconfig[]'::regtype, "
+					  " 'pg_catalog.regdictionary[]'::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);
+
+			printfPQExpBuffer(&sql,
+							  "SELECT	1 "
+							  "FROM \"%s\".\"%s\" t1, "
+							  "\"%s\".\"%s\" t2 "
+							  "WHERE t2.oid = ANY(t1.\"%s\")"
+							  "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(fkrel_res);
+
+	PQclear(pkrel_res);
+
 	PQfinish(conn);
 
 	termPQExpBuffer(&sql);