Skip site navigation (1) Skip section navigation (2)

Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-patches(at)postgresql(dot)org
Subject: Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)
Date: 2002-09-11 02:32:35
Message-ID: 200209110232.g8B2WZr01535@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Your patch has been added to the PostgreSQL unapplied patches list at:

	http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Joe Conway wrote:
> 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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us)
> + Updated for 7.3 by Joe Conway (mail(at)joeconway(dot)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);

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2002-09-11 02:33:15
Subject: Re: Please rename split(text,text,int) to splitpart
Previous:From: Curt SampsonDate: 2002-09-11 02:18:53
Subject: Re: Optimization levels when compiling PostgreSQL...

pgsql-patches by date

Next:From: Bruce MomjianDate: 2002-09-11 02:33:15
Subject: Re: Please rename split(text,text,int) to splitpart
Previous:From: Bruce MomjianDate: 2002-09-11 02:11:32
Subject: Re: cube patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group