From 795c382e8caf27f9db2fb09d12384b8183280fee Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 23 Apr 2011 19:33:04 -0400 Subject: [PATCH] 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. --- src/test/regress/expected/oidjoins.out | 136 +++++++++++++++++++++ src/test/regress/sql/oidjoins.sql | 68 +++++++++++ src/tools/findoidjoins/README | 57 ++++++--- src/tools/findoidjoins/findoidjoins.c | 95 ++++++++++++-- src/tools/findoidjoins/make_oidjoins_check | 22 +++- 5 files changed, 342 insertions(+), 36 deletions(-) diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out index 348033354c..a7426dde73 100644 --- a/src/test/regress/expected/oidjoins.out +++ b/src/test/regress/expected/oidjoins.out @@ -241,6 +241,14 @@ WHERE amproc != 0 AND ------+-------- (0 rows) +SELECT ctid, adrelid +FROM pg_catalog.pg_attrdef fk +WHERE adrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.adrelid); + ctid | adrelid +------+--------- +(0 rows) + SELECT ctid, attrelid FROM pg_catalog.pg_attribute fk WHERE attrelid != 0 AND @@ -305,6 +313,14 @@ WHERE reltype != 0 AND ------+--------- (0 rows) +SELECT ctid, reloftype +FROM pg_catalog.pg_class fk +WHERE reloftype != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.reloftype); + ctid | reloftype +------+----------- +(0 rows) + SELECT ctid, relowner FROM pg_catalog.pg_class fk WHERE relowner != 0 AND @@ -369,6 +385,14 @@ WHERE connamespace != 0 AND ------+-------------- (0 rows) +SELECT ctid, conrelid +FROM pg_catalog.pg_constraint fk +WHERE conrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.conrelid); + ctid | conrelid +------+---------- +(0 rows) + SELECT ctid, contypid FROM pg_catalog.pg_constraint fk WHERE contypid != 0 AND @@ -377,6 +401,22 @@ WHERE contypid != 0 AND ------+---------- (0 rows) +SELECT ctid, conindid +FROM pg_catalog.pg_constraint fk +WHERE conindid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.conindid); + ctid | conindid +------+---------- +(0 rows) + +SELECT ctid, confrelid +FROM pg_catalog.pg_constraint fk +WHERE confrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.confrelid); + ctid | confrelid +------+----------- +(0 rows) + SELECT ctid, connamespace FROM pg_catalog.pg_conversion fk WHERE connamespace != 0 AND @@ -449,6 +489,14 @@ WHERE classoid != 0 AND ------+---------- (0 rows) +SELECT ctid, enumtypid +FROM pg_catalog.pg_enum fk +WHERE enumtypid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.enumtypid); + ctid | enumtypid +------+----------- +(0 rows) + SELECT ctid, extowner FROM pg_catalog.pg_extension fk WHERE extowner != 0 AND @@ -481,6 +529,22 @@ WHERE indrelid != 0 AND ------+---------- (0 rows) +SELECT ctid, inhrelid +FROM pg_catalog.pg_inherits fk +WHERE inhrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.inhrelid); + ctid | inhrelid +------+---------- +(0 rows) + +SELECT ctid, inhparent +FROM pg_catalog.pg_inherits fk +WHERE inhparent != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.inhparent); + ctid | inhparent +------+----------- +(0 rows) + SELECT ctid, lanowner FROM pg_catalog.pg_language fk WHERE lanowner != 0 AND @@ -777,6 +841,46 @@ WHERE spcowner != 0 AND ------+---------- (0 rows) +SELECT ctid, tgrelid +FROM pg_catalog.pg_trigger fk +WHERE tgrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgrelid); + ctid | tgrelid +------+--------- +(0 rows) + +SELECT ctid, tgfoid +FROM pg_catalog.pg_trigger fk +WHERE tgfoid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.tgfoid); + ctid | tgfoid +------+-------- +(0 rows) + +SELECT ctid, tgconstrrelid +FROM pg_catalog.pg_trigger fk +WHERE tgconstrrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgconstrrelid); + ctid | tgconstrrelid +------+--------------- +(0 rows) + +SELECT ctid, tgconstrindid +FROM pg_catalog.pg_trigger fk +WHERE tgconstrindid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgconstrindid); + ctid | tgconstrindid +------+--------------- +(0 rows) + +SELECT ctid, tgconstraint +FROM pg_catalog.pg_trigger fk +WHERE tgconstraint != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_constraint pk WHERE pk.oid = fk.tgconstraint); + ctid | tgconstraint +------+-------------- +(0 rows) + SELECT ctid, cfgnamespace FROM pg_catalog.pg_ts_config fk WHERE cfgnamespace != 0 AND @@ -1025,3 +1129,35 @@ WHERE typcollation != 0 AND ------+-------------- (0 rows) +SELECT ctid, conpfeqop +FROM (SELECT ctid, unnest(conpfeqop) AS conpfeqop FROM pg_catalog.pg_constraint) fk +WHERE conpfeqop != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conpfeqop); + ctid | conpfeqop +------+----------- +(0 rows) + +SELECT ctid, conppeqop +FROM (SELECT ctid, unnest(conppeqop) AS conppeqop FROM pg_catalog.pg_constraint) fk +WHERE conppeqop != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conppeqop); + ctid | conppeqop +------+----------- +(0 rows) + +SELECT ctid, conffeqop +FROM (SELECT ctid, unnest(conffeqop) AS conffeqop FROM pg_catalog.pg_constraint) fk +WHERE conffeqop != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conffeqop); + ctid | conffeqop +------+----------- +(0 rows) + +SELECT ctid, proallargtypes +FROM (SELECT ctid, unnest(proallargtypes) AS proallargtypes FROM pg_catalog.pg_proc) fk +WHERE proallargtypes != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.proallargtypes); + ctid | proallargtypes +------+---------------- +(0 rows) + diff --git a/src/test/regress/sql/oidjoins.sql b/src/test/regress/sql/oidjoins.sql index 5be38cff58..20ca7edd3b 100644 --- a/src/test/regress/sql/oidjoins.sql +++ b/src/test/regress/sql/oidjoins.sql @@ -121,6 +121,10 @@ SELECT ctid, amproc FROM pg_catalog.pg_amproc fk WHERE amproc != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amproc); +SELECT ctid, adrelid +FROM pg_catalog.pg_attrdef fk +WHERE adrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.adrelid); SELECT ctid, attrelid FROM pg_catalog.pg_attribute fk WHERE attrelid != 0 AND @@ -153,6 +157,10 @@ SELECT ctid, reltype FROM pg_catalog.pg_class fk WHERE reltype != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.reltype); +SELECT ctid, reloftype +FROM pg_catalog.pg_class fk +WHERE reloftype != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.reloftype); SELECT ctid, relowner FROM pg_catalog.pg_class fk WHERE relowner != 0 AND @@ -185,10 +193,22 @@ SELECT ctid, connamespace FROM pg_catalog.pg_constraint fk WHERE connamespace != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_namespace pk WHERE pk.oid = fk.connamespace); +SELECT ctid, conrelid +FROM pg_catalog.pg_constraint fk +WHERE conrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.conrelid); SELECT ctid, contypid FROM pg_catalog.pg_constraint fk WHERE contypid != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.contypid); +SELECT ctid, conindid +FROM pg_catalog.pg_constraint fk +WHERE conindid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.conindid); +SELECT ctid, confrelid +FROM pg_catalog.pg_constraint fk +WHERE confrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.confrelid); SELECT ctid, connamespace FROM pg_catalog.pg_conversion fk WHERE connamespace != 0 AND @@ -225,6 +245,10 @@ SELECT ctid, classoid FROM pg_catalog.pg_description fk WHERE classoid != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.classoid); +SELECT ctid, enumtypid +FROM pg_catalog.pg_enum fk +WHERE enumtypid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.enumtypid); SELECT ctid, extowner FROM pg_catalog.pg_extension fk WHERE extowner != 0 AND @@ -241,6 +265,14 @@ SELECT ctid, indrelid FROM pg_catalog.pg_index fk WHERE indrelid != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.indrelid); +SELECT ctid, inhrelid +FROM pg_catalog.pg_inherits fk +WHERE inhrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.inhrelid); +SELECT ctid, inhparent +FROM pg_catalog.pg_inherits fk +WHERE inhparent != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.inhparent); SELECT ctid, lanowner FROM pg_catalog.pg_language fk WHERE lanowner != 0 AND @@ -389,6 +421,26 @@ SELECT ctid, spcowner FROM pg_catalog.pg_tablespace fk WHERE spcowner != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.spcowner); +SELECT ctid, tgrelid +FROM pg_catalog.pg_trigger fk +WHERE tgrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgrelid); +SELECT ctid, tgfoid +FROM pg_catalog.pg_trigger fk +WHERE tgfoid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.tgfoid); +SELECT ctid, tgconstrrelid +FROM pg_catalog.pg_trigger fk +WHERE tgconstrrelid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgconstrrelid); +SELECT ctid, tgconstrindid +FROM pg_catalog.pg_trigger fk +WHERE tgconstrindid != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgconstrindid); +SELECT ctid, tgconstraint +FROM pg_catalog.pg_trigger fk +WHERE tgconstraint != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_constraint pk WHERE pk.oid = fk.tgconstraint); SELECT ctid, cfgnamespace FROM pg_catalog.pg_ts_config fk WHERE cfgnamespace != 0 AND @@ -513,3 +565,19 @@ SELECT ctid, typcollation FROM pg_catalog.pg_type fk WHERE typcollation != 0 AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_collation pk WHERE pk.oid = fk.typcollation); +SELECT ctid, conpfeqop +FROM (SELECT ctid, unnest(conpfeqop) AS conpfeqop FROM pg_catalog.pg_constraint) fk +WHERE conpfeqop != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conpfeqop); +SELECT ctid, conppeqop +FROM (SELECT ctid, unnest(conppeqop) AS conppeqop FROM pg_catalog.pg_constraint) fk +WHERE conppeqop != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conppeqop); +SELECT ctid, conffeqop +FROM (SELECT ctid, unnest(conffeqop) AS conffeqop FROM pg_catalog.pg_constraint) fk +WHERE conffeqop != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conffeqop); +SELECT ctid, proallargtypes +FROM (SELECT ctid, unnest(proallargtypes) AS proallargtypes FROM pg_catalog.pg_proc) fk +WHERE proallargtypes != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.proallargtypes); diff --git a/src/tools/findoidjoins/README b/src/tools/findoidjoins/README index 8bc11d0b4c..c4a96e3c4b 100644 --- a/src/tools/findoidjoins/README +++ b/src/tools/findoidjoins/README @@ -4,39 +4,41 @@ 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. +and the tables they join to. It is normally used to check the system +catalog join relationships (shown below for 9.1devel). -Run on an empty database, it returns the system join relationships (shown -below for 9.1devel). 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 9.1devel, the *only* fields that should join to more -than one target are pg_description.objoid, pg_depend.objid, -pg_depend.refobjid, pg_shdescription.objoid, pg_shdepend.objid, and -pg_shdepend.refobjid. (Running make_oidjoins_check is an easy way to spot -fields joining to more than one table, BTW.) NOTE: in an empty database, -findoidjoins may not report joins for pg_shdescription and pg_shdepend for -lack of any entries there. +Historically this has been run against an empty database such as template1, +but there's a problem with that approach: some of the catalogs are empty +and so their joining columns won't show up in the output. Current practice +is to run it against the regression-test database, which populates the +catalogs in interesting ways. + +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 9.1devel, the *only* fields that should join to more than one target +table are pg_description.objoid, pg_depend.objid, pg_depend.refobjid, +pg_shdescription.objoid, pg_shdepend.objid, and pg_shdepend.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. +Note that fields joining to more than one table are NOT processed, +just reported as linking to more than one table. 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...) +(Typically we update it at the end of each development cycle.) -NOTE: in 9.1devel, make_oidjoins_check produces two bogus join checks: +NOTE: as of 9.1devel, 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. You might also get output for pg_shdepend.refobjid and pg_shdescription.objoid, -neither of which should be added. +neither of which should be added to the regress test. --------------------------------------------------------------------------- @@ -70,6 +72,7 @@ Join pg_catalog.pg_amproc.amprocfamily => pg_catalog.pg_opfamily.oid Join pg_catalog.pg_amproc.amproclefttype => pg_catalog.pg_type.oid Join pg_catalog.pg_amproc.amprocrighttype => pg_catalog.pg_type.oid Join pg_catalog.pg_amproc.amproc => pg_catalog.pg_proc.oid +Join pg_catalog.pg_attrdef.adrelid => pg_catalog.pg_class.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_attribute.attcollation => pg_catalog.pg_collation.oid @@ -78,6 +81,7 @@ 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.reloftype => pg_catalog.pg_type.oid Join pg_catalog.pg_class.relowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_class.relam => pg_catalog.pg_am.oid Join pg_catalog.pg_class.reltablespace => pg_catalog.pg_tablespace.oid @@ -86,7 +90,10 @@ Join pg_catalog.pg_class.reltoastidxid => pg_catalog.pg_class.oid Join pg_catalog.pg_collation.collnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_collation.collowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_constraint.connamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_constraint.conrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_constraint.contypid => pg_catalog.pg_type.oid +Join pg_catalog.pg_constraint.conindid => pg_catalog.pg_class.oid +Join pg_catalog.pg_constraint.confrelid => pg_catalog.pg_class.oid Join pg_catalog.pg_conversion.connamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_conversion.conowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_conversion.conproc => pg_catalog.pg_proc.oid @@ -96,10 +103,13 @@ Join pg_catalog.pg_db_role_setting.setdatabase => pg_catalog.pg_database.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_enum.enumtypid => pg_catalog.pg_type.oid Join pg_catalog.pg_extension.extowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_extension.extnamespace => pg_catalog.pg_namespace.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_inherits.inhrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_inherits.inhparent => pg_catalog.pg_class.oid Join pg_catalog.pg_language.lanowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_language.lanplcallfoid => pg_catalog.pg_proc.oid Join pg_catalog.pg_language.laninline => pg_catalog.pg_proc.oid @@ -137,6 +147,11 @@ 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_tablespace.spcowner => pg_catalog.pg_authid.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_trigger.tgconstrrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_trigger.tgconstrindid => pg_catalog.pg_class.oid +Join pg_catalog.pg_trigger.tgconstraint => pg_catalog.pg_constraint.oid Join pg_catalog.pg_ts_config.cfgnamespace => pg_catalog.pg_namespace.oid Join pg_catalog.pg_ts_config.cfgowner => pg_catalog.pg_authid.oid Join pg_catalog.pg_ts_config.cfgparser => pg_catalog.pg_ts_parser.oid @@ -168,6 +183,10 @@ Join pg_catalog.pg_type.typmodout => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typanalyze => pg_catalog.pg_proc.oid Join pg_catalog.pg_type.typbasetype => pg_catalog.pg_type.oid Join pg_catalog.pg_type.typcollation => pg_catalog.pg_collation.oid +Join pg_catalog.pg_constraint.conpfeqop []=> pg_catalog.pg_operator.oid +Join pg_catalog.pg_constraint.conppeqop []=> pg_catalog.pg_operator.oid +Join pg_catalog.pg_constraint.conffeqop []=> pg_catalog.pg_operator.oid +Join pg_catalog.pg_proc.proallargtypes []=> pg_catalog.pg_type.oid --------------------------------------------------------------------------- diff --git a/src/tools/findoidjoins/findoidjoins.c b/src/tools/findoidjoins/findoidjoins.c index 3af97c7a09..031a77fa70 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); diff --git a/src/tools/findoidjoins/make_oidjoins_check b/src/tools/findoidjoins/make_oidjoins_check index 5c2742e01b..09d283462c 100755 --- a/src/tools/findoidjoins/make_oidjoins_check +++ b/src/tools/findoidjoins/make_oidjoins_check @@ -2,7 +2,7 @@ # src/tools/findoidjoins/make_oidjoins_check -# You first run findoidjoins on the template1 database, and send that +# You first run findoidjoins on the regression database, then 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 @@ -12,17 +12,16 @@ # 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. +TMP="${TMPDIR:-/tmp}/make_oidjoins_check.$$" OMASK="`umask`" umask 077 if ! mkdir $TMP then echo "Can't create temporary directory $TMP." 1>&2 exit 1 fi +trap "rm -rf $TMP" 0 1 2 3 15 umask "$OMASK" unset OMASK @@ -40,7 +39,7 @@ if [ -s $DUPSFILE ] ; then cat $DUPSFILE 1>&2 fi -# Get the non-multiply-referenced fields. +# Get the fields without multiple references. cat $INPUTFILE | while read LINE do set -- $LINE @@ -49,7 +48,7 @@ done >$NONDUPSFILE # Generate the output. cat $NONDUPSFILE | -$AWK -F'[ \.]' '\ +$AWK -F'[ .]' '\ BEGIN \ { printf "\ @@ -57,6 +56,7 @@ $AWK -F'[ \.]' '\ -- This is created by pgsql/src/tools/findoidjoins/make_oidjoins_check\n\ --\n"; } + $5 == "=>" \ { printf "\ SELECT ctid, %s\n\ @@ -65,6 +65,16 @@ 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; + } + $5 == "[]=>" \ + { + printf "\ +SELECT ctid, %s\n\ +FROM (SELECT ctid, unnest(%s) AS %s 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, $4, $4, $2, $3, $4, + $6, $7, $4; }' exit 0 -- 2.40.0