diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/test/regress/expected/oidjoins.out | 136 | ||||
| -rw-r--r-- | src/test/regress/sql/oidjoins.sql | 68 | ||||
| -rw-r--r-- | src/tools/findoidjoins/README | 57 | ||||
| -rw-r--r-- | src/tools/findoidjoins/findoidjoins.c | 95 | ||||
| -rwxr-xr-x | 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 348033354c4..a7426dde737 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 5be38cff58f..20ca7edd3ba 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 8bc11d0b4ca..c4a96e3c4bc 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 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); diff --git a/src/tools/findoidjoins/make_oidjoins_check b/src/tools/findoidjoins/make_oidjoins_check index 5c2742e01b5..09d283462c1 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 |