Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit85e9e03

Browse files
committed
Update findoidjoins for 6.5: remove workaround for long-dead bug,
use NOT EXISTS() which is a lot faster than NOT IN (),update documentation.
1 parent1e11792 commit85e9e03

File tree

4 files changed

+50
-31
lines changed

4 files changed

+50
-31
lines changed

‎contrib/findoidjoins/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
#
55
PGINTERFACE = ../pginterface/pginterface.o ../pginterface/halt.o# these have to be in your library search path
66
TARGET = findoidjoins
7-
CFLAGS = -g -Wall -I. -I../../src/interfaces/libpq -I/usr/local/pgsql/include
7+
CFLAGS = -g -Wall -I. -I../pginterface -I../../src/interfaces/libpq -I/usr/local/pgsql/include
88
LDFLAGS = -L/usr/local/pgsql/lib -lpq
99

1010
all :$(TARGET)

‎contrib/findoidjoins/README

Lines changed: 19 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,27 +1,36 @@
11

2-
32
findoidjoins
43

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.
4+
This program scans a database, and prints oid fields and the tables
5+
they join to. CAUTION: it is ver-r-r-y slow on a large database, or
6+
even a not-so-large one. We don't really recommend running it on
7+
anything but an empty database.
8+
9+
It requires pgsql/contrib/pginterface to be compiled first.
910

10-
It requires /pgsql/contrib/pginterface to be compiled first.
11+
Run on an empty database, it returns the system join relationships
12+
(shown below for 6.5). Note that unexpected matches may indicate
13+
bogus entries in system tables --- don't accept a peculiar match
14+
without question. In particular, a field shown as joining to more
15+
than one target table is probably messed up.
1116

