findoidjoins patch (was Re: [HACKERS] findoidjoins) - Mailing list pgsql-patches
| From | Joe Conway |
|---|---|
| Subject | findoidjoins patch (was Re: [HACKERS] findoidjoins) |
| Date | |
| Msg-id | 3D76EABF.2070900@joeconway.com Whole thread Raw |
| Responses |
Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)
Re: findoidjoins patch (was Re: [HACKERS] findoidjoins) |
| List | pgsql-patches |
Tom Lane wrote:
> I'd be inclined to reproduce the original behavior. findoidjoins is
> pretty slow already, and I don't much want to slow it down more in order
> to provide info that's useless for the primary purpose.
Here's take two. It produces results similar to the previous version,
but using libpq and schema aware queries.
> use it for checking the stuff in pg_catalog. So I'm not at all sure why
> I made that note. Do you get a plausible set of joins out of your
> version?
Looks reasonable to me. I attached the outputs of findoidjoins and
make_oidjoins_check for review as well.
Please review and commit, or kick back to me if more work is needed.
Thanks,
Joe
Index: contrib/findoidjoins/Makefile
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/Makefile,v
retrieving revision 1.13
diff -c -r1.13 Makefile
*** contrib/findoidjoins/Makefile 6 Sep 2001 10:49:29 -0000 1.13
--- contrib/findoidjoins/Makefile 4 Sep 2002 23:36:27 -0000
***************
*** 1,5 ****
- # $Header: /opt/src/cvs/pgsql-server/contrib/findoidjoins/Makefile,v 1.13 2001/09/06 10:49:29 petere Exp $
-
subdir = contrib/findoidjoins
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
--- 1,3 ----
***************
*** 7,17 ****
PROGRAM = findoidjoins
OBJS = findoidjoins.o
! libpgeasy_srcdir = $(top_srcdir)/src/interfaces/libpgeasy
! libpgeasy_builddir = $(top_builddir)/src/interfaces/libpgeasy
!
! PG_CPPFLAGS = -I$(libpgeasy_srcdir) -I$(libpq_srcdir)
! PG_LIBS = -L$(libpgeasy_builddir) -lpgeasy $(libpq)
SCRIPTS = make_oidjoins_check
DOCS = README.findoidjoins
--- 5,12 ----
PROGRAM = findoidjoins
OBJS = findoidjoins.o
! PG_CPPFLAGS = -I$(libpq_srcdir)
! PG_LIBS = $(libpq)
SCRIPTS = make_oidjoins_check
DOCS = README.findoidjoins
Index: contrib/findoidjoins/README.findoidjoins
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/README.findoidjoins,v
retrieving revision 1.5
diff -c -r1.5 README.findoidjoins
*** contrib/findoidjoins/README.findoidjoins 25 Apr 2002 02:56:55 -0000 1.5
--- contrib/findoidjoins/README.findoidjoins 5 Sep 2002 04:42:21 -0000
***************
*** 1,24 ****
findoidjoins
! This program scans a database, and prints oid fields (also regproc, regclass
! and regtype 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, such as template1.
!
! Uses pgeasy library.
Run on an empty database, it returns the system join relationships (shown
! below for 7.2). 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 7.2, the *only* field that should join to more
! than one target is pg_description.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 REGPROC 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"
--- 1,22 ----
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.
Run on an empty database, it returns the system join relationships (shown
! below for 7.3). 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 7.3, 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* columns 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"
***************
*** 27,43 ****
(Ideally we'd just regenerate the script as part of the regression
tests themselves, but that seems too slow...)
! NOTE: in 7.2, make_oidjoins_check produces one bogus join check, for
pg_class.relfilenode => pg_class.oid. This is an artifact and should not
be added to the oidjoins regress test.
---------------------------------------------------------------------------
!
Join pg_aggregate.aggtransfn => pg_proc.oid
Join pg_aggregate.aggfinalfn => pg_proc.oid
- Join pg_aggregate.aggbasetype => pg_type.oid
Join pg_aggregate.aggtranstype => pg_type.oid
- Join pg_aggregate.aggfinaltype => pg_type.oid
Join pg_am.amgettuple => pg_proc.oid
Join pg_am.aminsert => pg_proc.oid
Join pg_am.ambeginscan => pg_proc.oid
--- 25,39 ----
(Ideally we'd just regenerate the script as part of the regression
tests themselves, but that seems too slow...)
! NOTE: in 7.3, make_oidjoins_check produces one bogus join check, for
pg_class.relfilenode => pg_class.oid. This is an artifact and should not
be added to the oidjoins regress test.
---------------------------------------------------------------------------
! Join pg_aggregate.aggfnoid => pg_proc.oid
Join pg_aggregate.aggtransfn => pg_proc.oid
Join pg_aggregate.aggfinalfn => pg_proc.oid
Join pg_aggregate.aggtranstype => pg_type.oid
Join pg_am.amgettuple => pg_proc.oid
Join pg_am.aminsert => pg_proc.oid
Join pg_am.ambeginscan => pg_proc.oid
***************
*** 54,68 ****
--- 50,95 ----
Join pg_amproc.amproc => pg_proc.oid
Join pg_attribute.attrelid => pg_class.oid
Join pg_attribute.atttypid => pg_type.oid
+ Join pg_cast.castsource => pg_type.oid
+ Join pg_cast.casttarget => pg_type.oid
+ Join pg_cast.castfunc => pg_proc.oid
+ Join pg_class.relnamespace => pg_namespace.oid
Join pg_class.reltype => pg_type.oid
Join pg_class.relam => pg_am.oid
+ Join pg_class.relfilenode => pg_class.oid
Join pg_class.reltoastrelid => pg_class.oid
Join pg_class.reltoastidxid => pg_class.oid
+ Join pg_conversion.connamespace => pg_namespace.oid
+ Join pg_conversion.conproc => pg_proc.oid
+ Join pg_database.datlastsysoid => pg_conversion.oid
+ Join pg_depend.classid => pg_class.oid
+ Join pg_depend.objid => pg_conversion.oid
+ Join pg_depend.objid => pg_rewrite.oid
+ Join pg_depend.objid => pg_type.oid
+ Join pg_depend.refclassid => pg_class.oid
+ Join pg_depend.refobjid => pg_cast.oid
+ Join pg_depend.refobjid => pg_class.oid
+ Join pg_depend.refobjid => pg_language.oid
+ Join pg_depend.refobjid => pg_namespace.oid
+ Join pg_depend.refobjid => pg_opclass.oid
+ Join pg_depend.refobjid => pg_operator.oid
+ Join pg_depend.refobjid => pg_proc.oid
+ Join pg_depend.refobjid => pg_trigger.oid
+ Join pg_depend.refobjid => pg_type.oid
+ Join pg_description.objoid => pg_am.oid
+ Join pg_description.objoid => pg_database.oid
+ Join pg_description.objoid => pg_language.oid
+ Join pg_description.objoid => pg_namespace.oid
+ Join pg_description.objoid => pg_proc.oid
+ Join pg_description.objoid => pg_type.oid
Join pg_description.classoid => pg_class.oid
Join pg_index.indexrelid => pg_class.oid
Join pg_index.indrelid => pg_class.oid
+ Join pg_language.lanvalidator => pg_proc.oid
Join pg_opclass.opcamid => pg_am.oid
+ Join pg_opclass.opcnamespace => pg_namespace.oid
Join pg_opclass.opcintype => pg_type.oid
+ Join pg_operator.oprnamespace => pg_namespace.oid
Join pg_operator.oprleft => pg_type.oid
Join pg_operator.oprright => pg_type.oid
Join pg_operator.oprresult => pg_type.oid
***************
*** 70,94 ****
Join pg_operator.oprnegate => pg_operator.oid
Join pg_operator.oprlsortop => pg_operator.oid
Join pg_operator.oprrsortop => pg_operator.oid
Join pg_operator.oprcode => pg_proc.oid
Join pg_operator.oprrest => pg_proc.oid
Join pg_operator.oprjoin => pg_proc.oid
Join pg_proc.prolang => pg_language.oid
Join pg_proc.prorettype => pg_type.oid
Join pg_rewrite.ev_class => pg_class.oid
- Join pg_statistic.starelid => pg_class.oid
- Join pg_statistic.staop1 => pg_operator.oid
- Join pg_statistic.staop2 => pg_operator.oid
- Join pg_statistic.staop3 => pg_operator.oid
Join pg_trigger.tgrelid => pg_class.oid
Join pg_trigger.tgfoid => pg_proc.oid
Join pg_type.typrelid => pg_class.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)
--- 97,119 ----
Join pg_operator.oprnegate => pg_operator.oid
Join pg_operator.oprlsortop => pg_operator.oid
Join pg_operator.oprrsortop => pg_operator.oid
+ Join pg_operator.oprltcmpop => pg_operator.oid
+ Join pg_operator.oprgtcmpop => pg_operator.oid
Join pg_operator.oprcode => pg_proc.oid
Join pg_operator.oprrest => pg_proc.oid
Join pg_operator.oprjoin => pg_proc.oid
+ Join pg_proc.pronamespace => pg_namespace.oid
Join pg_proc.prolang => pg_language.oid
Join pg_proc.prorettype => pg_type.oid
Join pg_rewrite.ev_class => pg_class.oid
Join pg_trigger.tgrelid => pg_class.oid
Join pg_trigger.tgfoid => pg_proc.oid
+ Join pg_type.typnamespace => pg_namespace.oid
Join pg_type.typrelid => pg_class.oid
Join pg_type.typelem => pg_type.oid
Join pg_type.typinput => pg_proc.oid
Join pg_type.typoutput => pg_proc.oid
---------------------------------------------------------------------------
Bruce Momjian (root@candle.pha.pa.us)
+ Updated for 7.3 by Joe Conway (mail@joeconway.com)
Index: contrib/findoidjoins/findoidjoins.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/findoidjoins/findoidjoins.c,v
retrieving revision 1.17
diff -c -r1.17 findoidjoins.c
*** contrib/findoidjoins/findoidjoins.c 4 Sep 2002 20:31:06 -0000 1.17
--- contrib/findoidjoins/findoidjoins.c 5 Sep 2002 04:51:16 -0000
***************
*** 1,109 ****
/*
! * findoidjoins.c, requires src/interfaces/libpgeasy
*
*/
- #include "postgres_fe.h"
! #include "libpq-fe.h"
! #include "halt.h"
! #include "libpgeasy.h"
! PGresult *attres,
! *relres;
int
main(int argc, char **argv)
{
! char query[4000];
! char relname[256];
! char relname2[256];
! char attname[256];
! char typname[256];
! int count;
! char optstr[256];
if (argc != 2)
! halt("Usage: %s database\n", argv[0]);
! snprintf(optstr, 256, "dbname=%s", argv[1]);
! connectdb(optstr);
! on_error_continue();
! on_error_stop();
! doquery("BEGIN WORK");
! doquery("\
! DECLARE c_attributes BINARY CURSOR FOR \
! SELECT typname, relname, a.attname \
! FROM pg_class c, pg_attribute a, pg_type t \
! WHERE a.attnum > 0 AND \
! relkind = 'r' AND \
! (typname = 'oid' OR \
! typname = 'regproc' OR \
! typname = 'regclass' OR \
! typname = 'regtype') AND \
! a.attrelid = c.oid AND \
! a.atttypid = t.oid \
! ORDER BY 2, a.attnum ; \
! ");
! doquery("FETCH ALL IN c_attributes");
! attres = get_result();
!
! doquery("\
! DECLARE c_relations BINARY CURSOR FOR \
! SELECT relname \
! FROM pg_class c \
! WHERE relkind = 'r' AND relhasoids \
! ORDER BY 1; \
! ");
! doquery("FETCH ALL IN c_relations");
! relres = get_result();
! set_result(attres);
! while (fetch(typname, relname, attname) != END_OF_TUPLES)
{
! set_result(relres);
! reset_fetch();
! while (fetch(relname2) != END_OF_TUPLES)
! {
! unset_result(relres);
! if (strcmp(typname, "oid") == 0)
! snprintf(query, 4000, "\
! DECLARE c_matches BINARY CURSOR FOR \
! SELECT count(*)::int4 \
! FROM \"%s\" t1, \"%s\" t2 \
! WHERE t1.\"%s\" = t2.oid ",
! relname, relname2, attname);
! else
! sprintf(query, 4000, "\
! DECLARE c_matches BINARY CURSOR FOR \
! SELECT count(*)::int4 \
! FROM \"%s\" t1, \"%s\" t2 \
! WHERE t1.\"%s\"::oid = t2.oid ",
! relname, relname2, attname);
!
! doquery(query);
! doquery("FETCH ALL IN c_matches");
! fetch(&count);
! if (count != 0)
! printf("Join %s.%s => %s.oid\n", relname, attname, relname2);
! doquery("CLOSE c_matches");
! set_result(relres);
! }
! set_result(attres);
}
! set_result(relres);
! doquery("CLOSE c_relations");
! PQclear(relres);
!
! set_result(attres);
! doquery("CLOSE c_attributes");
! PQclear(attres);
! unset_result(attres);
! doquery("COMMIT WORK");
! disconnectdb();
! return 0;
}
--- 1,152 ----
/*
! * findoidjoins
! *
! * Copyright 2002 by PostgreSQL Global Development Group
! *
! * Permission to use, copy, modify, and distribute this software and its
! * documentation for any purpose, without fee, and without a written agreement
! * is hereby granted, provided that the above copyright notice and this
! * paragraph and the following two paragraphs appear in all copies.
! *
! * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
! * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
! * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
! * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
! * POSSIBILITY OF SUCH DAMAGE.
! *
! * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
! * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
! * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
! * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
! * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
*
*/
! #include <stdlib.h>
! #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 *fk_typname;
! 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);
! }
!
! termPQExpBuffer(&sql);
! initPQExpBuffer(&sql);
! appendPQExpBuffer(&sql, "%s",
! "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;
+
+ termPQExpBuffer(&sql);
+ initPQExpBuffer(&sql);
! appendPQExpBuffer(&sql, "%s",
! "SELECT c.relname, "
! "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
! "a.attname, "
! "t.typname "
! "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, pg_catalog.pg_type t "
! "WHERE a.attnum > 0 AND c.relkind = 'r' "
! "AND t.typnamespace IN (SELECT n.oid FROM pg_catalog.pg_namespace n WHERE nspname LIKE 'pg\\_%') "
! "AND (t.typname = 'oid' OR t.typname LIKE 'reg%') "
! "AND a.attrelid = c.oid "
! "AND a.atttypid = t.oid "
! "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;
!
! termPQExpBuffer(&sql);
! initPQExpBuffer(&sql);
!
! 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);
! fk_typname = PQgetvalue(fkrel_res, fk, 3);
!
! for (pk = 0; pk < PQntuples(pkrel_res); pk++)
! {
! pk_relname = PQgetvalue(pkrel_res, pk, 0);
! pk_nspname = PQgetvalue(pkrel_res, pk, 1);
!
! appendPQExpBuffer(&sql,
! "SELECT 1 "
! "FROM \"%s\".\"%s\" t1, "
! "\"%s\".\"%s\" t2 "
! "WHERE t1.\"%s\"::oid = t2.oid",
! 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.oid\n",
! fk_relname, fk_attname, pk_relname);
!
! PQclear(res);
!
! termPQExpBuffer(&sql);
! initPQExpBuffer(&sql);
! }
! }
! PQclear(pkrel_res);
! PQclear(fkrel_res);
! PQfinish(conn);
! exit(EXIT_SUCCESS);
}
Join pg_aggregate.aggfnoid => pg_proc.oid
Join pg_aggregate.aggtransfn => pg_proc.oid
Join pg_aggregate.aggfinalfn => pg_proc.oid
Join pg_aggregate.aggtranstype => pg_type.oid
Join pg_am.amgettuple => pg_proc.oid
Join pg_am.aminsert => pg_proc.oid
Join pg_am.ambeginscan => pg_proc.oid
Join pg_am.amrescan => pg_proc.oid
Join pg_am.amendscan => pg_proc.oid
Join pg_am.ammarkpos => pg_proc.oid
Join pg_am.amrestrpos => pg_proc.oid
Join pg_am.ambuild => pg_proc.oid
Join pg_am.ambulkdelete => pg_proc.oid
Join pg_am.amcostestimate => pg_proc.oid
Join pg_amop.amopclaid => pg_opclass.oid
Join pg_amop.amopopr => pg_operator.oid
Join pg_amproc.amopclaid => pg_opclass.oid
Join pg_amproc.amproc => pg_proc.oid
Join pg_attribute.attrelid => pg_class.oid
Join pg_attribute.atttypid => pg_type.oid
Join pg_cast.castsource => pg_type.oid
Join pg_cast.casttarget => pg_type.oid
Join pg_cast.castfunc => pg_proc.oid
Join pg_class.relnamespace => pg_namespace.oid
Join pg_class.reltype => pg_type.oid
Join pg_class.relam => pg_am.oid
Join pg_class.relfilenode => pg_class.oid
Join pg_class.reltoastrelid => pg_class.oid
Join pg_class.reltoastidxid => pg_class.oid
Join pg_conversion.connamespace => pg_namespace.oid
Join pg_conversion.conproc => pg_proc.oid
Join pg_database.datlastsysoid => pg_conversion.oid
Join pg_depend.classid => pg_class.oid
Join pg_depend.objid => pg_conversion.oid
Join pg_depend.objid => pg_rewrite.oid
Join pg_depend.objid => pg_type.oid
Join pg_depend.refclassid => pg_class.oid
Join pg_depend.refobjid => pg_cast.oid
Join pg_depend.refobjid => pg_class.oid
Join pg_depend.refobjid => pg_language.oid
Join pg_depend.refobjid => pg_namespace.oid
Join pg_depend.refobjid => pg_opclass.oid
Join pg_depend.refobjid => pg_operator.oid
Join pg_depend.refobjid => pg_proc.oid
Join pg_depend.refobjid => pg_trigger.oid
Join pg_depend.refobjid => pg_type.oid
Join pg_description.objoid => pg_am.oid
Join pg_description.objoid => pg_database.oid
Join pg_description.objoid => pg_language.oid
Join pg_description.objoid => pg_namespace.oid
Join pg_description.objoid => pg_proc.oid
Join pg_description.objoid => pg_type.oid
Join pg_description.classoid => pg_class.oid
Join pg_index.indexrelid => pg_class.oid
Join pg_index.indrelid => pg_class.oid
Join pg_language.lanvalidator => pg_proc.oid
Join pg_opclass.opcamid => pg_am.oid
Join pg_opclass.opcnamespace => pg_namespace.oid
Join pg_opclass.opcintype => pg_type.oid
Join pg_operator.oprnamespace => pg_namespace.oid
Join pg_operator.oprleft => pg_type.oid
Join pg_operator.oprright => pg_type.oid
Join pg_operator.oprresult => pg_type.oid
Join pg_operator.oprcom => pg_operator.oid
Join pg_operator.oprnegate => pg_operator.oid
Join pg_operator.oprlsortop => pg_operator.oid
Join pg_operator.oprrsortop => pg_operator.oid
Join pg_operator.oprltcmpop => pg_operator.oid
Join pg_operator.oprgtcmpop => pg_operator.oid
Join pg_operator.oprcode => pg_proc.oid
Join pg_operator.oprrest => pg_proc.oid
Join pg_operator.oprjoin => pg_proc.oid
Join pg_proc.pronamespace => pg_namespace.oid
Join pg_proc.prolang => pg_language.oid
Join pg_proc.prorettype => pg_type.oid
Join pg_rewrite.ev_class => pg_class.oid
Join pg_trigger.tgrelid => pg_class.oid
Join pg_trigger.tgfoid => pg_proc.oid
Join pg_type.typnamespace => pg_namespace.oid
Join pg_type.typrelid => pg_class.oid
Join pg_type.typelem => pg_type.oid
Join pg_type.typinput => pg_proc.oid
Join pg_type.typoutput => pg_proc.oid
--
-- This is created by pgsql/contrib/findoidjoins/make_oidjoin_check
--
SELECT ctid, pg_aggregate.aggfnoid
FROM pg_aggregate
WHERE pg_aggregate.aggfnoid != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfnoid);
SELECT ctid, pg_aggregate.aggtransfn
FROM pg_aggregate
WHERE pg_aggregate.aggtransfn != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggtransfn);
SELECT ctid, pg_aggregate.aggfinalfn
FROM pg_aggregate
WHERE pg_aggregate.aggfinalfn != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfinalfn);
SELECT ctid, pg_aggregate.aggtranstype
FROM pg_aggregate
WHERE pg_aggregate.aggtranstype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggtranstype);
SELECT ctid, pg_am.amgettuple
FROM pg_am
WHERE pg_am.amgettuple != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amgettuple);
SELECT ctid, pg_am.aminsert
FROM pg_am
WHERE pg_am.aminsert != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.aminsert);
SELECT ctid, pg_am.ambeginscan
FROM pg_am
WHERE pg_am.ambeginscan != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambeginscan);
SELECT ctid, pg_am.amrescan
FROM pg_am
WHERE pg_am.amrescan != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrescan);
SELECT ctid, pg_am.amendscan
FROM pg_am
WHERE pg_am.amendscan != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amendscan);
SELECT ctid, pg_am.ammarkpos
FROM pg_am
WHERE pg_am.ammarkpos != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ammarkpos);
SELECT ctid, pg_am.amrestrpos
FROM pg_am
WHERE pg_am.amrestrpos != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrestrpos);
SELECT ctid, pg_am.ambuild
FROM pg_am
WHERE pg_am.ambuild != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambuild);
SELECT ctid, pg_am.ambulkdelete
FROM pg_am
WHERE pg_am.ambulkdelete != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambulkdelete);
SELECT ctid, pg_am.amcostestimate
FROM pg_am
WHERE pg_am.amcostestimate != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amcostestimate);
SELECT ctid, pg_amop.amopclaid
FROM pg_amop
WHERE pg_amop.amopclaid != 0 AND
NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amop.amopclaid);
SELECT ctid, pg_amop.amopopr
FROM pg_amop
WHERE pg_amop.amopopr != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_amop.amopopr);
SELECT ctid, pg_amproc.amopclaid
FROM pg_amproc
WHERE pg_amproc.amopclaid != 0 AND
NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amproc.amopclaid);
SELECT ctid, pg_amproc.amproc
FROM pg_amproc
WHERE pg_amproc.amproc != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_amproc.amproc);
SELECT ctid, pg_attribute.attrelid
FROM pg_attribute
WHERE pg_attribute.attrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_attribute.attrelid);
SELECT ctid, pg_attribute.atttypid
FROM pg_attribute
WHERE pg_attribute.atttypid != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_attribute.atttypid);
SELECT ctid, pg_cast.castsource
FROM pg_cast
WHERE pg_cast.castsource != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_cast.castsource);
SELECT ctid, pg_cast.casttarget
FROM pg_cast
WHERE pg_cast.casttarget != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_cast.casttarget);
SELECT ctid, pg_cast.castfunc
FROM pg_cast
WHERE pg_cast.castfunc != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_cast.castfunc);
SELECT ctid, pg_class.relnamespace
FROM pg_class
WHERE pg_class.relnamespace != 0 AND
NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_class.relnamespace);
SELECT ctid, pg_class.reltype
FROM pg_class
WHERE pg_class.reltype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_class.reltype);
SELECT ctid, pg_class.relam
FROM pg_class
WHERE pg_class.relam != 0 AND
NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_class.relam);
SELECT ctid, pg_class.relfilenode
FROM pg_class
WHERE pg_class.relfilenode != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.relfilenode);
SELECT ctid, pg_class.reltoastrelid
FROM pg_class
WHERE pg_class.reltoastrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastrelid);
SELECT ctid, pg_class.reltoastidxid
FROM pg_class
WHERE pg_class.reltoastidxid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastidxid);
SELECT ctid, pg_conversion.connamespace
FROM pg_conversion
WHERE pg_conversion.connamespace != 0 AND
NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_conversion.connamespace);
SELECT ctid, pg_conversion.conproc
FROM pg_conversion
WHERE pg_conversion.conproc != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_conversion.conproc);
SELECT ctid, pg_database.datlastsysoid
FROM pg_database
WHERE pg_database.datlastsysoid != 0 AND
NOT EXISTS(SELECT * FROM pg_conversion AS t1 WHERE t1.oid = pg_database.datlastsysoid);
SELECT ctid, pg_depend.classid
FROM pg_depend
WHERE pg_depend.classid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_depend.classid);
SELECT ctid, pg_depend.refclassid
FROM pg_depend
WHERE pg_depend.refclassid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_depend.refclassid);
SELECT ctid, pg_description.classoid
FROM pg_description
WHERE pg_description.classoid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_description.classoid);
SELECT ctid, pg_index.indexrelid
FROM pg_index
WHERE pg_index.indexrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indexrelid);
SELECT ctid, pg_index.indrelid
FROM pg_index
WHERE pg_index.indrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indrelid);
SELECT ctid, pg_language.lanvalidator
FROM pg_language
WHERE pg_language.lanvalidator != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_language.lanvalidator);
SELECT ctid, pg_opclass.opcamid
FROM pg_opclass
WHERE pg_opclass.opcamid != 0 AND
NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_opclass.opcamid);
SELECT ctid, pg_opclass.opcnamespace
FROM pg_opclass
WHERE pg_opclass.opcnamespace != 0 AND
NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_opclass.opcnamespace);
SELECT ctid, pg_opclass.opcintype
FROM pg_opclass
WHERE pg_opclass.opcintype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcintype);
SELECT ctid, pg_operator.oprnamespace
FROM pg_operator
WHERE pg_operator.oprnamespace != 0 AND
NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_operator.oprnamespace);
SELECT ctid, pg_operator.oprleft
FROM pg_operator
WHERE pg_operator.oprleft != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprleft);
SELECT ctid, pg_operator.oprright
FROM pg_operator
WHERE pg_operator.oprright != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprright);
SELECT ctid, pg_operator.oprresult
FROM pg_operator
WHERE pg_operator.oprresult != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprresult);
SELECT ctid, pg_operator.oprcom
FROM pg_operator
WHERE pg_operator.oprcom != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprcom);
SELECT ctid, pg_operator.oprnegate
FROM pg_operator
WHERE pg_operator.oprnegate != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprnegate);
SELECT ctid, pg_operator.oprlsortop
FROM pg_operator
WHERE pg_operator.oprlsortop != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprlsortop);
SELECT ctid, pg_operator.oprrsortop
FROM pg_operator
WHERE pg_operator.oprrsortop != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprrsortop);
SELECT ctid, pg_operator.oprltcmpop
FROM pg_operator
WHERE pg_operator.oprltcmpop != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprltcmpop);
SELECT ctid, pg_operator.oprgtcmpop
FROM pg_operator
WHERE pg_operator.oprgtcmpop != 0 AND
NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprgtcmpop);
SELECT ctid, pg_operator.oprcode
FROM pg_operator
WHERE pg_operator.oprcode != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprcode);
SELECT ctid, pg_operator.oprrest
FROM pg_operator
WHERE pg_operator.oprrest != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprrest);
SELECT ctid, pg_operator.oprjoin
FROM pg_operator
WHERE pg_operator.oprjoin != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprjoin);
SELECT ctid, pg_proc.pronamespace
FROM pg_proc
WHERE pg_proc.pronamespace != 0 AND
NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_proc.pronamespace);
SELECT ctid, pg_proc.prolang
FROM pg_proc
WHERE pg_proc.prolang != 0 AND
NOT EXISTS(SELECT * FROM pg_language AS t1 WHERE t1.oid = pg_proc.prolang);
SELECT ctid, pg_proc.prorettype
FROM pg_proc
WHERE pg_proc.prorettype != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_proc.prorettype);
SELECT ctid, pg_rewrite.ev_class
FROM pg_rewrite
WHERE pg_rewrite.ev_class != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_rewrite.ev_class);
SELECT ctid, pg_trigger.tgrelid
FROM pg_trigger
WHERE pg_trigger.tgrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_trigger.tgrelid);
SELECT ctid, pg_trigger.tgfoid
FROM pg_trigger
WHERE pg_trigger.tgfoid != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_trigger.tgfoid);
SELECT ctid, pg_type.typnamespace
FROM pg_type
WHERE pg_type.typnamespace != 0 AND
NOT EXISTS(SELECT * FROM pg_namespace AS t1 WHERE t1.oid = pg_type.typnamespace);
SELECT ctid, pg_type.typrelid
FROM pg_type
WHERE pg_type.typrelid != 0 AND
NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_type.typrelid);
SELECT ctid, pg_type.typelem
FROM pg_type
WHERE pg_type.typelem != 0 AND
NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_type.typelem);
SELECT ctid, pg_type.typinput
FROM pg_type
WHERE pg_type.typinput != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typinput);
SELECT ctid, pg_type.typoutput
FROM pg_type
WHERE pg_type.typoutput != 0 AND
NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typoutput);
pgsql-patches by date: