Skip to content

Commit 85c165c

Browse files
bmomjianbmomjian
committed
New findoidjoins examines oid columns to find join relationships.
1 parent ffb120e commit 85c165c

File tree

8 files changed

+266
-1
lines changed

8 files changed

+266
-1
lines changed

‎contrib/README

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,11 @@ earthdistance -
1818
Operator for computing earth distance for two points
1919
by Hal Snyder <hal@vailsys.com>
2020

21+
findoidjoins -
22+
Finds the joins used by oid columns by examining the actual
23+
values in the oid columns and row oids.
24+
by Bruce Momjian <root@candle.pha.pa.us>
25+
2126
fulltextindex -
2227
Full text indexing using triggers
2328
by Maarten Boekhold <maartenb@dutepp0.et.tudelft.nl>

‎contrib/findoidjoins/Makefile

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
#
2+
# Makefile, requires pgsql/contrib/pginterface
3+
#
4+
#
5+
PGINTERFACE = pginterface.o halt.o # these have to be in your library search path
6+
TARGET = findoidjoins
7+
CFLAGS = -g -Wall -I. -I../../src/interfaces/libpq -I/usr/local/pgsql/include
8+
LDFLAGS = -L/usr/local/pgsql/lib -lpq
9+
10+
all : $(TARGET)
11+
12+
findoidjoins: $(PGINTERFACE) findoidjoins.c
13+
gcc -o $@ $(CFLAGS) $@.c $(PGINTERFACE) $(LDFLAGS)
14+
15+
clean:
16+
rm -f *.o $(TARGET) log core
17+
18+
install:
19+
install -s -o bin -g bin $(TARGET) /usr/local/pgsql/bin
20+

‎contrib/findoidjoins/README

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
2+
3+
findoidjoins
4+
5+
This program scans the a database, and prints oid fields, and the tables
6+
they join to. PostgreSQL version 6.3.2 crashes with aggregates on
7+
views, so I have removed the view pg_user from the list of relations to
8+
examine.
9+
10+
Run on am empty database, it returns the system join relationships:
11+
12+
---------------------------------------------------------------------------
13+
14+
Join pg_aggregate.aggfinaltype => pg_proc.oid
15+
Join pg_aggregate.aggfinaltype => pg_type.oid
16+
Join pg_aggregate.aggowner => pg_proc.oid
17+
Join pg_aggregate.aggbasetype => pg_proc.oid
18+
Join pg_aggregate.aggbasetype => pg_type.oid
19+
Join pg_aggregate.aggtranstype1 => pg_proc.oid
20+
Join pg_aggregate.aggtranstype1 => pg_type.oid
21+
Join pg_aggregate.aggtranstype2 => pg_type.oid
22+
Join pg_am.amowner => pg_proc.oid
23+
Join pg_amop.amopid => pg_am.oid
24+
Join pg_amop.amopopr => pg_operator.oid
25+
Join pg_amop.amopclaid => pg_opclass.oid
26+
Join pg_amproc.amproc => pg_operator.oid
27+
Join pg_amproc.amproc => pg_proc.oid
28+
Join pg_amproc.amopclaid => pg_opclass.oid
29+
Join pg_amproc.amopclaid => pg_operator.oid
30+
Join pg_amproc.amopclaid => pg_proc.oid
31+
Join pg_amproc.amid => pg_am.oid
32+
Join pg_attribute.attrelid => pg_class.oid
33+
Join pg_attribute.atttypid => pg_type.oid
34+
Join pg_class.relam => pg_am.oid
35+
Join pg_class.reltype => pg_type.oid
36+
Join pg_class.relowner => pg_proc.oid
37+
Join pg_description.objoid => pg_proc.oid
38+
Join pg_description.objoid => pg_type.oid
39+
Join pg_index.indexrelid => pg_class.oid
40+
Join pg_index.indrelid => pg_class.oid
41+
Join pg_index.indproc => pg_proc.oid
42+
Join pg_opclass.opcdeftype => pg_type.oid
43+
Join pg_operator.oprcom => pg_operator.oid
44+
Join pg_operator.oprrsortop => pg_operator.oid
45+
Join pg_operator.oprlsortop => pg_operator.oid
46+
Join pg_operator.oprnegate => pg_operator.oid
47+
Join pg_operator.oprresult => pg_type.oid
48+
Join pg_operator.oprright => pg_type.oid
49+
Join pg_operator.oprleft => pg_type.oid
50+
Join pg_operator.oprowner => pg_proc.oid
51+
Join pg_parg.partype => pg_type.oid
52+
Join pg_parg.parproid => pg_operator.oid
53+
Join pg_parg.parproid => pg_proc.oid
54+
Join pg_proc.prolang => pg_language.oid
55+
Join pg_proc.prorettype => pg_type.oid
56+
Join pg_proc.proowner => pg_proc.oid
57+
Join pg_rewrite.ev_class => pg_class.oid
58+
Join pg_statistic.starelid => pg_class.oid
59+
Join pg_type.typrelid => pg_class.oid
60+
Join pg_type.typowner => pg_proc.oid
61+
Join pg_type.typelem => pg_operator.oid
62+
Join pg_type.typelem => pg_proc.oid
63+
Join pg_type.typelem => pg_type.oid
64+
65+
66+
---------------------------------------------------------------------------
67+
68+
Bruce Momjian (root@candle.pha.pa.us)

