findoidjoins doens't seem to compile:

gmake[1]: Entering directory `/home/chriskl/pgsql-head/contrib/findoidjoins'
gcc -pipe -O -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../sr
c/interfaces/libpgeasy -I../../src/interfaces/libpq -I. -I../../src/include 
  -c -o findoidjoins.o findoidjoins.c -MMD
findoidjoins.c:8: halt.h: No such file or directory
findoidjoins.c:9: libpgeasy.h: No such file or directory
findoidjoins.c: In function `main':
findoidjoins.c:26: warning: implicit declaration of function `halt'
findoidjoins.c:29: warning: implicit declaration of function `connectdb'
findoidjoins.c:31: warning: implicit declaration of function
`on_error_continue'
findoidjoins.c:32: warning: implicit declaration of function `on_error_stop'
findoidjoins.c:34: warning: implicit declaration of function `doquery'
findoidjoins.c:50: warning: implicit declaration of function `get_result'
findoidjoins.c:50: warning: assignment makes pointer from integer without a
cast
findoidjoins.c:60: warning: assignment makes pointer from integer without a
cast
findoidjoins.c:62: warning: implicit declaration of function `set_result'
findoidjoins.c:63: warning: implicit declaration of function `fetch'
findoidjoins.c:63: `END_OF_TUPLES' undeclared (first use in this function)
findoidjoins.c:63: (Each undeclared identifier is reported only once
findoidjoins.c:63: for each function it appears in.)
findoidjoins.c:66: warning: implicit declaration of function `reset_fetch'
findoidjoins.c:69: warning: implicit declaration of function `unset_result'
findoidjoins.c:83: warning: passing arg 2 of `sprintf' makes pointer from
integer without a cast
findoidjoins.c:107: warning: implicit declaration of function `disconnectdb'
gmake[1]: *** [findoidjoins.o] Error 1
gmake[1]: Leaving directory `/home/chriskl/pgsql-head/contrib/findoidjoins'
gmake: *** [install] Error 2


Tue, 3 Sep 2002 14:41:50 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
findoidjoins
Christopher Kings-Lynne dijo: 

