diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
commit | 6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch) | |
tree | 657d8194422a5daccecfd42d654b8a245ef7b4c8 /src/tools/findoidjoins | |
parent | Initial commit. (diff) | |
download | postgresql-13-upstream.tar.xz postgresql-13-upstream.zip |
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/tools/findoidjoins')
-rw-r--r-- | src/tools/findoidjoins/.gitignore | 1 | ||||
-rw-r--r-- | src/tools/findoidjoins/Makefile | 27 | ||||
-rw-r--r-- | src/tools/findoidjoins/README | 248 | ||||
-rw-r--r-- | src/tools/findoidjoins/findoidjoins.c | 243 | ||||
-rwxr-xr-x | src/tools/findoidjoins/make_oidjoins_check | 80 |
5 files changed, 599 insertions, 0 deletions
diff --git a/src/tools/findoidjoins/.gitignore b/src/tools/findoidjoins/.gitignore new file mode 100644 index 0000000..21ec074 --- /dev/null +++ b/src/tools/findoidjoins/.gitignore @@ -0,0 +1 @@ +/findoidjoins diff --git a/src/tools/findoidjoins/Makefile b/src/tools/findoidjoins/Makefile new file mode 100644 index 0000000..aa6ca2f --- /dev/null +++ b/src/tools/findoidjoins/Makefile @@ -0,0 +1,27 @@ +#------------------------------------------------------------------------- +# +# Makefile for src/tools/findoidjoins +# +# Copyright (c) 2003-2020, PostgreSQL Global Development Group +# +# src/tools/findoidjoins/Makefile +# +#------------------------------------------------------------------------- + +subdir = src/tools/findoidjoins +top_builddir = ../../.. +include $(top_builddir)/src/Makefile.global + +override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS) +LDFLAGS_INTERNAL += $(libpq_pgport) + +OBJS = \ + findoidjoins.o + +all: findoidjoins + +findoidjoins: findoidjoins.o | submake-libpq submake-libpgport + $(CC) $(CFLAGS) findoidjoins.o $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X) + +clean distclean maintainer-clean: + rm -f findoidjoins$(X) $(OBJS) diff --git a/src/tools/findoidjoins/README b/src/tools/findoidjoins/README new file mode 100644 index 0000000..d009710 --- /dev/null +++ b/src/tools/findoidjoins/README @@ -0,0 +1,248 @@ +src/tools/findoidjoins/README + +findoidjoins +============ + +This program scans a database and prints oid fields (also reg* fields) +and the tables they join to. It is normally used to check the system +catalog join relationships (shown below for 11devel as of 2018-05-07). + +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. +Currently, 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, pg_shdepend.refobjid, +and pg_init_privs.objoid. +(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, +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. +(Typically we update it at the end of each development cycle.) + +NOTE: currently, 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 regression test. +You might also get output for pg_shdepend.refobjid and pg_shdescription.objoid, +neither of which should be added to the regression test. + +In short, the procedure is: + +1. make installcheck in src/test/regress +2. cd here, make +3. ./findoidjoins regression >foj.out +4. ./make_oidjoins_check foj.out >oidjoins.sql +5. paste foj.out below, removing the entries reported as duplicative + by make_oidjoins_check or described as bogus above +6. remove bogus tests in oidjoins.sql as per above +7. copy oidjoins.sql to src/test/regress/sql/, + and update oidjoins.out to match. +8. Review diffs to ensure they correspond to new catalog relationships, + then commit. (Sometimes, a pre-existing catalog relationship might + become newly visible here as a result of the regression tests populating + a catalog they didn't before. That's OK too.) + +--------------------------------------------------------------------------- + +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.aggcombinefn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggserialfn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggdeserialfn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggmtransfn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggminvtransfn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggmfinalfn => pg_catalog.pg_proc.oid +Join pg_catalog.pg_aggregate.aggsortop => pg_catalog.pg_operator.oid +Join pg_catalog.pg_aggregate.aggtranstype => pg_catalog.pg_type.oid +Join pg_catalog.pg_aggregate.aggmtranstype => pg_catalog.pg_type.oid +Join pg_catalog.pg_am.amhandler => pg_catalog.pg_proc.oid +Join pg_catalog.pg_amop.amopfamily => pg_catalog.pg_opfamily.oid +Join pg_catalog.pg_amop.amoplefttype => pg_catalog.pg_type.oid +Join pg_catalog.pg_amop.amoprighttype => pg_catalog.pg_type.oid +Join pg_catalog.pg_amop.amopopr => pg_catalog.pg_operator.oid +Join pg_catalog.pg_amop.amopmethod => pg_catalog.pg_am.oid +Join pg_catalog.pg_amop.amopsortfamily => pg_catalog.pg_opfamily.oid +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 +Join pg_catalog.pg_auth_members.roleid => pg_catalog.pg_authid.oid +Join pg_catalog.pg_auth_members.member => pg_catalog.pg_authid.oid +Join pg_catalog.pg_auth_members.grantor => pg_catalog.pg_authid.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.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 +Join pg_catalog.pg_class.reltoastrelid => 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.conparentid => pg_catalog.pg_constraint.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 +Join pg_catalog.pg_database.datdba => pg_catalog.pg_authid.oid +Join pg_catalog.pg_database.dattablespace => pg_catalog.pg_tablespace.oid +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_foreign_data_wrapper.fdwowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_foreign_server.srvowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_foreign_server.srvfdw => pg_catalog.pg_foreign_data_wrapper.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_init_privs.classoid => 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 +Join pg_catalog.pg_language.lanvalidator => pg_catalog.pg_proc.oid +Join pg_catalog.pg_largeobject.loid => pg_catalog.pg_largeobject_metadata.oid +Join pg_catalog.pg_largeobject_metadata.lomowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_namespace.nspowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_opclass.opcmethod => pg_catalog.pg_am.oid +Join pg_catalog.pg_opclass.opcnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_opclass.opcowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_opclass.opcfamily => pg_catalog.pg_opfamily.oid +Join pg_catalog.pg_opclass.opcintype => pg_catalog.pg_type.oid +Join pg_catalog.pg_opclass.opckeytype => pg_catalog.pg_type.oid +Join pg_catalog.pg_operator.oprnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_operator.oprowner => pg_catalog.pg_authid.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.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_opfamily.opfmethod => pg_catalog.pg_am.oid +Join pg_catalog.pg_opfamily.opfnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_opfamily.opfowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_partitioned_table.partrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_partitioned_table.partdefid => pg_catalog.pg_class.oid +Join pg_catalog.pg_policy.polrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_proc.pronamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_proc.proowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_proc.prolang => pg_catalog.pg_language.oid +Join pg_catalog.pg_proc.provariadic => pg_catalog.pg_type.oid +Join pg_catalog.pg_proc.prosupport => pg_catalog.pg_proc.oid +Join pg_catalog.pg_proc.prorettype => pg_catalog.pg_type.oid +Join pg_catalog.pg_range.rngtypid => pg_catalog.pg_type.oid +Join pg_catalog.pg_range.rngsubtype => pg_catalog.pg_type.oid +Join pg_catalog.pg_range.rngcollation => pg_catalog.pg_collation.oid +Join pg_catalog.pg_range.rngsubopc => pg_catalog.pg_opclass.oid +Join pg_catalog.pg_range.rngcanonical => pg_catalog.pg_proc.oid +Join pg_catalog.pg_range.rngsubdiff => pg_catalog.pg_proc.oid +Join pg_catalog.pg_rewrite.ev_class => pg_catalog.pg_class.oid +Join pg_catalog.pg_sequence.seqrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_sequence.seqtypid => pg_catalog.pg_type.oid +Join pg_catalog.pg_shdepend.refclassid => pg_catalog.pg_class.oid +Join pg_catalog.pg_shdescription.classoid => 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_statistic.staop4 => pg_catalog.pg_operator.oid +Join pg_catalog.pg_statistic.staop5 => pg_catalog.pg_operator.oid +Join pg_catalog.pg_statistic.stacoll1 => pg_catalog.pg_collation.oid +Join pg_catalog.pg_statistic.stacoll2 => pg_catalog.pg_collation.oid +Join pg_catalog.pg_statistic.stacoll3 => pg_catalog.pg_collation.oid +Join pg_catalog.pg_statistic.stacoll4 => pg_catalog.pg_collation.oid +Join pg_catalog.pg_statistic.stacoll5 => pg_catalog.pg_collation.oid +Join pg_catalog.pg_statistic_ext.stxrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_statistic_ext.stxnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_statistic_ext.stxowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_statistic_ext_data.stxoid => pg_catalog.pg_statistic_ext.oid +Join pg_catalog.pg_tablespace.spcowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_transform.trftype => pg_catalog.pg_type.oid +Join pg_catalog.pg_transform.trflang => pg_catalog.pg_language.oid +Join pg_catalog.pg_transform.trffromsql => pg_catalog.pg_proc.oid +Join pg_catalog.pg_transform.trftosql => pg_catalog.pg_proc.oid +Join pg_catalog.pg_trigger.tgrelid => pg_catalog.pg_class.oid +Join pg_catalog.pg_trigger.tgparentid => pg_catalog.pg_trigger.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 +Join pg_catalog.pg_ts_config_map.mapcfg => pg_catalog.pg_ts_config.oid +Join pg_catalog.pg_ts_config_map.mapdict => pg_catalog.pg_ts_dict.oid +Join pg_catalog.pg_ts_dict.dictnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_ts_dict.dictowner => pg_catalog.pg_authid.oid +Join pg_catalog.pg_ts_dict.dicttemplate => pg_catalog.pg_ts_template.oid +Join pg_catalog.pg_ts_parser.prsnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_ts_parser.prsstart => pg_catalog.pg_proc.oid +Join pg_catalog.pg_ts_parser.prstoken => pg_catalog.pg_proc.oid +Join pg_catalog.pg_ts_parser.prsend => pg_catalog.pg_proc.oid +Join pg_catalog.pg_ts_parser.prsheadline => pg_catalog.pg_proc.oid +Join pg_catalog.pg_ts_parser.prslextype => pg_catalog.pg_proc.oid +Join pg_catalog.pg_ts_template.tmplnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_ts_template.tmplinit => pg_catalog.pg_proc.oid +Join pg_catalog.pg_ts_template.tmpllexize => pg_catalog.pg_proc.oid +Join pg_catalog.pg_type.typnamespace => pg_catalog.pg_namespace.oid +Join pg_catalog.pg_type.typowner => pg_catalog.pg_authid.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.typarray => 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.typmodin => pg_catalog.pg_proc.oid +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_constraint.conexclop []=> pg_catalog.pg_operator.oid +Join pg_catalog.pg_index.indcollation []=> pg_catalog.pg_collation.oid +Join pg_catalog.pg_index.indclass []=> pg_catalog.pg_opclass.oid +Join pg_catalog.pg_partitioned_table.partclass []=> pg_catalog.pg_opclass.oid +Join pg_catalog.pg_partitioned_table.partcollation []=> pg_catalog.pg_collation.oid +Join pg_catalog.pg_proc.proargtypes []=> pg_catalog.pg_type.oid +Join pg_catalog.pg_proc.proallargtypes []=> pg_catalog.pg_type.oid + +--------------------------------------------------------------------------- + +Bruce Momjian (bruce@momjian.us) +Updated for 7.3 by Joe Conway (mail@joeconway.com) diff --git a/src/tools/findoidjoins/findoidjoins.c b/src/tools/findoidjoins/findoidjoins.c new file mode 100644 index 0000000..3d9ca26 --- /dev/null +++ b/src/tools/findoidjoins/findoidjoins.c @@ -0,0 +1,243 @@ +/* + * findoidjoins.c + * + * Copyright (c) 2002-2020, PostgreSQL Global Development Group + * + * src/tools/findoidjoins/findoidjoins.c + */ +#include "postgres_fe.h" + +#include "access/transam.h" +#include "catalog/pg_class_d.h" + +#include "common/connect.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); + } + + res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL); + if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) + { + fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn)); + exit(EXIT_FAILURE); + } + PQclear(res); + + /* Get a list of system relations that have OIDs */ + + printfPQExpBuffer(&sql, + "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 = " CppAsString2(RELKIND_RELATION) + " AND c.oid < '%u'" + " AND EXISTS(SELECT * FROM pg_attribute a" + " WHERE a.attrelid = c.oid AND a.attname = 'oid' " + " AND a.atttypid = 'oid'::regtype)" + "ORDER BY nspname, c.relname", + FirstNormalObjectId + ); + + 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 system columns of OID type (or any OID-alias type) */ + + printfPQExpBuffer(&sql, + "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 a.attname != 'oid'" + " AND c.relkind = " CppAsString2(RELKIND_RELATION) + " AND c.oid < '%u'" + " 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", + FirstNormalObjectId + ); + + 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 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(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 = " CppAsString2(RELKIND_RELATION) + " AND a.attrelid = c.oid" + " AND a.atttypid IN ('pg_catalog.oid[]'::regtype, " + " 'pg_catalog.oidvector'::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); + + exit(EXIT_SUCCESS); +} diff --git a/src/tools/findoidjoins/make_oidjoins_check b/src/tools/findoidjoins/make_oidjoins_check new file mode 100755 index 0000000..09d2834 --- /dev/null +++ b/src/tools/findoidjoins/make_oidjoins_check @@ -0,0 +1,80 @@ +#! /bin/sh + +# src/tools/findoidjoins/make_oidjoins_check + +# 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 +# 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} + +# 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 + +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 fields without multiple references. +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/src/tools/findoidjoins/make_oidjoins_check\n\ +--\n"; + } + $5 == "=>" \ + { + 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; + } + $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 |