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"

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

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

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

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

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

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

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

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

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