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

Commitb0a66dd

Browse files
committed
New regression test to cross-check pg_type, pg_class,
and related tables.
1 parent8a3ef74 commitb0a66dd

File tree

3 files changed

+285
-0
lines changed

3 files changed

+285
-0
lines changed
Lines changed: 131 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,131 @@
1+
QUERY: SELECT p1.oid, p1.typname
2+
FROM pg_type as p1
3+
WHERE (p1.typlen <= 0 AND p1.typlen != -1) OR
4+
(p1.typtype != 'b' AND p1.typtype != 'c') OR
5+
NOT p1.typisdefined OR
6+
(p1.typalign != 'c' AND p1.typalign != 's' AND
7+
p1.typalign != 'i' AND p1.typalign != 'd');
8+
oid|typname
9+
---+-------
10+
(0 rows)
11+
12+
QUERY: SELECT p1.oid, p1.typname
13+
FROM pg_type as p1
14+
WHERE p1.typbyval AND
15+
(p1.typlen != 1 OR p1.typalign != 'c') AND
16+
(p1.typlen != 2 OR p1.typalign != 's') AND
17+
(p1.typlen != 4 OR p1.typalign != 'i');
18+
oid|typname
19+
---+-------
20+
(0 rows)
21+
22+
QUERY: SELECT p1.oid, p1.typname
23+
FROM pg_type as p1
24+
WHERE (p1.typtype = 'c' AND p1.typrelid = 0) OR
25+
(p1.typtype != 'c' AND p1.typrelid != 0);
26+
oid|typname
27+
---+-------
28+
(0 rows)
29+
30+
QUERY: SELECT p1.oid, p1.typname
31+
FROM pg_type as p1
32+
WHERE p1.typtype != 'c' AND
33+
(p1.typinput = 0 OR p1.typoutput = 0 OR
34+
p1.typreceive = 0 OR p1.typsend = 0);
35+
oid|typname
36+
---+-------
37+
(0 rows)
38+
39+
QUERY: SELECT p1.oid, p1.typname, p2.oid, p2.proname
40+
FROM pg_type AS p1, pg_proc AS p2
41+
WHERE p1.typinput = p2.oid AND p1.typtype = 'b' AND
42+
(p2.pronargs != 1 OR p2.proretset) AND
43+
(p2.pronargs < 2 OR p2.pronargs > 3 OR p2.proretset OR p1.typelem = 0);
44+
oid|typname|oid|proname
45+
---+-------+---+-------
46+
(0 rows)
47+
48+
QUERY: SELECT p1.oid, p1.typname, p2.oid, p2.proname
49+
FROM pg_type AS p1, pg_proc AS p2
50+
WHERE p1.typoutput = p2.oid AND p1.typtype = 'b' AND
51+
(p2.pronargs != 1 OR p2.proretset) AND
52+
(p2.pronargs != 2 OR p2.proretset OR p1.typelem = 0);
53+
oid|typname|oid|proname
54+
---+-------+---+-------
55+
(0 rows)
56+
57+
QUERY: SELECT p1.oid, p1.typname, p2.oid, p2.proname
58+
FROM pg_type AS p1, pg_proc AS p2
59+
WHERE p1.typreceive = p2.oid AND p1.typtype = 'b' AND
60+
(p2.pronargs != 1 OR p2.proretset) AND
61+
(p2.pronargs < 2 OR p2.pronargs > 3 OR p2.proretset OR p1.typelem = 0);
62+
oid|typname|oid|proname
63+
---+-------+---+-------
64+
(0 rows)
65+
66+
QUERY: SELECT p1.oid, p1.typname, p2.oid, p2.proname
67+
FROM pg_type AS p1, pg_proc AS p2
68+
WHERE p1.typsend = p2.oid AND p1.typtype = 'b' AND
69+
(p2.pronargs != 1 OR p2.proretset) AND
70+
(p2.pronargs != 2 OR p2.proretset OR p1.typelem = 0);
71+
oid|typname|oid|proname
72+
---+-------+---+-------
73+
(0 rows)
74+
75+
QUERY: SELECT p1.oid, p1.relname
76+
FROM pg_class as p1
77+
WHERE (p1.relkind != 'r' AND p1.relkind != 'i' AND p1.relkind != 's');
78+
oid|relname
79+
---+-------
80+
(0 rows)
81+
82+
QUERY: SELECT p1.oid, p1.relname
83+
FROM pg_class as p1
84+
WHERE (p1.relkind = 'i' AND p1.relam = 0) OR
85+
(p1.relkind != 'i' AND p1.relam != 0);
86+
oid|relname
87+
---+-------
88+
(0 rows)
89+
90+
QUERY: SELECT p1.oid, p1.attrelid, p1.attname
91+
FROM pg_attribute as p1
92+
WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR
93+
p1.attcacheoff != -1;
94+
oid|attrelid|attname
95+
---+--------+-------
96+
(0 rows)
97+
98+
QUERY: SELECT p1.oid, p1.attname, p2.oid, p2.attname
99+
FROM pg_attribute AS p1, pg_attribute AS p2
100+
WHERE p1.oid != p2.oid AND
101+
p1.attrelid = p2.attrelid AND
102+
(p1.attname = p2.attname OR p1.attnum = p2.attnum);
103+
oid|attname|oid|attname
104+
---+-------+---+-------
105+
(0 rows)
106+
107+
QUERY: SELECT p1.oid, p1.attname, p2.oid, p2.relname
108+
FROM pg_attribute AS p1, pg_class AS p2
109+
WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts;
110+
oid|attname|oid|relname
111+
---+-------+---+-------
112+
(0 rows)
113+
114+
QUERY: SELECT p1.oid, p1.relname
115+
FROM pg_class AS p1
116+
WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2
117+
WHERE p2.attrelid = p1.oid AND p2.attnum > 0);
118+
oid|relname
119+
---+-------
120+
(0 rows)
121+
122+
QUERY: SELECT p1.oid, p1.attname, p2.oid, p2.typname
123+
FROM pg_attribute AS p1, pg_type AS p2
124+
WHERE p1.atttypid = p2.oid AND
125+
(p1.attlen != p2.typlen OR
126+
p1.attalign != p2.typalign OR
127+
p1.attbyval != p2.typbyval);
128+
oid|attname|oid|typname
129+
---+-------+---+-------
130+
(0 rows)
131+