‎contrib/findoidjoins/findoidjoins.c

Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
/*
2+
* findoidjoins.c, required pgsql/contrib/pginterface
3+
*
4+
*/
5+
6+
#include <stdio.h>
7+
#include "halt.h"
8+
#include <libpq-fe.h>
9+
#include "pginterface.h"
10+
11+
PGresult *attres, *relres;
12+
13+
int
14+
main(int argc, char **argv)
15+
{
16+
char query[4000];
17+
char relname[256];
18+
char relname2[256];
19+
char attname[256];
20+
int count;
21+
22+
if (argc != 2)
23+
halt("Usage: %s database\n", argv[0]);
24+
25+
connectdb(argv[1], NULL, NULL, NULL, NULL);
26+
on_error_continue();
27+
on_error_stop();
28+
29+
doquery("BEGIN WORK");
30+
doquery("\
31+
DECLARE c_attributes BINARY CURSOR FOR \
32+
SELECT relname, a.attname \
33+
FROM pg_class c, pg_attribute a, pg_type t \
34+
WHERE a.attnum > 0 AND \
35+
relkind = 'r' AND \
36+
typname = 'oid' AND \
37+
a.attrelid = c.oid AND \
38+
a.atttypid = t.oid \
39+
ORDER BY 1; \
40+
");
41+
doquery("FETCH ALL IN c_attributes");
42+
attres = get_result();
43+
44+
doquery("\
45+
DECLARE c_relations BINARY CURSOR FOR \
46+
SELECT relname \
47+
FROM pg_class c \
48+
WHERE relkind = 'r' AND \
49+
relname != 'pg_user' \
50+
ORDER BY 1; \
51+
");
52+
doquery("FETCH ALL IN c_relations");
53+
relres = get_result();
54+
55+
set_result(attres);
56+
while (fetch(relname, attname) != END_OF_TUPLES)
57+
{
58+
set_result(relres);
59+
reset_fetch();
60+
while (fetch(relname2) != END_OF_TUPLES)
61+
{
62+
unset_result(relres);
63+
sprintf(query,"\
64+
DECLARE c_matches BINARY CURSOR FOR \
65+
SELECT count(*)
66+
FROM %s t1, %s t2 \
67+
WHERE t1.%s = t2.oid", relname, relname2, attname);
68+
69+
doquery(query);
70+
doquery("FETCH ALL IN c_matches");
71+
fetch(&count);
72+
if (count != 0)
73+
printf("Join %s.%s => %s.oid\n", relname, attname, relname2);
74+
doquery("CLOSE c_matches");
75+
set_result(relres);
76+
}
77+
set_result(attres);
78+
}
79+
80+
set_result(relres);
81+
doquery("CLOSE c_relations");
82+
PQclear(relres);
83+
84+
set_result(attres);
85+
doquery("CLOSE c_attributes");
86+
PQclear(attres);
87+
unset_result(attres);
88+
89+
doquery("COMMIT WORK");
90+
91+
disconnectdb();
92+
return 0;
93+
}

‎contrib/pginterface/README

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,11 @@ useful if you are running the query engine on a system with a different
2222
architecture than the database server. If you pass a NULL pointer, the
2323
column is skipped, and you can use libpq to handle it as you wish.
2424

25+
There are two functions, get_result() and set_result, that allow you to
26+
handle multiple result sets at the same time.
27+
28+
There is a reset_fetch() that starts the fetch back at the beginning.
29+
2530
There is a demo program called pginsert that demonstrates how the
2631
library can be used.
2732

‎contrib/pginterface/pginterface.c

Lines changed: 71 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,9 @@ static PGresult *res = NULL;
2222

2323
static int on_error_state = ON_ERROR_STOP;
2424

25+
static in_result_block = false;
26+
static was_get_unset_result = false;
27+
2528
/* LOCAL VARIABLES */
2629
static int tuple;
2730

@@ -64,9 +67,10 @@ disconnectdb()
6467
PGresult *
6568
doquery(char *query)
6669
{
67-
if (res != NULL)
70+
if (res != NULL && in_result_block == false && was_get_unset_result == false)
6871
PQclear(res);
6972

73+
was_get_unset_result = false;
7074
res = PQexec(conn, query);
7175

7276
if (on_error_state == ON_ERROR_STOP &&
@@ -187,3 +191,69 @@ on_error_continue()
187191
{
188192
on_error_state = ON_ERROR_CONTINUE;
189193
}
194+
195+
196+
/*
197+
**
198+
** get_result
199+
**
200+
*/
201+
PGresult *get_result()
202+
{
203+
was_get_unset_result = true;
204+
/* we have to store the fetch location somewhere */
205+
memcpy(&res->cmdStatus[CMDSTATUS_LEN-sizeof(tuple)],&tuple, sizeof(tuple));
206+
return res;
207+
}
208+
209+
/*
210+
**
211+
** set_result
212+
**
213+
*/
214+
void set_result(PGresult *newres)
215+
{
216+
if (newres == NULL)
217+
halt("set_result called with null result pointer\n");
218+
219+
if (res != NULL && was_get_unset_result == false)
220+
if (in_result_block == false)
221+
PQclear(res);
222+
else
223+
memcpy(&res->cmdStatus[CMDSTATUS_LEN-sizeof(tuple)], &tuple, sizeof(tuple));
224+
225+
in_result_block = true;
226+
was_get_unset_result = false;
227+
memcpy(&tuple, &newres->cmdStatus[CMDSTATUS_LEN-sizeof(tuple)], sizeof(tuple));
228+
res = newres;
229+
}
230+
231+
232+
/*
233+
**
234+
** unset_result
235+
**
236+
*/
237+
void unset_result(PGresult *oldres)
238+
{
239+
if (oldres == NULL)
240+
halt("unset_result called with null result pointer\n");
241+
242+
if (in_result_block == false)
243+
halt("Unset of result without being set.\n");
244+
245+
was_get_unset_result = true;
246+
memcpy(&oldres->cmdStatus[CMDSTATUS_LEN-sizeof(tuple)], &tuple, sizeof(tuple));
247+
in_result_block = false;
248+
}
249+
250+
/*
251+
**
252+
** reset_fetch
253+
**
254+
*/
255+
void reset_fetch()
256+
{
257+
tuple = 0;
258+
}
259+

‎contrib/pginterface/pginterface.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,5 +10,9 @@ int fetch(void *param,...);
1010
int fetchwithnulls(void *param,...);
1111
void on_error_continue();
1212
void on_error_stop();
13+
PGresult *get_result();
14+
void set_result(PGresult *newres);
15+
void unset_result(PGresult *oldres);
16+
void reset_fetch();
1317

1418
#define END_OF_TUPLES (-1)

‎doc/src/graphics/catalogs.ps

33.8 KB
Binary file not shown.

0 commit comments

Comments
 (0)