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 basic or enum types that don't have an array type.
54 -- NOTE: as of 9.1, this check finds pg_node_tree, smgr, and unknown.
56 SELECT p1.oid, p1.typname
58 WHERE p1.typtype in ('b','e') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
59 (SELECT 1 FROM pg_type as p2
60 WHERE p2.typname = ('_' || p1.typname)::name AND
61 p2.typelem = p1.oid and p1.typarray = p2.oid);
63 -- Make sure typarray points to a varlena array type of our own base
64 SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype,
66 FROM pg_type p1 LEFT JOIN pg_type p2 ON (p1.typarray = p2.oid)
67 WHERE p1.typarray <> 0 AND
68 (p2.oid IS NULL OR p2.typelem <> p1.oid OR p2.typlen <> -1);
70 -- Look for range types that do not have a pg_range entry
71 SELECT p1.oid, p1.typname
73 WHERE p1.typtype = 'r' AND
74 NOT EXISTS(SELECT 1 FROM pg_range r WHERE rngtypid = p1.oid);
76 -- Look for range types whose typalign isn't sufficient
77 SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign
79 LEFT JOIN pg_range as r ON rngtypid = p1.oid
80 LEFT JOIN pg_type as p2 ON rngsubtype = p2.oid
81 WHERE p1.typtype = 'r' AND
82 (p1.typalign != (CASE WHEN p2.typalign = 'd' THEN 'd'::"char"
86 -- Text conversion routines must be provided.
88 SELECT p1.oid, p1.typname
90 WHERE (p1.typinput = 0 OR p1.typoutput = 0);
92 -- Check for bogus typinput routines
94 SELECT p1.oid, p1.typname, p2.oid, p2.proname
95 FROM pg_type AS p1, pg_proc AS p2
96 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
97 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'cstring'::regtype) OR
98 (p2.pronargs = 3 AND p2.proargtypes[0] = 'cstring'::regtype AND
99 p2.proargtypes[1] = 'oid'::regtype AND
100 p2.proargtypes[2] = 'int4'::regtype));
102 -- As of 8.0, this check finds refcursor, which is borrowing
103 -- other types' I/O routines
104 SELECT p1.oid, p1.typname, p2.oid, p2.proname
105 FROM pg_type AS p1, pg_proc AS p2
106 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
107 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
108 (p2.prorettype = p1.oid AND NOT p2.proretset)
111 -- Varlena array types will point to array_in
112 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
113 SELECT p1.oid, p1.typname, p2.oid, p2.proname
114 FROM pg_type AS p1, pg_proc AS p2
115 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND
116 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
117 (p2.oid = 'array_in'::regproc)
120 -- Check for bogus typoutput routines
122 -- As of 8.0, this check finds refcursor, which is borrowing
123 -- other types' I/O routines
124 SELECT p1.oid, p1.typname, p2.oid, p2.proname
125 FROM pg_type AS p1, pg_proc AS p2
126 WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
128 (p2.proargtypes[0] = p1.oid OR
129 (p2.oid = 'array_out'::regproc AND
130 p1.typelem != 0 AND p1.typlen = -1)))
133 SELECT p1.oid, p1.typname, p2.oid, p2.proname
134 FROM pg_type AS p1, pg_proc AS p2
135 WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
136 (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
138 -- Check for bogus typreceive routines
140 SELECT p1.oid, p1.typname, p2.oid, p2.proname
141 FROM pg_type AS p1, pg_proc AS p2
142 WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
143 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR
144 (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND
145 p2.proargtypes[1] = 'oid'::regtype AND
146 p2.proargtypes[2] = 'int4'::regtype));
148 -- As of 7.4, this check finds refcursor, which is borrowing
149 -- other types' I/O routines
150 SELECT p1.oid, p1.typname, p2.oid, p2.proname
151 FROM pg_type AS p1, pg_proc AS p2
152 WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
153 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
154 (p2.prorettype = p1.oid AND NOT p2.proretset)
157 -- Varlena array types will point to array_recv
158 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
159 SELECT p1.oid, p1.typname, p2.oid, p2.proname
160 FROM pg_type AS p1, pg_proc AS p2
161 WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND
162 (p1.typelem != 0 AND p1.typlen < 0) AND NOT
163 (p2.oid = 'array_recv'::regproc)
166 -- Suspicious if typreceive doesn't take same number of args as typinput
167 SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname
168 FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3
169 WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND
170 p2.pronargs != p3.pronargs;
172 -- Check for bogus typsend routines
174 -- As of 7.4, this check finds refcursor, which is borrowing
175 -- other types' I/O routines
176 SELECT p1.oid, p1.typname, p2.oid, p2.proname
177 FROM pg_type AS p1, pg_proc AS p2
178 WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
180 (p2.proargtypes[0] = p1.oid OR
181 (p2.oid = 'array_send'::regproc AND
182 p1.typelem != 0 AND p1.typlen = -1)))
185 SELECT p1.oid, p1.typname, p2.oid, p2.proname
186 FROM pg_type AS p1, pg_proc AS p2
187 WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
188 (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset);
190 -- Check for bogus typmodin routines
192 SELECT p1.oid, p1.typname, p2.oid, p2.proname
193 FROM pg_type AS p1, pg_proc AS p2
194 WHERE p1.typmodin = p2.oid AND p1.typtype in ('b', 'p') AND NOT
196 p2.proargtypes[0] = 'cstring[]'::regtype AND
197 p2.prorettype = 'int4'::regtype AND NOT p2.proretset);
199 -- Check for bogus typmodout routines
201 SELECT p1.oid, p1.typname, p2.oid, p2.proname
202 FROM pg_type AS p1, pg_proc AS p2
203 WHERE p1.typmodout = p2.oid AND p1.typtype in ('b', 'p') AND NOT
205 p2.proargtypes[0] = 'int4'::regtype AND
206 p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
208 -- Array types should have same typmodin/out as their element types
210 SELECT p1.oid, p1.typname, p2.oid, p2.typname
211 FROM pg_type AS p1, pg_type AS p2
212 WHERE p1.typelem = p2.oid AND NOT
213 (p1.typmodin = p2.typmodin AND p1.typmodout = p2.typmodout);
215 -- Array types should have same typdelim as their element types
217 SELECT p1.oid, p1.typname, p2.oid, p2.typname
218 FROM pg_type AS p1, pg_type AS p2
219 WHERE p1.typarray = p2.oid AND NOT (p1.typdelim = p2.typdelim);
221 -- Look for array types whose typalign isn't sufficient
223 SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign
224 FROM pg_type AS p1, pg_type AS p2
225 WHERE p1.typarray = p2.oid AND
226 p2.typalign != (CASE WHEN p1.typalign = 'd' THEN 'd'::"char"
227 ELSE 'i'::"char" END);
229 -- Check for bogus typanalyze routines
231 SELECT p1.oid, p1.typname, p2.oid, p2.proname
232 FROM pg_type AS p1, pg_proc AS p2
233 WHERE p1.typanalyze = p2.oid AND p1.typtype in ('b', 'p') AND NOT
235 p2.proargtypes[0] = 'internal'::regtype AND
236 p2.prorettype = 'bool'::regtype AND NOT p2.proretset);
238 -- **************** pg_class ****************
240 -- Look for illegal values in pg_class fields
242 SELECT p1.oid, p1.relname
244 WHERE p1.relkind NOT IN ('r', 'i', 's', 'S', 'c', 't', 'v', 'f');
246 -- Indexes should have an access method, others not.
248 SELECT p1.oid, p1.relname
250 WHERE (p1.relkind = 'i' AND p1.relam = 0) OR
251 (p1.relkind != 'i' AND p1.relam != 0);
253 -- **************** pg_attribute ****************
255 -- Look for illegal values in pg_attribute fields
257 SELECT p1.attrelid, p1.attname
258 FROM pg_attribute as p1
259 WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR
260 p1.attcacheoff != -1 OR p1.attinhcount < 0 OR
261 (p1.attinhcount = 0 AND NOT p1.attislocal);
263 -- Cross-check attnum against parent relation
265 SELECT p1.attrelid, p1.attname, p2.oid, p2.relname
266 FROM pg_attribute AS p1, pg_class AS p2
267 WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts;
269 -- Detect missing pg_attribute entries: should have as many non-system
270 -- attributes as parent relation expects
272 SELECT p1.oid, p1.relname
274 WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2
275 WHERE p2.attrelid = p1.oid AND p2.attnum > 0);
277 -- Cross-check against pg_type entry
278 -- NOTE: we allow attstorage to be 'plain' even when typstorage is not;
279 -- this is mainly for toast tables.
281 SELECT p1.attrelid, p1.attname, p2.oid, p2.typname
282 FROM pg_attribute AS p1, pg_type AS p2
283 WHERE p1.atttypid = p2.oid AND
284 (p1.attlen != p2.typlen OR
285 p1.attalign != p2.typalign OR
286 p1.attbyval != p2.typbyval OR
287 (p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));
289 -- **************** pg_range ****************
291 -- Look for illegal values in pg_range fields.
293 SELECT p1.rngtypid, p1.rngsubtype
295 WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0;
297 -- rngcollation should be specified iff subtype is collatable
299 SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation
300 FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype
301 WHERE (rngcollation = 0) != (typcollation = 0);
303 -- opclass had better be a btree opclass accepting the subtype.
304 -- We must allow anyarray matches, cf opr_sanity's binary_coercible()
306 SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname
307 FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc
308 WHERE o.opcmethod != 403 OR
309 ((o.opcintype != p1.rngsubtype) AND NOT
310 (o.opcintype = 'pg_catalog.anyarray'::regtype AND
311 EXISTS(select 1 from pg_catalog.pg_type where
312 oid = p1.rngsubtype and typelem != 0 and typlen = -1)));
314 -- canonical function, if any, had better match the range type
316 SELECT p1.rngtypid, p1.rngsubtype, p.proname
317 FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical
318 WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid;
320 -- subdiff function, if any, had better match the subtype
322 SELECT p1.rngtypid, p1.rngsubtype, p.proname
323 FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff
325 OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype
326 OR prorettype != 'pg_catalog.float8'::regtype;