Re: findoidjoins

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

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"

Alvaro Herrera <alvherre(at)atentus(dot)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

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)atentus(dot)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

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(at)atentus(dot)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(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

Joe Conway <mail(at)joeconway(dot)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

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

Patch withdrawn by author.

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

Joe Conway wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)atentus(dot)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(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

Joe Conway <mail(at)joeconway(dot)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

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)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

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

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

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

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(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