12-
Run on am empty database, it returns the system join relationships:
17+
The shell script make_oidjoins_check converts findoidjoins' output
18+
into an SQL script that checks for dangling links (entries in an
19+
OID column that don't match any row in the expected table).
20+
The result of this script should be installed as the "oidjoins"
21+
regression test. The oidjoins test should be updated after any
22+
revision in the patterns of cross-links between system tables.
23+
(Ideally we'd just regenerate the script as part of the regression
24+
tests themselves, but that seems too slow...)
1325

1426
---------------------------------------------------------------------------
1527

1628
Join pg_aggregate.aggtransfn1 => pg_proc.oid
1729
Join pg_aggregate.aggtransfn2 => pg_proc.oid
1830
Join pg_aggregate.aggfinalfn => pg_proc.oid
19-
Join pg_aggregate.aggbasetype => pg_proc.oid
2031
Join pg_aggregate.aggbasetype => pg_type.oid
21-
Join pg_aggregate.aggtranstype1 => pg_proc.oid
2232
Join pg_aggregate.aggtranstype1 => pg_type.oid
2333
Join pg_aggregate.aggtranstype2 => pg_type.oid
24-
Join pg_aggregate.aggfinaltype => pg_proc.oid
2534
Join pg_aggregate.aggfinaltype => pg_type.oid
2635
Join pg_am.amgettuple => pg_proc.oid
2736
Join pg_am.aminsert => pg_proc.oid
@@ -35,14 +44,10 @@ Join pg_am.ambuild => pg_proc.oid
3544
Join pg_amop.amopid => pg_am.oid
3645
Join pg_amop.amopclaid => pg_opclass.oid
3746
Join pg_amop.amopopr => pg_operator.oid
38-
Join pg_amop.amopopr => pg_proc.oid
3947
Join pg_amop.amopselect => pg_proc.oid
4048
Join pg_amop.amopnpages => pg_proc.oid
4149
Join pg_amproc.amid => pg_am.oid
4250
Join pg_amproc.amopclaid => pg_opclass.oid
43-
Join pg_amproc.amopclaid => pg_operator.oid
44-
Join pg_amproc.amopclaid => pg_proc.oid
45-
Join pg_amproc.amproc => pg_operator.oid
4651
Join pg_amproc.amproc => pg_proc.oid
4752
Join pg_attribute.attrelid => pg_class.oid
4853
Join pg_attribute.atttypid => pg_type.oid
@@ -70,15 +75,12 @@ Join pg_proc.prolang => pg_language.oid
7075
Join pg_proc.prorettype => pg_type.oid
7176
Join pg_rewrite.ev_class => pg_class.oid
7277
Join pg_type.typrelid => pg_class.oid
73-
Join pg_type.typelem => pg_operator.oid
74-
Join pg_type.typelem => pg_proc.oid
7578
Join pg_type.typelem => pg_type.oid
7679
Join pg_type.typinput => pg_proc.oid
7780
Join pg_type.typoutput => pg_proc.oid
7881
Join pg_type.typreceive => pg_proc.oid
7982
Join pg_type.typsend => pg_proc.oid
8083

81-
8284
---------------------------------------------------------------------------
8385

8486
Bruce Momjian (root@candle.pha.pa.us)

‎contrib/findoidjoins/findoidjoins.c

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -50,8 +50,7 @@ main(int argc, char **argv)
5050
SELECT relname \
5151
FROM pg_class c \
5252
WHERE relkind = 'r' AND \
53-
relhasrules = 'f' AND \
54-
relname != 'pg_user' \
53+
relhasrules = 'f' \
5554
ORDER BY 1; \
5655
");
5756
doquery("FETCH ALL IN c_relations");
Lines changed: 29 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,17 +1,37 @@
1-
:
1+
#! /bin/sh
2+
23
# You first run findoidjoins on the template1 database, and send that
34
# output into this file to generate a list of SQL statements.
5+
6+
# NOTE: any field that findoidjoins thinks joins to more than one table
7+
# will NOT be checked by the output of this script. You should be
8+
# suspicious of multiple entries in findoidjoins' output.
9+
10+
# Caution: you may need to use GNU awk.
11+
AWK=${AWK:-awk}
12+
413
trap"rm -f /tmp/$$ /tmp/$$a /tmp/$$b" 0 1 2 3 15
514

15+
# Read input
616
cat"$@">/tmp/$$
17+
18+
# Look for fields with multiple references.
719
cat /tmp/$$| cut -d'' -f2| sort| uniq -d>/tmp/$$a
20+
if [-s /tmp/$$a ];then
21+
echo"Ignoring these fields that link to multiple tables:"1>&2
22+
cat /tmp/$$a1>&2
23+
fi
24+
25+
# Get the non-multiply-referenced fields.
826
cat /tmp/$$|whileread LINE
927
do
1028
set --$LINE
1129
grep"$2" /tmp/$$a>/dev/null2>&1||echo$LINE
1230
done>/tmp/$$b
31+
32+
# Generate the output.
1333
cat /tmp/$$b|
14-
awk -F'[ \.]' '\
34+
$AWK -F'[ \.]''\
1535
BEGIN \
1636
{
1737
printf "\
@@ -23,13 +43,11 @@ awk -F'[ \.]' '\
2343
printf "\
2444
SELECToid, %s.%s \n\
2545
FROM%s \n\
26-
WHERE%s%s.%s%s NOT IN (SELECT oid FROM %s) AND \n\
27-
%s%s.%s%s != 0;\n", $2, $3, $2,
28-
($5 == "pg_proc") ? "RegprocToOid(" : "",
29-
$2, $3,
30-
($5 == "pg_proc") ? ")" : "",
31-
$5,
32-
($5 == "pg_proc") ? "RegprocToOid(" : "",
33-
$2, $3,
34-
($5 == "pg_proc") ? ")" : "";
46+
WHERE%s.%s != 0 AND \n\
47+
NOT EXISTS(SELECT * FROM %s AS t1 WHERE t1.oid = %s.%s);\n",
48+
$2, $3, $2,
49+
$2, $3,
50+
$5, $2, $3;
3551
}'
52+
53+
exit 0

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp