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 types that should have an array type according to their typtype,
60 -- but don't. We exclude composites here because we have not bothered to
61 -- make array types corresponding to the system catalogs' rowtypes.
62 -- NOTE: as of 9.1, this check finds pg_node_tree, smgr, and unknown.
63 SELECT p1.oid, p1.typname
65 WHERE p1.typtype not in ('c','d','p') AND p1.typname NOT LIKE E'\\_%'
67 (SELECT 1 FROM pg_type as p2
68 WHERE p2.typname = ('_' || p1.typname)::name AND
69 p2.typelem = p1.oid and p1.typarray = p2.oid);
77 -- Make sure typarray points to a varlena array type of our own base
78 SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype,
80 FROM pg_type p1 LEFT JOIN pg_type p2 ON (p1.typarray = p2.oid)
81 WHERE p1.typarray <> 0 AND
82 (p2.oid IS NULL OR p2.typelem <> p1.oid OR p2.typlen <> -1);
83 oid | basetype | arraytype | typelem | typlen
84 -----+----------+-----------+---------+--------
87 -- Look for range types that do not have a pg_range entry
88 SELECT p1.oid, p1.typname
90 WHERE p1.typtype = 'r' AND
91 NOT EXISTS(SELECT 1 FROM pg_range r WHERE rngtypid = p1.oid);
96 -- Look for range types whose typalign isn't sufficient
97 SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign
99 LEFT JOIN pg_range as r ON rngtypid = p1.oid
100 LEFT JOIN pg_type as p2 ON rngsubtype = p2.oid
101 WHERE p1.typtype = 'r' AND
102 (p1.typalign != (CASE WHEN p2.typalign = 'd' THEN 'd'::"char"
103 ELSE 'i'::"char" END)
105 oid | typname | typalign | typname | typalign
106 -----+---------+----------+---------+----------
109 -- Text conversion routines must be provided.
110 SELECT p1.oid, p1.typname
112 WHERE (p1.typinput = 0 OR p1.typoutput = 0);
117 -- Check for bogus typinput routines
118 SELECT p1.oid, p1.typname, p2.oid, p2.proname
119 FROM pg_type AS p1, pg_proc AS p2
120 WHERE p1.typinput = p2.oid AND NOT
121 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'cstring'::regtype) OR
122 (p2.pronargs = 2 AND p2.proargtypes[0] = 'cstring'::regtype AND
123 p2.proargtypes[1] = 'oid'::regtype) OR
124 (p2.pronargs = 3 AND p2.proargtypes[0] = 'cstring'::regtype AND
125 p2.proargtypes[1] = 'oid'::regtype AND
126 p2.proargtypes[2] = 'int4'::regtype));
127 oid | typname | oid | proname
128 -----+---------+-----+---------
131 -- As of 8.0, this check finds refcursor, which is borrowing
132 -- other types' I/O routines
133 SELECT p1.oid, p1.typname, p2.oid, p2.proname
134 FROM pg_type AS p1, pg_proc AS p2
135 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
136 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
137 (p2.prorettype = p1.oid AND NOT p2.proretset)
139 oid | typname | oid | proname
140 ------+-----------+-----+---------
141 1790 | refcursor | 46 | textin
144 -- Varlena array types will point to array_in
145 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
146 SELECT p1.oid, p1.typname, p2.oid, p2.proname
147 FROM pg_type AS p1, pg_proc AS p2
148 WHERE p1.typinput = p2.oid AND
149 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
150 (p2.oid = 'array_in'::regproc)
152 oid | typname | oid | proname
153 -----+------------+-----+--------------
154 22 | int2vector | 40 | int2vectorin
155 30 | oidvector | 54 | oidvectorin
158 -- Composites, domains, enums, ranges should all use the same input routines
159 SELECT DISTINCT typtype, typinput
161 WHERE p1.typtype not in ('b', 'p')
164 ---------+-----------
171 -- Check for bogus typoutput routines
172 -- As of 8.0, this check finds refcursor, which is borrowing
173 -- other types' I/O routines
174 SELECT p1.oid, p1.typname, p2.oid, p2.proname
175 FROM pg_type AS p1, pg_proc AS p2
176 WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
178 (p2.proargtypes[0] = p1.oid OR
179 (p2.oid = 'array_out'::regproc AND
180 p1.typelem != 0 AND p1.typlen = -1)))
182 oid | typname | oid | proname
183 ------+-----------+-----+---------
184 1790 | refcursor | 47 | textout
187 SELECT p1.oid, p1.typname, p2.oid, p2.proname
188 FROM pg_type AS p1, pg_proc AS p2
189 WHERE p1.typoutput = p2.oid AND NOT
190 (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
191 oid | typname | oid | proname
192 -----+---------+-----+---------
195 -- Composites, enums, ranges should all use the same output routines
196 SELECT DISTINCT typtype, typoutput
198 WHERE p1.typtype not in ('b', 'd', 'p')
201 ---------+------------
207 -- Domains should have same typoutput as their base types
208 SELECT p1.oid, p1.typname, p2.oid, p2.typname
209 FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid
210 WHERE p1.typtype = 'd' AND p1.typoutput IS DISTINCT FROM p2.typoutput;
211 oid | typname | oid | typname
212 -----+---------+-----+---------
215 -- Check for bogus typreceive routines
216 SELECT p1.oid, p1.typname, p2.oid, p2.proname
217 FROM pg_type AS p1, pg_proc AS p2
218 WHERE p1.typreceive = p2.oid AND NOT
219 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR
220 (p2.pronargs = 2 AND p2.proargtypes[0] = 'internal'::regtype AND
221 p2.proargtypes[1] = 'oid'::regtype) OR
222 (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND
223 p2.proargtypes[1] = 'oid'::regtype AND
224 p2.proargtypes[2] = 'int4'::regtype));
225 oid | typname | oid | proname
226 -----+---------+-----+---------
229 -- As of 7.4, this check finds refcursor, which is borrowing
230 -- other types' I/O routines
231 SELECT p1.oid, p1.typname, p2.oid, p2.proname
232 FROM pg_type AS p1, pg_proc AS p2
233 WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
234 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
235 (p2.prorettype = p1.oid AND NOT p2.proretset)
237 oid | typname | oid | proname
238 ------+-----------+------+----------
239 1790 | refcursor | 2414 | textrecv
242 -- Varlena array types will point to array_recv
243 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
244 SELECT p1.oid, p1.typname, p2.oid, p2.proname
245 FROM pg_type AS p1, pg_proc AS p2
246 WHERE p1.typreceive = p2.oid AND
247 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
248 (p2.oid = 'array_recv'::regproc)
250 oid | typname | oid | proname
251 -----+------------+------+----------------
252 22 | int2vector | 2410 | int2vectorrecv
253 30 | oidvector | 2420 | oidvectorrecv
256 -- Suspicious if typreceive doesn't take same number of args as typinput
257 SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname
258 FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3
259 WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND
260 p2.pronargs != p3.pronargs;
261 oid | typname | oid | proname | oid | proname
262 -----+---------+-----+---------+-----+---------
265 -- Composites, domains, enums, ranges should all use the same receive routines
266 SELECT DISTINCT typtype, typreceive
268 WHERE p1.typtype not in ('b', 'p')
271 ---------+-------------
278 -- Check for bogus typsend routines
279 -- As of 7.4, this check finds refcursor, which is borrowing
280 -- other types' I/O routines
281 SELECT p1.oid, p1.typname, p2.oid, p2.proname
282 FROM pg_type AS p1, pg_proc AS p2
283 WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
285 (p2.proargtypes[0] = p1.oid OR
286 (p2.oid = 'array_send'::regproc AND
287 p1.typelem != 0 AND p1.typlen = -1)))
289 oid | typname | oid | proname
290 ------+-----------+------+----------
291 1790 | refcursor | 2415 | textsend
294 SELECT p1.oid, p1.typname, p2.oid, p2.proname
295 FROM pg_type AS p1, pg_proc AS p2
296 WHERE p1.typsend = p2.oid AND NOT
297 (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset);
298 oid | typname | oid | proname
299 -----+---------+-----+---------
302 -- Composites, enums, ranges should all use the same send routines
303 SELECT DISTINCT typtype, typsend
305 WHERE p1.typtype not in ('b', 'd', 'p')
308 ---------+-------------
314 -- Domains should have same typsend as their base types
315 SELECT p1.oid, p1.typname, p2.oid, p2.typname
316 FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid
317 WHERE p1.typtype = 'd' AND p1.typsend IS DISTINCT FROM p2.typsend;
318 oid | typname | oid | typname
319 -----+---------+-----+---------
322 -- Check for bogus typmodin routines
323 SELECT p1.oid, p1.typname, p2.oid, p2.proname
324 FROM pg_type AS p1, pg_proc AS p2
325 WHERE p1.typmodin = p2.oid AND NOT
327 p2.proargtypes[0] = 'cstring[]'::regtype AND
328 p2.prorettype = 'int4'::regtype AND NOT p2.proretset);
329 oid | typname | oid | proname
330 -----+---------+-----+---------
333 -- Check for bogus typmodout routines
334 SELECT p1.oid, p1.typname, p2.oid, p2.proname
335 FROM pg_type AS p1, pg_proc AS p2
336 WHERE p1.typmodout = p2.oid AND NOT
338 p2.proargtypes[0] = 'int4'::regtype AND
339 p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
340 oid | typname | oid | proname
341 -----+---------+-----+---------
344 -- Array types should have same typmodin/out as their element types
345 SELECT p1.oid, p1.typname, p2.oid, p2.typname
346 FROM pg_type AS p1, pg_type AS p2
347 WHERE p1.typelem = p2.oid AND NOT
348 (p1.typmodin = p2.typmodin AND p1.typmodout = p2.typmodout);
349 oid | typname | oid | typname
350 -----+---------+-----+---------
353 -- Array types should have same typdelim as their element types
354 SELECT p1.oid, p1.typname, p2.oid, p2.typname
355 FROM pg_type AS p1, pg_type AS p2
356 WHERE p1.typarray = p2.oid AND NOT (p1.typdelim = p2.typdelim);
357 oid | typname | oid | typname
358 -----+---------+-----+---------
361 -- Look for array types whose typalign isn't sufficient
362 SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign
363 FROM pg_type AS p1, pg_type AS p2
364 WHERE p1.typarray = p2.oid AND
365 p2.typalign != (CASE WHEN p1.typalign = 'd' THEN 'd'::"char"
366 ELSE 'i'::"char" END);
367 oid | typname | typalign | typname | typalign
368 -----+---------+----------+---------+----------
371 -- Check for bogus typanalyze routines
372 SELECT p1.oid, p1.typname, p2.oid, p2.proname
373 FROM pg_type AS p1, pg_proc AS p2
374 WHERE p1.typanalyze = p2.oid AND NOT
376 p2.proargtypes[0] = 'internal'::regtype AND
377 p2.prorettype = 'bool'::regtype AND NOT p2.proretset);
378 oid | typname | oid | proname
379 -----+---------+-----+---------
382 -- domains inherit their base type's typanalyze
383 SELECT d.oid, d.typname, d.typanalyze, t.oid, t.typname, t.typanalyze
384 FROM pg_type d JOIN pg_type t ON d.typbasetype = t.oid
385 WHERE d.typanalyze != t.typanalyze;
386 oid | typname | typanalyze | oid | typname | typanalyze
387 -----+---------+------------+-----+---------+------------
390 -- range_typanalyze should be used for all and only range types
391 -- (but exclude domains, which we checked above)
392 SELECT t.oid, t.typname, t.typanalyze
393 FROM pg_type t LEFT JOIN pg_range r on t.oid = r.rngtypid
394 WHERE t.typbasetype = 0 AND
395 (t.typanalyze = 'range_typanalyze'::regproc) != (r.rngtypid IS NOT NULL);
396 oid | typname | typanalyze
397 -----+---------+------------
400 -- array_typanalyze should be used for all and only array types
401 -- (but exclude domains, which we checked above)
402 -- As of 9.2 this finds int2vector and oidvector, which are weird anyway
403 SELECT t.oid, t.typname, t.typanalyze
405 WHERE t.typbasetype = 0 AND
406 (t.typanalyze = 'array_typanalyze'::regproc) !=
407 (typelem != 0 AND typlen < 0)
409 oid | typname | typanalyze
410 -----+------------+------------
415 -- **************** pg_class ****************
416 -- Look for illegal values in pg_class fields
417 SELECT p1.oid, p1.relname
419 WHERE p1.relkind NOT IN ('r', 'i', 's', 'S', 'c', 't', 'v', 'f');
424 -- Indexes should have an access method, others not.
425 SELECT p1.oid, p1.relname
427 WHERE (p1.relkind = 'i' AND p1.relam = 0) OR
428 (p1.relkind != 'i' AND p1.relam != 0);
433 -- **************** pg_attribute ****************
434 -- Look for illegal values in pg_attribute fields
435 SELECT p1.attrelid, p1.attname
436 FROM pg_attribute as p1
437 WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR
438 p1.attcacheoff != -1 OR p1.attinhcount < 0 OR
439 (p1.attinhcount = 0 AND NOT p1.attislocal);
444 -- Cross-check attnum against parent relation
445 SELECT p1.attrelid, p1.attname, p2.oid, p2.relname
446 FROM pg_attribute AS p1, pg_class AS p2
447 WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts;
448 attrelid | attname | oid | relname
449 ----------+---------+-----+---------
452 -- Detect missing pg_attribute entries: should have as many non-system
453 -- attributes as parent relation expects
454 SELECT p1.oid, p1.relname
456 WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2
457 WHERE p2.attrelid = p1.oid AND p2.attnum > 0);
462 -- Cross-check against pg_type entry
463 -- NOTE: we allow attstorage to be 'plain' even when typstorage is not;
464 -- this is mainly for toast tables.
465 SELECT p1.attrelid, p1.attname, p2.oid, p2.typname
466 FROM pg_attribute AS p1, pg_type AS p2
467 WHERE p1.atttypid = p2.oid AND
468 (p1.attlen != p2.typlen OR
469 p1.attalign != p2.typalign OR
470 p1.attbyval != p2.typbyval OR
471 (p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));
472 attrelid | attname | oid | typname
473 ----------+---------+-----+---------
476 -- **************** pg_range ****************
477 -- Look for illegal values in pg_range fields.
478 SELECT p1.rngtypid, p1.rngsubtype
480 WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0;
481 rngtypid | rngsubtype
482 ----------+------------
485 -- rngcollation should be specified iff subtype is collatable
486 SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation
487 FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype
488 WHERE (rngcollation = 0) != (typcollation = 0);
489 rngtypid | rngsubtype | rngcollation | typcollation
490 ----------+------------+--------------+--------------
493 -- opclass had better be a btree opclass accepting the subtype.
494 -- We must allow anyarray matches, cf opr_sanity's binary_coercible()
495 SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname
496 FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc
497 WHERE o.opcmethod != 403 OR
498 ((o.opcintype != p1.rngsubtype) AND NOT
499 (o.opcintype = 'pg_catalog.anyarray'::regtype AND
500 EXISTS(select 1 from pg_catalog.pg_type where
501 oid = p1.rngsubtype and typelem != 0 and typlen = -1)));
502 rngtypid | rngsubtype | opcmethod | opcname
503 ----------+------------+-----------+---------
506 -- canonical function, if any, had better match the range type
507 SELECT p1.rngtypid, p1.rngsubtype, p.proname
508 FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical
509 WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid;
510 rngtypid | rngsubtype | proname
511 ----------+------------+---------
514 -- subdiff function, if any, had better match the subtype
515 SELECT p1.rngtypid, p1.rngsubtype, p.proname
516 FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff
518 OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype
519 OR prorettype != 'pg_catalog.float8'::regtype;
520 rngtypid | rngsubtype | proname
521 ----------+------------+---------