3
-- Sanity checks for common errors in making type-related system tables:
4
-- pg_type, pg_class, pg_attribute.
6
-- None of the SELECTs here should ever find any matching entries,
7
-- so the expected output is easy to maintain ;-).
8
-- A test failure indicates someone messed up an entry in the system tables.
10
-- NB: we assume the oidjoins test will have caught any dangling links,
11
-- that is OID or REGPROC fields that are not zero and do not match some
12
-- row in the linked-to table. However, if we want to enforce that a link
13
-- field can't be 0, we have to check it here.
14
-- **************** pg_type ****************
15
-- Look for illegal values in pg_type fields.
16
SELECT p1.oid, p1.typname
18
WHERE p1.typnamespace = 0 OR
19
(p1.typlen <= 0 AND p1.typlen != -1 AND p1.typlen != -2) OR
20
(p1.typtype not in ('b', 'c', 'd', 'e', 'p')) OR
21
NOT p1.typisdefined OR
22
(p1.typalign not in ('c', 's', 'i', 'd')) OR
23
(p1.typstorage not in ('p', 'x', 'e', 'm'));
28
-- Look for "pass by value" types that can't be passed by value.
29
SELECT p1.oid, p1.typname
32
(p1.typlen != 1 OR p1.typalign != 'c') AND
33
(p1.typlen != 2 OR p1.typalign != 's') AND
34
(p1.typlen != 4 OR p1.typalign != 'i') AND
35
(p1.typlen != 8 OR p1.typalign != 'd');
40
-- Look for "toastable" types that aren't varlena.
41
SELECT p1.oid, p1.typname
43
WHERE p1.typstorage != 'p' AND
44
(p1.typbyval OR p1.typlen != -1);
49
-- Look for complex types that do not have a typrelid entry,
50
-- or basic types that do.
51
SELECT p1.oid, p1.typname
53
WHERE (p1.typtype = 'c' AND p1.typrelid = 0) OR
54
(p1.typtype != 'c' AND p1.typrelid != 0);
59
-- Look for basic or enum types that don't have an array type.
60
-- NOTE: as of 9.1, this check finds pg_node_tree, smgr, and unknown.
61
SELECT p1.oid, p1.typname
63
WHERE p1.typtype in ('b','e') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
64
(SELECT 1 FROM pg_type as p2
65
WHERE p2.typname = ('_' || p1.typname)::name AND
66
p2.typelem = p1.oid and p1.typarray = p2.oid);
74
-- Make sure typarray points to a varlena array type of our own base
75
SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype,
77
FROM pg_type p1 LEFT JOIN pg_type p2 ON (p1.typarray = p2.oid)
78
WHERE p1.typarray <> 0 AND
79
(p2.oid IS NULL OR p2.typelem <> p1.oid OR p2.typlen <> -1);
80
oid | basetype | arraytype | typelem | typlen
81
-----+----------+-----------+---------+--------
84
-- Text conversion routines must be provided.
85
SELECT p1.oid, p1.typname
87
WHERE (p1.typinput = 0 OR p1.typoutput = 0);
92
-- Check for bogus typinput routines
93
SELECT p1.oid, p1.typname, p2.oid, p2.proname
94
FROM pg_type AS p1, pg_proc AS p2
95
WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
96
((p2.pronargs = 1 AND p2.proargtypes[0] = 'cstring'::regtype) OR
97
(p2.pronargs = 3 AND p2.proargtypes[0] = 'cstring'::regtype AND
98
p2.proargtypes[1] = 'oid'::regtype AND
99
p2.proargtypes[2] = 'int4'::regtype));
100
oid | typname | oid | proname
101
-----+---------+-----+---------
104
-- As of 8.0, this check finds refcursor, which is borrowing
105
-- other types' I/O routines
106
SELECT p1.oid, p1.typname, p2.oid, p2.proname
107
FROM pg_type AS p1, pg_proc AS p2
108
WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
109
(p1.typelem != 0 AND p1.typlen < 0) AND NOT
110
(p2.prorettype = p1.oid AND NOT p2.proretset)
112
oid | typname | oid | proname
113
------+-----------+-----+---------
114
1790 | refcursor | 46 | textin
117
-- Varlena array types will point to array_in
118
-- Exception as of 8.1: int2vector and oidvector have their own I/O routines
119
SELECT p1.oid, p1.typname, p2.oid, p2.proname
120
FROM pg_type AS p1, pg_proc AS p2
121
WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND
122
(p1.typelem != 0 AND p1.typlen < 0) AND NOT
123
(p2.oid = 'array_in'::regproc)
125
oid | typname | oid | proname
126
-----+------------+-----+--------------
127
22 | int2vector | 40 | int2vectorin
128
30 | oidvector | 54 | oidvectorin
131
-- Check for bogus typoutput routines
132
-- As of 8.0, this check finds refcursor, which is borrowing
133
-- other types' I/O routines
134
SELECT p1.oid, p1.typname, p2.oid, p2.proname
135
FROM pg_type AS p1, pg_proc AS p2
136
WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
138
(p2.proargtypes[0] = p1.oid OR
139
(p2.oid = 'array_out'::regproc AND
140
p1.typelem != 0 AND p1.typlen = -1)))
142
oid | typname | oid | proname
143
------+-----------+-----+---------
144
1790 | refcursor | 47 | textout
147
SELECT p1.oid, p1.typname, p2.oid, p2.proname
148
FROM pg_type AS p1, pg_proc AS p2
149
WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
150
(p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
151
oid | typname | oid | proname
152
-----+---------+-----+---------
155
-- Check for bogus typreceive routines
156
SELECT p1.oid, p1.typname, p2.oid, p2.proname
157
FROM pg_type AS p1, pg_proc AS p2
158
WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
159
((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR
160
(p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND
161
p2.proargtypes[1] = 'oid'::regtype AND
162
p2.proargtypes[2] = 'int4'::regtype));
163
oid | typname | oid | proname
164
-----+---------+-----+---------
167
-- As of 7.4, this check finds refcursor, which is borrowing
168
-- other types' I/O routines
169
SELECT p1.oid, p1.typname, p2.oid, p2.proname
170
FROM pg_type AS p1, pg_proc AS p2
171
WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
172
(p1.typelem != 0 AND p1.typlen < 0) AND NOT
173
(p2.prorettype = p1.oid AND NOT p2.proretset)
175
oid | typname | oid | proname
176
------+-----------+------+----------
177
1790 | refcursor | 2414 | textrecv
180
-- Varlena array types will point to array_recv
181
-- Exception as of 8.1: int2vector and oidvector have their own I/O routines
182
SELECT p1.oid, p1.typname, p2.oid, p2.proname
183
FROM pg_type AS p1, pg_proc AS p2
184
WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND
185
(p1.typelem != 0 AND p1.typlen < 0) AND NOT
186
(p2.oid = 'array_recv'::regproc)
188
oid | typname | oid | proname
189
-----+------------+------+----------------
190
22 | int2vector | 2410 | int2vectorrecv
191
30 | oidvector | 2420 | oidvectorrecv
194
-- Suspicious if typreceive doesn't take same number of args as typinput
195
SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname
196
FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3
197
WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND
198
p2.pronargs != p3.pronargs;
199
oid | typname | oid | proname | oid | proname
200
-----+---------+-----+---------+-----+---------
203
-- Check for bogus typsend routines
204
-- As of 7.4, this check finds refcursor, which is borrowing
205
-- other types' I/O routines
206
SELECT p1.oid, p1.typname, p2.oid, p2.proname
207
FROM pg_type AS p1, pg_proc AS p2
208
WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
210
(p2.proargtypes[0] = p1.oid OR
211
(p2.oid = 'array_send'::regproc AND
212
p1.typelem != 0 AND p1.typlen = -1)))
214
oid | typname | oid | proname
215
------+-----------+------+----------
216
1790 | refcursor | 2415 | textsend
219
SELECT p1.oid, p1.typname, p2.oid, p2.proname
220
FROM pg_type AS p1, pg_proc AS p2
221
WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
222
(p2.prorettype = 'bytea'::regtype AND NOT p2.proretset);
223
oid | typname | oid | proname
224
-----+---------+-----+---------
227
-- Check for bogus typmodin routines
228
SELECT p1.oid, p1.typname, p2.oid, p2.proname
229
FROM pg_type AS p1, pg_proc AS p2
230
WHERE p1.typmodin = p2.oid AND p1.typtype in ('b', 'p') AND NOT
232
p2.proargtypes[0] = 'cstring[]'::regtype AND
233
p2.prorettype = 'int4'::regtype AND NOT p2.proretset);
234
oid | typname | oid | proname
235
-----+---------+-----+---------
238
-- Check for bogus typmodout routines
239
SELECT p1.oid, p1.typname, p2.oid, p2.proname
240
FROM pg_type AS p1, pg_proc AS p2
241
WHERE p1.typmodout = p2.oid AND p1.typtype in ('b', 'p') AND NOT
243
p2.proargtypes[0] = 'int4'::regtype AND
244
p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
245
oid | typname | oid | proname
246
-----+---------+-----+---------
249
-- Array types should have same typmodin/out as their element types
250
SELECT p1.oid, p1.typname, p2.oid, p2.typname
251
FROM pg_type AS p1, pg_type AS p2
252
WHERE p1.typelem = p2.oid AND NOT
253
(p1.typmodin = p2.typmodin AND p1.typmodout = p2.typmodout);
254
oid | typname | oid | typname
255
-----+---------+-----+---------
258
-- Array types should have same typdelim as their element types
259
SELECT p1.oid, p1.typname, p2.oid, p2.typname
260
FROM pg_type AS p1, pg_type AS p2
261
WHERE p1.typarray = p2.oid AND NOT (p1.typdelim = p2.typdelim);
262
oid | typname | oid | typname
263
-----+---------+-----+---------
266
-- Check for bogus typanalyze routines
267
SELECT p1.oid, p1.typname, p2.oid, p2.proname
268
FROM pg_type AS p1, pg_proc AS p2
269
WHERE p1.typanalyze = p2.oid AND p1.typtype in ('b', 'p') AND NOT
271
p2.proargtypes[0] = 'internal'::regtype AND
272
p2.prorettype = 'bool'::regtype AND NOT p2.proretset);
273
oid | typname | oid | proname
274
-----+---------+-----+---------
277
-- **************** pg_class ****************
278
-- Look for illegal values in pg_class fields
279
SELECT p1.oid, p1.relname
281
WHERE p1.relkind NOT IN ('r', 'i', 's', 'S', 'c', 't', 'v', 'f');
286
-- Indexes should have an access method, others not.
287
SELECT p1.oid, p1.relname
289
WHERE (p1.relkind = 'i' AND p1.relam = 0) OR
290
(p1.relkind != 'i' AND p1.relam != 0);
295
-- **************** pg_attribute ****************
296
-- Look for illegal values in pg_attribute fields
297
SELECT p1.attrelid, p1.attname
298
FROM pg_attribute as p1
299
WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR
300
p1.attcacheoff != -1 OR p1.attinhcount < 0 OR
301
(p1.attinhcount = 0 AND NOT p1.attislocal);
306
-- Cross-check attnum against parent relation
307
SELECT p1.attrelid, p1.attname, p2.oid, p2.relname
308
FROM pg_attribute AS p1, pg_class AS p2
309
WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts;
310
attrelid | attname | oid | relname
311
----------+---------+-----+---------
314
-- Detect missing pg_attribute entries: should have as many non-system
315
-- attributes as parent relation expects
316
SELECT p1.oid, p1.relname
318
WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2
319
WHERE p2.attrelid = p1.oid AND p2.attnum > 0);
324
-- Cross-check against pg_type entry
325
-- NOTE: we allow attstorage to be 'plain' even when typstorage is not;
326
-- this is mainly for toast tables.
327
SELECT p1.attrelid, p1.attname, p2.oid, p2.typname
328
FROM pg_attribute AS p1, pg_type AS p2
329
WHERE p1.atttypid = p2.oid AND
330
(p1.attlen != p2.typlen OR
331
p1.attalign != p2.typalign OR
332
p1.attbyval != p2.typbyval OR
333
(p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));
334
attrelid | attname | oid | typname
335
----------+---------+-----+---------