‎src/test/regress/sql/tests

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@ horology
2727
inet
2828
comments
2929
oidjoins
30+
type_sanity
3031
opr_sanity
3132
create_function_1
3233
create_type

‎src/test/regress/sql/type_sanity.sql

Lines changed: 153 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,153 @@
1+
--
2+
-- Sanity checks for common errors in making type-related system tables:
3+
-- pg_type, pg_class, pg_attribute.
4+
--
5+
-- None of the SELECTs here should ever find any matching entries,
6+
-- so the expected output is easy to maintain ;-).
7+
-- A test failure indicates someone messed up an entry in the system tables.
8+
--
9+
-- NB: we assume the oidjoins test will have caught any dangling links,
10+
-- that is OID or REGPROC fields that are not zero and do not match some
11+
-- row in the linked-to table. However, if we want to enforce that a link
12+
-- field can't be 0, we have to check it here.
13+
14+
-- **************** pg_type ****************
15+
16+
-- Look for illegal values in pg_type fields.
17+
18+
SELECTp1.oid,p1.typname
19+
FROM pg_typeas p1
20+
WHERE (p1.typlen<=0ANDp1.typlen!=-1)OR
21+
(p1.typtype!='b'ANDp1.typtype!='c')OR
22+
NOTp1.typisdefinedOR
23+
(p1.typalign!='c'ANDp1.typalign!='s'AND
24+
p1.typalign!='i'ANDp1.typalign!='d');
25+
26+
-- Look for "pass by value" types that can't be passed by value.
27+
28+
SELECTp1.oid,p1.typname
29+
FROM pg_typeas p1
30+
WHEREp1.typbyvalAND
31+
(p1.typlen!=1ORp1.typalign!='c')AND
32+
(p1.typlen!=2ORp1.typalign!='s')AND
33+
(p1.typlen!=4ORp1.typalign!='i');
34+
35+
-- Look for complex types that do not have a typrelid entry,
36+
-- or basic types that do.
37+
38+
SELECTp1.oid,p1.typname
39+
FROM pg_typeas p1
40+
WHERE (p1.typtype='c'ANDp1.typrelid=0)OR
41+
(p1.typtype!='c'ANDp1.typrelid!=0);
42+
43+
-- Conversion routines must be provided except in 'c' entries.
44+
45+
SELECTp1.oid,p1.typname
46+
FROM pg_typeas p1
47+
WHEREp1.typtype!='c'AND
48+
(p1.typinput=0ORp1.typoutput=0OR
49+
p1.typreceive=0ORp1.typsend=0);
50+
51+
-- Check for bogus typinput routines
52+
-- The first OR subclause detects bogus non-array cases,
53+
-- the second one detects bogus array cases.
54+
-- FIXME: ought to check prorettype, but there are special cases that make it
55+
-- hard: prorettype might be binary-compatible with the type but not the same,
56+
-- and for array types array_in's result has nothing to do with anything.
57+
58+
SELECTp1.oid,p1.typname,p2.oid,p2.proname
59+
FROM pg_typeAS p1, pg_procAS p2
60+
WHEREp1.typinput=p2.oidANDp1.typtype='b'AND
61+
(p2.pronargs!=1ORp2.proretset)AND
62+
(p2.pronargs<2ORp2.pronargs>3ORp2.proretsetORp1.typelem=0);
63+
64+
-- Check for bogus typoutput routines
65+
-- The first OR subclause detects bogus non-array cases,
66+
-- the second one detects bogus array cases.
67+
-- FIXME: ought to check prorettype, but not clear what it should be.
68+
69+
SELECTp1.oid,p1.typname,p2.oid,p2.proname
70+
FROM pg_typeAS p1, pg_procAS p2
71+
WHEREp1.typoutput=p2.oidANDp1.typtype='b'AND
72+
(p2.pronargs!=1ORp2.proretset)AND
73+
(p2.pronargs!=2ORp2.proretsetORp1.typelem=0);
74+
75+
-- Check for bogus typreceive routines
76+
-- The first OR subclause detects bogus non-array cases,
77+
-- the second one detects bogus array cases.
78+
-- FIXME: ought to check prorettype, but there are special cases that make it
79+
-- hard: prorettype might be binary-compatible with the type but not the same,
80+
-- and for array types array_in's result has nothing to do with anything.
81+
82+
SELECTp1.oid,p1.typname,p2.oid,p2.proname
83+
FROM pg_typeAS p1, pg_procAS p2
84+
WHEREp1.typreceive=p2.oidANDp1.typtype='b'AND
85+
(p2.pronargs!=1ORp2.proretset)AND
86+
(p2.pronargs<2ORp2.pronargs>3ORp2.proretsetORp1.typelem=0);
87+
88+
-- Check for bogus typsend routines
89+
-- The first OR subclause detects bogus non-array cases,
90+
-- the second one detects bogus array cases.
91+
-- FIXME: ought to check prorettype, but not clear what it should be.
92+
93+
SELECTp1.oid,p1.typname,p2.oid,p2.proname
94+
FROM pg_typeAS p1, pg_procAS p2
95+
WHEREp1.typsend=p2.oidANDp1.typtype='b'AND
96+
(p2.pronargs!=1ORp2.proretset)AND
97+
(p2.pronargs!=2ORp2.proretsetORp1.typelem=0);
98+
99+
-- **************** pg_class ****************
100+
101+
-- Look for illegal values in pg_class fields
102+
103+
SELECTp1.oid,p1.relname
104+
FROM pg_classas p1
105+
WHERE (p1.relkind!='r'ANDp1.relkind!='i'ANDp1.relkind!='s');
106+
107+
-- Indexes should have an access method, others not.
108+
109+
SELECTp1.oid,p1.relname
110+
FROM pg_classas p1
111+
WHERE (p1.relkind='i'ANDp1.relam=0)OR
112+
(p1.relkind!='i'ANDp1.relam!=0);
113+
114+
-- **************** pg_attribute ****************
115+
116+
-- Look for illegal values in pg_attribute fields
117+
118+
SELECTp1.oid,p1.attrelid,p1.attname
119+
FROM pg_attributeas p1
120+
WHEREp1.attrelid=0ORp1.atttypid=0ORp1.attnum=0OR
121+
p1.attcacheoff!=-1;
122+
123+
-- Look for duplicate pg_attribute entries
124+
-- (This would not be necessary if the indexes on pg_attribute were UNIQUE?)
125+
126+
SELECTp1.oid,p1.attname,p2.oid,p2.attname
127+
FROM pg_attributeAS p1, pg_attributeAS p2
128+
WHEREp1.oid!=p2.oidAND
129+
p1.attrelid=p2.attrelidAND
130+
(p1.attname=p2.attnameORp1.attnum=p2.attnum);
131+
132+
-- Cross-check attnum against parent relation
133+
134+
SELECTp1.oid,p1.attname,p2.oid,p2.relname
135+
FROM pg_attributeAS p1, pg_classAS p2
136+
WHEREp1.attrelid=p2.oidANDp1.attnum>p2.relnatts;
137+
138+
-- Detect missing pg_attribute entries: should have as many non-system
139+
-- attributes as parent relation expects
140+
141+
SELECTp1.oid,p1.relname
142+
FROM pg_classAS p1
143+
WHEREp1.relnatts!= (SELECTcount(*)FROM pg_attributeAS p2
144+
WHEREp2.attrelid=p1.oidANDp2.attnum>0);
145+
146+
-- Cross-check against pg_type entry
147+
148+
SELECTp1.oid,p1.attname,p2.oid,p2.typname
149+
FROM pg_attributeAS p1, pg_typeAS p2
150+
WHEREp1.atttypid=p2.oidAND
151+
(p1.attlen!=p2.typlenOR
152+
p1.attalign!=p2.typalignOR
153+
p1.attbyval!=p2.typbyval);

0 commit comments

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