diff options
| -rw-r--r-- | contrib/findoidjoins/Makefile | 2 | ||||
| -rw-r--r-- | contrib/findoidjoins/README | 36 | ||||
| -rw-r--r-- | contrib/findoidjoins/findoidjoins.c | 3 | ||||
| -rwxr-xr-x | contrib/findoidjoins/make_oidjoins_check | 40 |
4 files changed, 50 insertions, 31 deletions
diff --git a/contrib/findoidjoins/Makefile b/contrib/findoidjoins/Makefile index bf98412ecfa..898f92c6cf2 100644 --- 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 index 9170832bde4..ef7b3048db2 100644 --- 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 index 6c0e2e19d15..77e8c7475db 100644 --- 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 index a17052f7301..63cb8c89719 100755 --- 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 |