3 -- Sanity checks for common errors in making type-related system tables:
4 -- pg_type, pg_class, pg_attribute, pg_range.
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', 'r')) 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 -- Look for range types that do not have a pg_range entry
85 SELECT p1.oid, p1.typname
87 WHERE p1.typtype = 'r' AND
88 NOT EXISTS(SELECT 1 FROM pg_range r WHERE rngtypid = p1.oid);
93 -- Look for range types whose typalign isn't sufficient
94 SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign
96 LEFT JOIN pg_range as r ON rngtypid = p1.oid
97 LEFT JOIN pg_type as p2 ON rngsubtype = p2.oid
98 WHERE p1.typtype = 'r' AND
99 (p1.typalign != (CASE WHEN p2.typalign = 'd' THEN 'd'::"char"
100 ELSE 'i'::"char" END)
102 oid | typname | typalign | typname | typalign
103 -----+---------+----------+---------+----------
106 -- Text conversion routines must be provided.
107 SELECT p1.oid, p1.typname
109 WHERE (p1.typinput = 0 OR p1.typoutput = 0);
114 -- Check for bogus typinput routines
115 SELECT p1.oid, p1.typname, p2.oid, p2.proname
116 FROM pg_type AS p1, pg_proc AS p2
117 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
118 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'cstring'::regtype) OR
119 (p2.pronargs = 3 AND p2.proargtypes[0] = 'cstring'::regtype AND
120 p2.proargtypes[1] = 'oid'::regtype AND
121 p2.proargtypes[2] = 'int4'::regtype));
122 oid | typname | oid | proname
123 -----+---------+-----+---------
126 -- As of 8.0, this check finds refcursor, which is borrowing
127 -- other types' I/O routines
128 SELECT p1.oid, p1.typname, p2.oid, p2.proname
129 FROM pg_type AS p1, pg_proc AS p2
130 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
131 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
132 (p2.prorettype = p1.oid AND NOT p2.proretset)
134 oid | typname | oid | proname
135 ------+-----------+-----+---------
136 1790 | refcursor | 46 | textin
139 -- Varlena array types will point to array_in
140 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
141 SELECT p1.oid, p1.typname, p2.oid, p2.proname
142 FROM pg_type AS p1, pg_proc AS p2
143 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND
144 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
145 (p2.oid = 'array_in'::regproc)
147 oid | typname | oid | proname
148 -----+------------+-----+--------------
149 22 | int2vector | 40 | int2vectorin
150 30 | oidvector | 54 | oidvectorin
153 -- Check for bogus typoutput routines
154 -- As of 8.0, this check finds refcursor, which is borrowing
155 -- other types' I/O routines
156 SELECT p1.oid, p1.typname, p2.oid, p2.proname
157 FROM pg_type AS p1, pg_proc AS p2
158 WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
160 (p2.proargtypes[0] = p1.oid OR
161 (p2.oid = 'array_out'::regproc AND
162 p1.typelem != 0 AND p1.typlen = -1)))
164 oid | typname | oid | proname
165 ------+-----------+-----+---------
166 1790 | refcursor | 47 | textout
169 SELECT p1.oid, p1.typname, p2.oid, p2.proname
170 FROM pg_type AS p1, pg_proc AS p2
171 WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
172 (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
173 oid | typname | oid | proname
174 -----+---------+-----+---------
177 -- Check for bogus typreceive routines
178 SELECT p1.oid, p1.typname, p2.oid, p2.proname
179 FROM pg_type AS p1, pg_proc AS p2
180 WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
181 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR
182 (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND
183 p2.proargtypes[1] = 'oid'::regtype AND
184 p2.proargtypes[2] = 'int4'::regtype));
185 oid | typname | oid | proname
186 -----+---------+-----+---------
189 -- As of 7.4, this check finds refcursor, which is borrowing
190 -- other types' I/O routines
191 SELECT p1.oid, p1.typname, p2.oid, p2.proname
192 FROM pg_type AS p1, pg_proc AS p2
193 WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
194 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
195 (p2.prorettype = p1.oid AND NOT p2.proretset)
197 oid | typname | oid | proname
198 ------+-----------+------+----------
199 1790 | refcursor | 2414 | textrecv
202 -- Varlena array types will point to array_recv
203 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
204 SELECT p1.oid, p1.typname, p2.oid, p2.proname
205 FROM pg_type AS p1, pg_proc AS p2
206 WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND
207 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
208 (p2.oid = 'array_recv'::regproc)
210 oid | typname | oid | proname
211 -----+------------+------+----------------
212 22 | int2vector | 2410 | int2vectorrecv
213 30 | oidvector | 2420 | oidvectorrecv
216 -- Suspicious if typreceive doesn't take same number of args as typinput
217 SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname
218 FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3
219 WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND
220 p2.pronargs != p3.pronargs;
221 oid | typname | oid | proname | oid | proname
222 -----+---------+-----+---------+-----+---------
225 -- Check for bogus typsend routines
226 -- As of 7.4, this check finds refcursor, which is borrowing
227 -- other types' I/O routines
228 SELECT p1.oid, p1.typname, p2.oid, p2.proname
229 FROM pg_type AS p1, pg_proc AS p2
230 WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
232 (p2.proargtypes[0] = p1.oid OR
233 (p2.oid = 'array_send'::regproc AND
234 p1.typelem != 0 AND p1.typlen = -1)))
236 oid | typname | oid | proname
237 ------+-----------+------+----------
238 1790 | refcursor | 2415 | textsend
241 SELECT p1.oid, p1.typname, p2.oid, p2.proname
242 FROM pg_type AS p1, pg_proc AS p2
243 WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
244 (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset);
245 oid | typname | oid | proname
246 -----+---------+-----+---------
249 -- Check for bogus typmodin routines
250 SELECT p1.oid, p1.typname, p2.oid, p2.proname
251 FROM pg_type AS p1, pg_proc AS p2
252 WHERE p1.typmodin = p2.oid AND p1.typtype in ('b', 'p') AND NOT
254 p2.proargtypes[0] = 'cstring[]'::regtype AND
255 p2.prorettype = 'int4'::regtype AND NOT p2.proretset);
256 oid | typname | oid | proname
257 -----+---------+-----+---------
260 -- Check for bogus typmodout routines
261 SELECT p1.oid, p1.typname, p2.oid, p2.proname
262 FROM pg_type AS p1, pg_proc AS p2
263 WHERE p1.typmodout = p2.oid AND p1.typtype in ('b', 'p') AND NOT
265 p2.proargtypes[0] = 'int4'::regtype AND
266 p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
267 oid | typname | oid | proname
268 -----+---------+-----+---------
271 -- Array types should have same typmodin/out as their element types
272 SELECT p1.oid, p1.typname, p2.oid, p2.typname
273 FROM pg_type AS p1, pg_type AS p2
274 WHERE p1.typelem = p2.oid AND NOT
275 (p1.typmodin = p2.typmodin AND p1.typmodout = p2.typmodout);
276 oid | typname | oid | typname
277 -----+---------+-----+---------
280 -- Array types should have same typdelim as their element types
281 SELECT p1.oid, p1.typname, p2.oid, p2.typname
282 FROM pg_type AS p1, pg_type AS p2
283 WHERE p1.typarray = p2.oid AND NOT (p1.typdelim = p2.typdelim);
284 oid | typname | oid | typname
285 -----+---------+-----+---------
288 -- Look for array types whose typalign isn't sufficient
289 SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign
290 FROM pg_type AS p1, pg_type AS p2
291 WHERE p1.typarray = p2.oid AND
292 p2.typalign != (CASE WHEN p1.typalign = 'd' THEN 'd'::"char"
293 ELSE 'i'::"char" END);
294 oid | typname | typalign | typname | typalign
295 -----+---------+----------+---------+----------
298 -- Check for bogus typanalyze routines
299 SELECT p1.oid, p1.typname, p2.oid, p2.proname
300 FROM pg_type AS p1, pg_proc AS p2
301 WHERE p1.typanalyze = p2.oid AND p1.typtype in ('b', 'p') AND NOT
303 p2.proargtypes[0] = 'internal'::regtype AND
304 p2.prorettype = 'bool'::regtype AND NOT p2.proretset);
305 oid | typname | oid | proname
306 -----+---------+-----+---------
309 -- **************** pg_class ****************
310 -- Look for illegal values in pg_class fields
311 SELECT p1.oid, p1.relname
313 WHERE p1.relkind NOT IN ('r', 'i', 's', 'S', 'c', 't', 'v', 'f');
318 -- Indexes should have an access method, others not.
319 SELECT p1.oid, p1.relname
321 WHERE (p1.relkind = 'i' AND p1.relam = 0) OR
322 (p1.relkind != 'i' AND p1.relam != 0);
327 -- **************** pg_attribute ****************
328 -- Look for illegal values in pg_attribute fields
329 SELECT p1.attrelid, p1.attname
330 FROM pg_attribute as p1
331 WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR
332 p1.attcacheoff != -1 OR p1.attinhcount < 0 OR
333 (p1.attinhcount = 0 AND NOT p1.attislocal);
338 -- Cross-check attnum against parent relation
339 SELECT p1.attrelid, p1.attname, p2.oid, p2.relname
340 FROM pg_attribute AS p1, pg_class AS p2
341 WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts;
342 attrelid | attname | oid | relname
343 ----------+---------+-----+---------
346 -- Detect missing pg_attribute entries: should have as many non-system
347 -- attributes as parent relation expects
348 SELECT p1.oid, p1.relname
350 WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2
351 WHERE p2.attrelid = p1.oid AND p2.attnum > 0);
356 -- Cross-check against pg_type entry
357 -- NOTE: we allow attstorage to be 'plain' even when typstorage is not;
358 -- this is mainly for toast tables.
359 SELECT p1.attrelid, p1.attname, p2.oid, p2.typname
360 FROM pg_attribute AS p1, pg_type AS p2
361 WHERE p1.atttypid = p2.oid AND
362 (p1.attlen != p2.typlen OR
363 p1.attalign != p2.typalign OR
364 p1.attbyval != p2.typbyval OR
365 (p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));
366 attrelid | attname | oid | typname
367 ----------+---------+-----+---------
370 -- **************** pg_range ****************
371 -- Look for illegal values in pg_range fields.
372 SELECT p1.rngtypid, p1.rngsubtype
374 WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0;
375 rngtypid | rngsubtype
376 ----------+------------
379 -- rngcollation should be specified iff subtype is collatable
380 SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation
381 FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype
382 WHERE (rngcollation = 0) != (typcollation = 0);
383 rngtypid | rngsubtype | rngcollation | typcollation
384 ----------+------------+--------------+--------------
387 -- opclass had better be a btree opclass accepting the subtype.
388 -- We must allow anyarray matches, cf opr_sanity's binary_coercible()
389 SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname
390 FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc
391 WHERE o.opcmethod != 403 OR
392 ((o.opcintype != p1.rngsubtype) AND NOT
393 (o.opcintype = 'pg_catalog.anyarray'::regtype AND
394 EXISTS(select 1 from pg_catalog.pg_type where
395 oid = p1.rngsubtype and typelem != 0 and typlen = -1)));
396 rngtypid | rngsubtype | opcmethod | opcname
397 ----------+------------+-----------+---------
400 -- canonical function, if any, had better match the range type
401 SELECT p1.rngtypid, p1.rngsubtype, p.proname
402 FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical
403 WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid;
404 rngtypid | rngsubtype | proname
405 ----------+------------+---------
408 -- subdiff function, if any, had better match the subtype
409 SELECT p1.rngtypid, p1.rngsubtype, p.proname
410 FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff
412 OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype
413 OR prorettype != 'pg_catalog.float8'::regtype;
414 rngtypid | rngsubtype | proname
415 ----------+------------+---------