diff options
Diffstat (limited to 'contrib')
| -rw-r--r-- | contrib/Makefile | 3 | ||||
| -rw-r--r-- | contrib/README | 5 | ||||
| -rw-r--r-- | contrib/findoidjoins/Makefile | 20 | ||||
| -rw-r--r-- | contrib/findoidjoins/README.findoidjoins | 119 | ||||
| -rw-r--r-- | contrib/findoidjoins/findoidjoins.c | 147 | ||||
| -rwxr-xr-x | contrib/findoidjoins/make_oidjoins_check | 68 |
6 files changed, 1 insertions, 361 deletions
diff --git a/contrib/Makefile b/contrib/Makefile index 893a2c6e0b9..0641b50872d 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -1,4 +1,4 @@ -# $PostgreSQL: pgsql/contrib/Makefile,v 1.55 2005/06/22 22:56:25 tgl Exp $ +# $PostgreSQL: pgsql/contrib/Makefile,v 1.56 2005/06/23 02:33:25 tgl Exp $ subdir = contrib top_builddir = .. @@ -13,7 +13,6 @@ WANTED_DIRS = \ dbmirror \ dbsize \ earthdistance \ - findoidjoins \ fulltextindex \ fuzzystrmatch \ intagg \ diff --git a/contrib/README b/contrib/README index f14481c7bae..3e1ae62b00c 100644 --- a/contrib/README +++ b/contrib/README @@ -62,11 +62,6 @@ earthdistance - Operator for computing earth distance for two points by Hal Snyder <hal@vailsys.com> -findoidjoins - - Finds the joins used by oid columns by examining the actual - values in the oid columns and row oids. - by Bruce Momjian <pgman@candle.pha.pa.us> - fulltextindex - Full text indexing using triggers by Maarten Boekhold <maartenb@dutepp0.et.tudelft.nl> diff --git a/contrib/findoidjoins/Makefile b/contrib/findoidjoins/Makefile deleted file mode 100644 index a15378a2a23..00000000000 --- a/contrib/findoidjoins/Makefile +++ /dev/null @@ -1,20 +0,0 @@ -# $PostgreSQL: pgsql/contrib/findoidjoins/Makefile,v 1.17 2005/03/25 18:17:10 momjian Exp $ - -PROGRAM = findoidjoins -OBJS = findoidjoins.o - -PG_CPPFLAGS = -I$(libpq_srcdir) -PG_LIBS = $(libpq_pgport) - -SCRIPTS = make_oidjoins_check -DOCS = README.findoidjoins - -ifdef USE_PGXS -PGXS = $(shell pg_config --pgxs) -include $(PGXS) -else -subdir = contrib/findoidjoins -top_builddir = ../.. -include $(top_builddir)/src/Makefile.global -include $(top_srcdir)/contrib/contrib-global.mk -endif diff --git a/contrib/findoidjoins/README.findoidjoins b/contrib/findoidjoins/README.findoidjoins deleted file mode 100644 index a96641430ab..00000000000 --- a/contrib/findoidjoins/README.findoidjoins +++ /dev/null @@ -1,119 +0,0 @@ - - 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.0). 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.0, 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.0, make_oidjoins_check produces one bogus join check: -Join pg_catalog.pg_class.relfilenode => pg_catalog.pg_class.oid -This is an artifact and should not be added to the oidjoins regress test. -Also beware of any claim that pg_database.datlastsysoid joins to anything; -this does not actually happen in 8.0, but it did happen before and might -happen again in future, depending on what operation initdb does last. - ---------------------------------------------------------------------------- - -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.aggtranstype => pg_catalog.pg_type.oid -Join pg_catalog.pg_am.amgettuple => pg_catalog.pg_proc.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.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/contrib/findoidjoins/findoidjoins.c b/contrib/findoidjoins/findoidjoins.c deleted file mode 100644 index 3afcd7c3c35..00000000000 --- a/contrib/findoidjoins/findoidjoins.c +++ /dev/null @@ -1,147 +0,0 @@ -/* - * findoidjoins.c - * - * Copyright (c) 2002-2005, PostgreSQL Global Development Group - * - * $PostgreSQL: pgsql/contrib/findoidjoins/findoidjoins.c,v 1.25 2005/01/01 05:43:05 momjian 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/contrib/findoidjoins/make_oidjoins_check b/contrib/findoidjoins/make_oidjoins_check deleted file mode 100755 index b6a720a5048..00000000000 --- a/contrib/findoidjoins/make_oidjoins_check +++ /dev/null @@ -1,68 +0,0 @@ -#! /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 |