> findoidjoins doens't seem to compile:
> 
> gmake[1]: Entering directory `/home/chriskl/pgsql-head/contrib/findoidjoins'
> gcc -pipe -O -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../sr
> c/interfaces/libpgeasy -I../../src/interfaces/libpq -I. -I../../src/include 
>   -c -o findoidjoins.o findoidjoins.c -MMD
> findoidjoins.c:8: halt.h: No such file or directory
> findoidjoins.c:9: libpgeasy.h: No such file or directory

Seems related to the ripping of libpgeasy out of the main
distribution...

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
"Uno puede defenderse de los ataques; contra los elogios se esta indefenso"


Tue, 3 Sep 2002 02:46:46 -0400 (CLT)
Alvaro Herrera <alvherre@atentus.com>
Re: findoidjoins
Alvaro Herrera <alvherre@atentus.com> writes:
> Christopher Kings-Lynne dijo: 
>> findoidjoins doens't seem to compile:

> Seems related to the ripping of libpgeasy out of the main
> distribution...

I believe it's been broken for some time (disremember just why, maybe a
schema issue?).  I had a TODO item to resurrect it so that we could
update the oidjoins regression test, which is sadly out of date for
the current system catalogs.  If anyone wants to work on that ...

			regards, tom lane

Tue, 03 Sep 2002 09:21:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: findoidjoins 
Tom Lane wrote:
> Alvaro Herrera <alvherre@atentus.com> writes:
>>Christopher Kings-Lynne dijo: 
>>>findoidjoins doens't seem to compile:
>>Seems related to the ripping of libpgeasy out of the main
>>distribution...
> 
> I believe it's been broken for some time (disremember just why, maybe a
> schema issue?).  I had a TODO item to resurrect it so that we could
> update the oidjoins regression test, which is sadly out of date for
> the current system catalogs.  If anyone wants to work on that ...

I'm not sure I interpreted the intent of findoidjoins just right, but 
here it is updated for schemas, new reg* types, using SPI instead of 
libpgeasy, and returning the results as a table function. Any 
corrections/comments? If there is any interest, I'll polish this up a 
bit more and submit to patches. Just let me know.

(Should qualify as a fix, right?)

Thanks,

Joe
Tue, 03 Sep 2002 23:06:11 -0700
Joe Conway <mail@joeconway.com>
Re: findoidjoins

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://207.106.42.251/cgi-bin/pgpatches

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

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


Joe Conway wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre@atentus.com> writes:
> >>Christopher Kings-Lynne dijo: 
> >>>findoidjoins doens't seem to compile:
> >>Seems related to the ripping of libpgeasy out of the main
> >>distribution...
> > 
> > I believe it's been broken for some time (disremember just why, maybe a
> > schema issue?).  I had a TODO item to resurrect it so that we could
> > update the oidjoins regression test, which is sadly out of date for
> > the current system catalogs.  If anyone wants to work on that ...
> 
> I'm not sure I interpreted the intent of findoidjoins just right, but 
> here it is updated for schemas, new reg* types, using SPI instead of 
> libpgeasy, and returning the results as a table function. Any 
> corrections/comments? If there is any interest, I'll polish this up a 
> bit more and submit to patches. Just let me know.
> 
> (Should qualify as a fix, right?)
> 
> Thanks,
> 
> Joe
> 

[ application/x-gzip is not supported, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

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

Wed, 4 Sep 2002 02:20:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: findoidjoins
Joe Conway <mail@joeconway.com> writes:
> I'm not sure I interpreted the intent of findoidjoins just right, but 
> here it is updated for schemas, new reg* types, using SPI instead of 
> libpgeasy, and returning the results as a table function. Any 
> corrections/comments?

For what we want it for (viz, regenerating the oidjoins test every so
often), this is really a step backwards.  It requires more work to run
than the original program, and it modifies the database under test,
which is undesirable because it's commonly run against template1.

I was thinking of keeping it as a client program, but recasting it to
use libpq since libpgeasy isn't in the standard distribution anymore.

I've looked through my notes and I can't find why I thought findoidjoins
was broken for 7.3.  Did you come across anything obviously wrong with
its queries?

			regards, tom lane

Wed, 04 Sep 2002 10:55:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: findoidjoins 
Tom Lane wrote:
> For what we want it for (viz, regenerating the oidjoins test every so
> often), this is really a step backwards.  It requires more work to run
> than the original program, and it modifies the database under test,
> which is undesirable because it's commonly run against template1.
> 
> I was thinking of keeping it as a client program, but recasting it to
> use libpq since libpgeasy isn't in the standard distribution anymore.

OK. I'll take another shot using that approach. A couple questions:

Is it useful to have the reference count and unreferenced counts like 
currently written, or should I just faithfully reproduce the original 
behavior (except schema aware queries) using libpq in place of libpgeasy?

Is the oidjoins.sql test just the output of the make_oidjoins_check 
script? It is probably easier to produce that output while looping 
through the first time versus running a script -- should I do that?


> I've looked through my notes and I can't find why I thought findoidjoins
> was broken for 7.3.  Did you come across anything obviously wrong with
> its queries?

As written the queries did not know anything about schemas or the newer 
reg* data types, e.g. this:

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 ;

became this:

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

Does the latter produce the desired result?

Joe


Wed, 04 Sep 2002 09:21:39 -0700
Joe Conway <mail@joeconway.com>
Re: findoidjoins

Patch withdrawn by author.

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

Joe Conway wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre@atentus.com> writes:
> >>Christopher Kings-Lynne dijo: 
> >>>findoidjoins doens't seem to compile:
> >>Seems related to the ripping of libpgeasy out of the main
> >>distribution...
> > 
> > I believe it's been broken for some time (disremember just why, maybe a
> > schema issue?).  I had a TODO item to resurrect it so that we could
> > update the oidjoins regression test, which is sadly out of date for
> > the current system catalogs.  If anyone wants to work on that ...
> 
> I'm not sure I interpreted the intent of findoidjoins just right, but 
> here it is updated for schemas, new reg* types, using SPI instead of 
> libpgeasy, and returning the results as a table function. Any 
> corrections/comments? If there is any interest, I'll polish this up a 
> bit more and submit to patches. Just let me know.
> 
> (Should qualify as a fix, right?)
> 
> Thanks,
> 
> Joe
> 

[ application/x-gzip is not supported, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

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

Wed, 4 Sep 2002 12:43:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: findoidjoins
Joe Conway <mail@joeconway.com> writes:
> Is it useful to have the reference count and unreferenced counts like 
> currently written, or should I just faithfully reproduce the original 
> behavior (except schema aware queries) using libpq in place of libpgeasy?

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.

> Is the oidjoins.sql test just the output of the make_oidjoins_check 
> script?

Yes.

> It is probably easier to produce that output while looping 
> through the first time versus running a script -- should I do that?

The separation between findoidjoins and make_oidjoins_check is
deliberate --- this allows for easy hand-editing of the join list to
remove unwanted joins before preparing the regression test script
(cf the notes in the README about bogus joins).  Even though this is
an extra manual step, I think it's a better answer than trying to make
findoidjoins smart enough to suppress the bogus joins itself.  Part of
the reason for running findoidjoins is to detect any unexpected
linkages, so it should not be too eager to hide things.  Also, because
the output of findoidjoins *should* be manually reviewed, it's better
to put it out in an easy-to-read one-line-per-join format than to put
out the finished regression script directly.

>> I've looked through my notes and I can't find why I thought findoidjoins
>> was broken for 7.3.  Did you come across anything obviously wrong with
>> its queries?

> As written the queries did not know anything about schemas or the newer 
> reg* data types, e.g. this:
> Does the latter produce the desired result?

Not sure.  My oldest note saying it was busted predates the invention of
the new reg* types, I think.  And while schema awareness is nice, it's
not critical to the usefulness of the script: we're only really going to
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?

			regards, tom lane

Wed, 04 Sep 2002 12:57:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: findoidjoins 
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>Is it useful to have the reference count and unreferenced counts like 
>>currently written, or should I just faithfully reproduce the original 
>>behavior (except schema aware queries) using libpq in place of libpgeasy?
 >
> 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.

It was only taking about 7 seconds for me on an empty database, but if 
it's not useful I'll go back to the original output format.


>>It is probably easier to produce that output while looping 
>>through the first time versus running a script -- should I do that?
> 
> The separation between findoidjoins and make_oidjoins_check is
> deliberate --- this allows for easy hand-editing of the join list to
> remove unwanted joins before preparing the regression test script
> (cf the notes in the README about bogus joins).  Even though this is
> an extra manual step, I think it's a better answer than trying to make
> findoidjoins smart enough to suppress the bogus joins itself.  Part of
> the reason for running findoidjoins is to detect any unexpected
> linkages, so it should not be too eager to hide things.  Also, because
> the output of findoidjoins *should* be manually reviewed, it's better
> to put it out in an easy-to-read one-line-per-join format than to put
> out the finished regression script directly.

OK. I'll leave as is.

>>As written the queries did not know anything about schemas or the newer 
>>reg* data types, e.g. this:
>>Does the latter produce the desired result?
> 
> Not sure.  My oldest note saying it was busted predates the invention of
> the new reg* types, I think.  And while schema awareness is nice, it's
> not critical to the usefulness of the script: we're only really going to
> 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 plausible. But I guess it will be easier to tell once it produces 
results in the same format as before. I'll make the changes and send it 
in to patches.

Thanks,

Joe


Wed, 04 Sep 2002 10:15:19 -0700
Joe Conway <mail@joeconway.com>
Re: findoidjoins
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
Wed, 04 Sep 2002 22:25:19 -0700
Joe Conway <mail@joeconway.com>
findoidjoins patch (was Re: [HACKERS] findoidjoins)

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@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);

> 
> ---------------------------(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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 10 Sep 2002 22:32:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)
Bruce Momjian wrote:
> 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.
> 

I think I saw a commit message from Tom applying this already...yup:

http://developer.postgresql.org/cvsweb.cgi/pgsql-server/contrib/findoidjoins/findoidjoins.c

Joe


Tue, 10 Sep 2002 19:54:26 -0700
Joe Conway <mail@joeconway.com>
Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)

Patch already applied by Tom.

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

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@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);

> 
> ---------------------------(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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 10 Sep 2002 22:57:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: findoidjoins patch (was Re: [HACKERS] findoidjoins)