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.
15 -- **************** pg_type ****************
17 -- Look for illegal values in pg_type fields.
19 SELECT p1.oid, p1.typname
21 WHERE p1.typnamespace = 0 OR
22 (p1.typlen <= 0 AND p1.typlen != -1 AND p1.typlen != -2) OR
23 (p1.typtype not in ('b', 'c', 'd', 'e', 'p', 'r')) OR
24 NOT p1.typisdefined OR
25 (p1.typalign not in ('c', 's', 'i', 'd')) OR
26 (p1.typstorage not in ('p', 'x', 'e', 'm'));
28 -- Look for "pass by value" types that can't be passed by value.
30 SELECT p1.oid, p1.typname
33 (p1.typlen != 1 OR p1.typalign != 'c') AND
34 (p1.typlen != 2 OR p1.typalign != 's') AND
35 (p1.typlen != 4 OR p1.typalign != 'i') AND
36 (p1.typlen != 8 OR p1.typalign != 'd');
38 -- Look for "toastable" types that aren't varlena.
40 SELECT p1.oid, p1.typname
42 WHERE p1.typstorage != 'p' AND
43 (p1.typbyval OR p1.typlen != -1);
45 -- Look for complex types that do not have a typrelid entry,
46 -- or basic types that do.
48 SELECT p1.oid, p1.typname
50 WHERE (p1.typtype = 'c' AND p1.typrelid = 0) OR
51 (p1.typtype != 'c' AND p1.typrelid != 0);
53 -- Look for types that should have an array type according to their typtype,
54 -- but don't. We exclude composites here because we have not bothered to
55 -- make array types corresponding to the system catalogs' rowtypes.
56 -- NOTE: as of 9.1, this check finds pg_node_tree, smgr, and unknown.
58 SELECT p1.oid, p1.typname
60 WHERE p1.typtype not in ('c','d','p') AND p1.typname NOT LIKE E'\\_%'
62 (SELECT 1 FROM pg_type as p2
63 WHERE p2.typname = ('_' || p1.typname)::name AND
64 p2.typelem = p1.oid and p1.typarray = p2.oid);
66 -- Make sure typarray points to a varlena array type of our own base
67 SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype,
69 FROM pg_type p1 LEFT JOIN pg_type p2 ON (p1.typarray = p2.oid)
70 WHERE p1.typarray <> 0 AND
71 (p2.oid IS NULL OR p2.typelem <> p1.oid OR p2.typlen <> -1);
73 -- Look for range types that do not have a pg_range entry
74 SELECT p1.oid, p1.typname
76 WHERE p1.typtype = 'r' AND
77 NOT EXISTS(SELECT 1 FROM pg_range r WHERE rngtypid = p1.oid);
79 -- Look for range types whose typalign isn't sufficient
80 SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign
82 LEFT JOIN pg_range as r ON rngtypid = p1.oid
83 LEFT JOIN pg_type as p2 ON rngsubtype = p2.oid
84 WHERE p1.typtype = 'r' AND
85 (p1.typalign != (CASE WHEN p2.typalign = 'd' THEN 'd'::"char"
89 -- Text conversion routines must be provided.
91 SELECT p1.oid, p1.typname
93 WHERE (p1.typinput = 0 OR p1.typoutput = 0);
95 -- Check for bogus typinput routines
97 SELECT p1.oid, p1.typname, p2.oid, p2.proname
98 FROM pg_type AS p1, pg_proc AS p2
99 WHERE p1.typinput = p2.oid AND NOT
100 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'cstring'::regtype) OR
101 (p2.pronargs = 2 AND p2.proargtypes[0] = 'cstring'::regtype AND
102 p2.proargtypes[1] = 'oid'::regtype) OR
103 (p2.pronargs = 3 AND p2.proargtypes[0] = 'cstring'::regtype AND
104 p2.proargtypes[1] = 'oid'::regtype AND
105 p2.proargtypes[2] = 'int4'::regtype));
107 -- As of 8.0, this check finds refcursor, which is borrowing
108 -- other types' I/O routines
109 SELECT p1.oid, p1.typname, p2.oid, p2.proname
110 FROM pg_type AS p1, pg_proc AS p2
111 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
112 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
113 (p2.prorettype = p1.oid AND NOT p2.proretset)
116 -- Varlena array types will point to array_in
117 -- Exception as of 8.1: int2vector and oidvector have their own I/O 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
121 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
122 (p2.oid = 'array_in'::regproc)
125 -- Composites, domains, enums, ranges should all use the same input routines
126 SELECT DISTINCT typtype, typinput
128 WHERE p1.typtype not in ('b', 'p')
131 -- Check for bogus typoutput routines
133 -- As of 8.0, this check finds refcursor, which is borrowing
134 -- other types' I/O routines
135 SELECT p1.oid, p1.typname, p2.oid, p2.proname
136 FROM pg_type AS p1, pg_proc AS p2
137 WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
139 (p2.proargtypes[0] = p1.oid OR
140 (p2.oid = 'array_out'::regproc AND
141 p1.typelem != 0 AND p1.typlen = -1)))
144 SELECT p1.oid, p1.typname, p2.oid, p2.proname
145 FROM pg_type AS p1, pg_proc AS p2
146 WHERE p1.typoutput = p2.oid AND NOT
147 (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
149 -- Composites, enums, ranges should all use the same output routines
150 SELECT DISTINCT typtype, typoutput
152 WHERE p1.typtype not in ('b', 'd', 'p')
155 -- Domains should have same typoutput as their base types
156 SELECT p1.oid, p1.typname, p2.oid, p2.typname
157 FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid
158 WHERE p1.typtype = 'd' AND p1.typoutput IS DISTINCT FROM p2.typoutput;
160 -- Check for bogus typreceive routines
162 SELECT p1.oid, p1.typname, p2.oid, p2.proname
163 FROM pg_type AS p1, pg_proc AS p2
164 WHERE p1.typreceive = p2.oid AND NOT
165 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR
166 (p2.pronargs = 2 AND p2.proargtypes[0] = 'internal'::regtype AND
167 p2.proargtypes[1] = 'oid'::regtype) OR
168 (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND
169 p2.proargtypes[1] = 'oid'::regtype AND
170 p2.proargtypes[2] = 'int4'::regtype));
172 -- As of 7.4, 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.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
177 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
178 (p2.prorettype = p1.oid AND NOT p2.proretset)
181 -- Varlena array types will point to array_recv
182 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
183 SELECT p1.oid, p1.typname, p2.oid, p2.proname
184 FROM pg_type AS p1, pg_proc AS p2
185 WHERE p1.typreceive = p2.oid AND
186 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
187 (p2.oid = 'array_recv'::regproc)
190 -- Suspicious if typreceive doesn't take same number of args as typinput
191 SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname
192 FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3
193 WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND
194 p2.pronargs != p3.pronargs;
196 -- Composites, domains, enums, ranges should all use the same receive routines
197 SELECT DISTINCT typtype, typreceive
199 WHERE p1.typtype not in ('b', 'p')
202 -- 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)))
215 SELECT p1.oid, p1.typname, p2.oid, p2.proname
216 FROM pg_type AS p1, pg_proc AS p2
217 WHERE p1.typsend = p2.oid AND NOT
218 (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset);
220 -- Composites, enums, ranges should all use the same send routines
221 SELECT DISTINCT typtype, typsend
223 WHERE p1.typtype not in ('b', 'd', 'p')
226 -- Domains should have same typsend as their base types
227 SELECT p1.oid, p1.typname, p2.oid, p2.typname
228 FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid
229 WHERE p1.typtype = 'd' AND p1.typsend IS DISTINCT FROM p2.typsend;
231 -- Check for bogus typmodin routines
233 SELECT p1.oid, p1.typname, p2.oid, p2.proname
234 FROM pg_type AS p1, pg_proc AS p2
235 WHERE p1.typmodin = p2.oid AND NOT
237 p2.proargtypes[0] = 'cstring[]'::regtype AND
238 p2.prorettype = 'int4'::regtype AND NOT p2.proretset);
240 -- Check for bogus typmodout routines
242 SELECT p1.oid, p1.typname, p2.oid, p2.proname
243 FROM pg_type AS p1, pg_proc AS p2
244 WHERE p1.typmodout = p2.oid AND NOT
246 p2.proargtypes[0] = 'int4'::regtype AND
247 p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
249 -- Array types should have same typmodin/out as their element types
251 SELECT p1.oid, p1.typname, p2.oid, p2.typname
252 FROM pg_type AS p1, pg_type AS p2
253 WHERE p1.typelem = p2.oid AND NOT
254 (p1.typmodin = p2.typmodin AND p1.typmodout = p2.typmodout);
256 -- Array types should have same typdelim as their element types
258 SELECT p1.oid, p1.typname, p2.oid, p2.typname
259 FROM pg_type AS p1, pg_type AS p2
260 WHERE p1.typarray = p2.oid AND NOT (p1.typdelim = p2.typdelim);
262 -- Look for array types whose typalign isn't sufficient
264 SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign
265 FROM pg_type AS p1, pg_type AS p2
266 WHERE p1.typarray = p2.oid AND
267 p2.typalign != (CASE WHEN p1.typalign = 'd' THEN 'd'::"char"
268 ELSE 'i'::"char" END);
270 -- Check for bogus typanalyze routines
272 SELECT p1.oid, p1.typname, p2.oid, p2.proname
273 FROM pg_type AS p1, pg_proc AS p2
274 WHERE p1.typanalyze = p2.oid AND NOT
276 p2.proargtypes[0] = 'internal'::regtype AND
277 p2.prorettype = 'bool'::regtype AND NOT p2.proretset);
279 -- domains inherit their base type's typanalyze
281 SELECT d.oid, d.typname, d.typanalyze, t.oid, t.typname, t.typanalyze
282 FROM pg_type d JOIN pg_type t ON d.typbasetype = t.oid
283 WHERE d.typanalyze != t.typanalyze;
285 -- range_typanalyze should be used for all and only range types
286 -- (but exclude domains, which we checked above)
288 SELECT t.oid, t.typname, t.typanalyze
289 FROM pg_type t LEFT JOIN pg_range r on t.oid = r.rngtypid
290 WHERE t.typbasetype = 0 AND
291 (t.typanalyze = 'range_typanalyze'::regproc) != (r.rngtypid IS NOT NULL);
293 -- array_typanalyze should be used for all and only array types
294 -- (but exclude domains, which we checked above)
295 -- As of 9.2 this finds int2vector and oidvector, which are weird anyway
297 SELECT t.oid, t.typname, t.typanalyze
299 WHERE t.typbasetype = 0 AND
300 (t.typanalyze = 'array_typanalyze'::regproc) !=
301 (typelem != 0 AND typlen < 0)
304 -- **************** pg_class ****************
306 -- Look for illegal values in pg_class fields
308 SELECT p1.oid, p1.relname
310 WHERE p1.relkind NOT IN ('r', 'i', 's', 'S', 'c', 't', 'v', 'f');
312 -- Indexes should have an access method, others not.
314 SELECT p1.oid, p1.relname
316 WHERE (p1.relkind = 'i' AND p1.relam = 0) OR
317 (p1.relkind != 'i' AND p1.relam != 0);
319 -- **************** pg_attribute ****************
321 -- Look for illegal values in pg_attribute fields
323 SELECT p1.attrelid, p1.attname
324 FROM pg_attribute as p1
325 WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR
326 p1.attcacheoff != -1 OR p1.attinhcount < 0 OR
327 (p1.attinhcount = 0 AND NOT p1.attislocal);
329 -- Cross-check attnum against parent relation
331 SELECT p1.attrelid, p1.attname, p2.oid, p2.relname
332 FROM pg_attribute AS p1, pg_class AS p2
333 WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts;
335 -- Detect missing pg_attribute entries: should have as many non-system
336 -- attributes as parent relation expects
338 SELECT p1.oid, p1.relname
340 WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2
341 WHERE p2.attrelid = p1.oid AND p2.attnum > 0);
343 -- Cross-check against pg_type entry
344 -- NOTE: we allow attstorage to be 'plain' even when typstorage is not;
345 -- this is mainly for toast tables.
347 SELECT p1.attrelid, p1.attname, p2.oid, p2.typname
348 FROM pg_attribute AS p1, pg_type AS p2
349 WHERE p1.atttypid = p2.oid AND
350 (p1.attlen != p2.typlen OR
351 p1.attalign != p2.typalign OR
352 p1.attbyval != p2.typbyval OR
353 (p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));
355 -- **************** pg_range ****************
357 -- Look for illegal values in pg_range fields.
359 SELECT p1.rngtypid, p1.rngsubtype
361 WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0;
363 -- rngcollation should be specified iff subtype is collatable
365 SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation
366 FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype
367 WHERE (rngcollation = 0) != (typcollation = 0);
369 -- opclass had better be a btree opclass accepting the subtype.
370 -- We must allow anyarray matches, cf opr_sanity's binary_coercible()
372 SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname
373 FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc
374 WHERE o.opcmethod != 403 OR
375 ((o.opcintype != p1.rngsubtype) AND NOT
376 (o.opcintype = 'pg_catalog.anyarray'::regtype AND
377 EXISTS(select 1 from pg_catalog.pg_type where
378 oid = p1.rngsubtype and typelem != 0 and typlen = -1)));
380 -- canonical function, if any, had better match the range type
382 SELECT p1.rngtypid, p1.rngsubtype, p.proname
383 FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical
384 WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid;
386 -- subdiff function, if any, had better match the subtype
388 SELECT p1.rngtypid, p1.rngsubtype, p.proname
389 FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff
391 OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype
392 OR prorettype != 'pg_catalog.float8'::regtype;