1-- 2-- TYPE_SANITY 3-- Sanity checks for common errors in making type-related system tables: 4-- pg_type, pg_class, pg_attribute, pg_range. 5-- 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. 9-- 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 15-- **************** pg_type **************** 16 17-- Look for illegal values in pg_type fields. 18 19SELECT p1.oid, p1.typname 20FROM pg_type as p1 21WHERE 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')); 27 28-- Look for "pass by value" types that can't be passed by value. 29 30SELECT p1.oid, p1.typname 31FROM pg_type as p1 32WHERE p1.typbyval AND 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'); 37 38-- Look for "toastable" types that aren't varlena. 39 40SELECT p1.oid, p1.typname 41FROM pg_type as p1 42WHERE p1.typstorage != 'p' AND 43 (p1.typbyval OR p1.typlen != -1); 44 45-- Look for complex types that do not have a typrelid entry, 46-- or basic types that do. 47 48SELECT p1.oid, p1.typname 49FROM pg_type as p1 50WHERE (p1.typtype = 'c' AND p1.typrelid = 0) OR 51 (p1.typtype != 'c' AND p1.typrelid != 0); 52 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 v10, this check finds pg_node_tree, pg_ndistinct, smgr. 57 58SELECT p1.oid, p1.typname 59FROM pg_type as p1 60WHERE p1.typtype not in ('c','d','p') AND p1.typname NOT LIKE E'\\_%' 61 AND NOT EXISTS 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); 65 66-- Make sure typarray points to a varlena array type of our own base 67SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype, 68 p2.typelem, p2.typlen 69FROM pg_type p1 LEFT JOIN pg_type p2 ON (p1.typarray = p2.oid) 70WHERE p1.typarray <> 0 AND 71 (p2.oid IS NULL OR p2.typelem <> p1.oid OR p2.typlen <> -1); 72 73-- Look for range types that do not have a pg_range entry 74SELECT p1.oid, p1.typname 75FROM pg_type as p1 76WHERE p1.typtype = 'r' AND 77 NOT EXISTS(SELECT 1 FROM pg_range r WHERE rngtypid = p1.oid); 78 79-- Look for range types whose typalign isn't sufficient 80SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign 81FROM pg_type as p1 82 LEFT JOIN pg_range as r ON rngtypid = p1.oid 83 LEFT JOIN pg_type as p2 ON rngsubtype = p2.oid 84WHERE p1.typtype = 'r' AND 85 (p1.typalign != (CASE WHEN p2.typalign = 'd' THEN 'd'::"char" 86 ELSE 'i'::"char" END) 87 OR p2.oid IS NULL); 88 89-- Text conversion routines must be provided. 90 91SELECT p1.oid, p1.typname 92FROM pg_type as p1 93WHERE (p1.typinput = 0 OR p1.typoutput = 0); 94 95-- Check for bogus typinput routines 96 97SELECT p1.oid, p1.typname, p2.oid, p2.proname 98FROM pg_type AS p1, pg_proc AS p2 99WHERE 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)); 106 107-- Check for type of the variadic array parameter's elements. 108-- provariadic should be ANYOID if the type of the last element is ANYOID, 109-- ANYELEMENTOID if the type of the last element is ANYARRAYOID, and otherwise 110-- the element type corresponding to the array type. 111 112SELECT oid::regprocedure, provariadic::regtype, proargtypes::regtype[] 113FROM pg_proc 114WHERE provariadic != 0 115AND case proargtypes[array_length(proargtypes, 1)-1] 116 WHEN 2276 THEN 2276 -- any -> any 117 WHEN 2277 THEN 2283 -- anyarray -> anyelement 118 ELSE (SELECT t.oid 119 FROM pg_type t 120 WHERE t.typarray = proargtypes[array_length(proargtypes, 1)-1]) 121 END != provariadic; 122 123-- Check that all and only those functions with a variadic type have 124-- a variadic argument. 125SELECT oid::regprocedure, proargmodes, provariadic 126FROM pg_proc 127WHERE (proargmodes IS NOT NULL AND 'v' = any(proargmodes)) 128 IS DISTINCT FROM 129 (provariadic != 0); 130 131-- As of 8.0, this check finds refcursor, which is borrowing 132-- other types' I/O routines 133SELECT p1.oid, p1.typname, p2.oid, p2.proname 134FROM pg_type AS p1, pg_proc AS p2 135WHERE 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) 138ORDER BY 1; 139 140-- Varlena array types will point to array_in 141-- Exception as of 8.1: int2vector and oidvector have their own I/O routines 142SELECT p1.oid, p1.typname, p2.oid, p2.proname 143FROM pg_type AS p1, pg_proc AS p2 144WHERE p1.typinput = p2.oid AND 145 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 146 (p2.oid = 'array_in'::regproc) 147ORDER BY 1; 148 149-- typinput routines should not be volatile 150SELECT p1.oid, p1.typname, p2.oid, p2.proname 151FROM pg_type AS p1, pg_proc AS p2 152WHERE p1.typinput = p2.oid AND p2.provolatile NOT IN ('i', 's'); 153 154-- Composites, domains, enums, ranges should all use the same input routines 155SELECT DISTINCT typtype, typinput 156FROM pg_type AS p1 157WHERE p1.typtype not in ('b', 'p') 158ORDER BY 1; 159 160-- Check for bogus typoutput routines 161 162-- As of 8.0, this check finds refcursor, which is borrowing 163-- other types' I/O routines 164SELECT p1.oid, p1.typname, p2.oid, p2.proname 165FROM pg_type AS p1, pg_proc AS p2 166WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT 167 (p2.pronargs = 1 AND 168 (p2.proargtypes[0] = p1.oid OR 169 (p2.oid = 'array_out'::regproc AND 170 p1.typelem != 0 AND p1.typlen = -1))) 171ORDER BY 1; 172 173SELECT p1.oid, p1.typname, p2.oid, p2.proname 174FROM pg_type AS p1, pg_proc AS p2 175WHERE p1.typoutput = p2.oid AND NOT 176 (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset); 177 178-- typoutput routines should not be volatile 179SELECT p1.oid, p1.typname, p2.oid, p2.proname 180FROM pg_type AS p1, pg_proc AS p2 181WHERE p1.typoutput = p2.oid AND p2.provolatile NOT IN ('i', 's'); 182 183-- Composites, enums, ranges should all use the same output routines 184SELECT DISTINCT typtype, typoutput 185FROM pg_type AS p1 186WHERE p1.typtype not in ('b', 'd', 'p') 187ORDER BY 1; 188 189-- Domains should have same typoutput as their base types 190SELECT p1.oid, p1.typname, p2.oid, p2.typname 191FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid 192WHERE p1.typtype = 'd' AND p1.typoutput IS DISTINCT FROM p2.typoutput; 193 194-- Check for bogus typreceive routines 195 196SELECT p1.oid, p1.typname, p2.oid, p2.proname 197FROM pg_type AS p1, pg_proc AS p2 198WHERE p1.typreceive = p2.oid AND NOT 199 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR 200 (p2.pronargs = 2 AND p2.proargtypes[0] = 'internal'::regtype AND 201 p2.proargtypes[1] = 'oid'::regtype) OR 202 (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND 203 p2.proargtypes[1] = 'oid'::regtype AND 204 p2.proargtypes[2] = 'int4'::regtype)); 205 206-- As of 7.4, this check finds refcursor, which is borrowing 207-- other types' I/O routines 208SELECT p1.oid, p1.typname, p2.oid, p2.proname 209FROM pg_type AS p1, pg_proc AS p2 210WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT 211 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 212 (p2.prorettype = p1.oid AND NOT p2.proretset) 213ORDER BY 1; 214 215-- Varlena array types will point to array_recv 216-- Exception as of 8.1: int2vector and oidvector have their own I/O routines 217SELECT p1.oid, p1.typname, p2.oid, p2.proname 218FROM pg_type AS p1, pg_proc AS p2 219WHERE p1.typreceive = p2.oid AND 220 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 221 (p2.oid = 'array_recv'::regproc) 222ORDER BY 1; 223 224-- Suspicious if typreceive doesn't take same number of args as typinput 225SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname 226FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3 227WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND 228 p2.pronargs != p3.pronargs; 229 230-- typreceive routines should not be volatile 231SELECT p1.oid, p1.typname, p2.oid, p2.proname 232FROM pg_type AS p1, pg_proc AS p2 233WHERE p1.typreceive = p2.oid AND p2.provolatile NOT IN ('i', 's'); 234 235-- Composites, domains, enums, ranges should all use the same receive routines 236SELECT DISTINCT typtype, typreceive 237FROM pg_type AS p1 238WHERE p1.typtype not in ('b', 'p') 239ORDER BY 1; 240 241-- Check for bogus typsend routines 242 243-- As of 7.4, this check finds refcursor, which is borrowing 244-- other types' I/O routines 245SELECT p1.oid, p1.typname, p2.oid, p2.proname 246FROM pg_type AS p1, pg_proc AS p2 247WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT 248 (p2.pronargs = 1 AND 249 (p2.proargtypes[0] = p1.oid OR 250 (p2.oid = 'array_send'::regproc AND 251 p1.typelem != 0 AND p1.typlen = -1))) 252ORDER BY 1; 253 254SELECT p1.oid, p1.typname, p2.oid, p2.proname 255FROM pg_type AS p1, pg_proc AS p2 256WHERE p1.typsend = p2.oid AND NOT 257 (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset); 258 259-- typsend routines should not be volatile 260SELECT p1.oid, p1.typname, p2.oid, p2.proname 261FROM pg_type AS p1, pg_proc AS p2 262WHERE p1.typsend = p2.oid AND p2.provolatile NOT IN ('i', 's'); 263 264-- Composites, enums, ranges should all use the same send routines 265SELECT DISTINCT typtype, typsend 266FROM pg_type AS p1 267WHERE p1.typtype not in ('b', 'd', 'p') 268ORDER BY 1; 269 270-- Domains should have same typsend as their base types 271SELECT p1.oid, p1.typname, p2.oid, p2.typname 272FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid 273WHERE p1.typtype = 'd' AND p1.typsend IS DISTINCT FROM p2.typsend; 274 275-- Check for bogus typmodin routines 276 277SELECT p1.oid, p1.typname, p2.oid, p2.proname 278FROM pg_type AS p1, pg_proc AS p2 279WHERE p1.typmodin = p2.oid AND NOT 280 (p2.pronargs = 1 AND 281 p2.proargtypes[0] = 'cstring[]'::regtype AND 282 p2.prorettype = 'int4'::regtype AND NOT p2.proretset); 283 284-- typmodin routines should not be volatile 285SELECT p1.oid, p1.typname, p2.oid, p2.proname 286FROM pg_type AS p1, pg_proc AS p2 287WHERE p1.typmodin = p2.oid AND p2.provolatile NOT IN ('i', 's'); 288 289-- Check for bogus typmodout routines 290 291SELECT p1.oid, p1.typname, p2.oid, p2.proname 292FROM pg_type AS p1, pg_proc AS p2 293WHERE p1.typmodout = p2.oid AND NOT 294 (p2.pronargs = 1 AND 295 p2.proargtypes[0] = 'int4'::regtype AND 296 p2.prorettype = 'cstring'::regtype AND NOT p2.proretset); 297 298-- typmodout routines should not be volatile 299SELECT p1.oid, p1.typname, p2.oid, p2.proname 300FROM pg_type AS p1, pg_proc AS p2 301WHERE p1.typmodout = p2.oid AND p2.provolatile NOT IN ('i', 's'); 302 303-- Array types should have same typmodin/out as their element types 304 305SELECT p1.oid, p1.typname, p2.oid, p2.typname 306FROM pg_type AS p1, pg_type AS p2 307WHERE p1.typelem = p2.oid AND NOT 308 (p1.typmodin = p2.typmodin AND p1.typmodout = p2.typmodout); 309 310-- Array types should have same typdelim as their element types 311 312SELECT p1.oid, p1.typname, p2.oid, p2.typname 313FROM pg_type AS p1, pg_type AS p2 314WHERE p1.typarray = p2.oid AND NOT (p1.typdelim = p2.typdelim); 315 316-- Look for array types whose typalign isn't sufficient 317 318SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign 319FROM pg_type AS p1, pg_type AS p2 320WHERE p1.typarray = p2.oid AND 321 p2.typalign != (CASE WHEN p1.typalign = 'd' THEN 'd'::"char" 322 ELSE 'i'::"char" END); 323 324-- Check for bogus typanalyze routines 325 326SELECT p1.oid, p1.typname, p2.oid, p2.proname 327FROM pg_type AS p1, pg_proc AS p2 328WHERE p1.typanalyze = p2.oid AND NOT 329 (p2.pronargs = 1 AND 330 p2.proargtypes[0] = 'internal'::regtype AND 331 p2.prorettype = 'bool'::regtype AND NOT p2.proretset); 332 333-- there does not seem to be a reason to care about volatility of typanalyze 334 335-- domains inherit their base type's typanalyze 336 337SELECT d.oid, d.typname, d.typanalyze, t.oid, t.typname, t.typanalyze 338FROM pg_type d JOIN pg_type t ON d.typbasetype = t.oid 339WHERE d.typanalyze != t.typanalyze; 340 341-- range_typanalyze should be used for all and only range types 342-- (but exclude domains, which we checked above) 343 344SELECT t.oid, t.typname, t.typanalyze 345FROM pg_type t LEFT JOIN pg_range r on t.oid = r.rngtypid 346WHERE t.typbasetype = 0 AND 347 (t.typanalyze = 'range_typanalyze'::regproc) != (r.rngtypid IS NOT NULL); 348 349-- array_typanalyze should be used for all and only array types 350-- (but exclude domains, which we checked above) 351-- As of 9.2 this finds int2vector and oidvector, which are weird anyway 352 353SELECT t.oid, t.typname, t.typanalyze 354FROM pg_type t 355WHERE t.typbasetype = 0 AND 356 (t.typanalyze = 'array_typanalyze'::regproc) != 357 (typelem != 0 AND typlen < 0) 358ORDER BY 1; 359 360-- **************** pg_class **************** 361 362-- Look for illegal values in pg_class fields 363 364SELECT p1.oid, p1.relname 365FROM pg_class as p1 366WHERE relkind NOT IN ('r', 'i', 'S', 't', 'v', 'm', 'c', 'f', 'p') OR 367 relpersistence NOT IN ('p', 'u', 't') OR 368 relreplident NOT IN ('d', 'n', 'f', 'i'); 369 370-- Indexes should have an access method, others not. 371 372SELECT p1.oid, p1.relname 373FROM pg_class as p1 374WHERE (p1.relkind = 'i' AND p1.relam = 0) OR 375 (p1.relkind != 'i' AND p1.relam != 0); 376 377-- **************** pg_attribute **************** 378 379-- Look for illegal values in pg_attribute fields 380 381SELECT p1.attrelid, p1.attname 382FROM pg_attribute as p1 383WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR 384 p1.attcacheoff != -1 OR p1.attinhcount < 0 OR 385 (p1.attinhcount = 0 AND NOT p1.attislocal); 386 387-- Cross-check attnum against parent relation 388 389SELECT p1.attrelid, p1.attname, p2.oid, p2.relname 390FROM pg_attribute AS p1, pg_class AS p2 391WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts; 392 393-- Detect missing pg_attribute entries: should have as many non-system 394-- attributes as parent relation expects 395 396SELECT p1.oid, p1.relname 397FROM pg_class AS p1 398WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2 399 WHERE p2.attrelid = p1.oid AND p2.attnum > 0); 400 401-- Cross-check against pg_type entry 402-- NOTE: we allow attstorage to be 'plain' even when typstorage is not; 403-- this is mainly for toast tables. 404 405SELECT p1.attrelid, p1.attname, p2.oid, p2.typname 406FROM pg_attribute AS p1, pg_type AS p2 407WHERE p1.atttypid = p2.oid AND 408 (p1.attlen != p2.typlen OR 409 p1.attalign != p2.typalign OR 410 p1.attbyval != p2.typbyval OR 411 (p1.attstorage != p2.typstorage AND p1.attstorage != 'p')); 412 413-- **************** pg_range **************** 414 415-- Look for illegal values in pg_range fields. 416 417SELECT p1.rngtypid, p1.rngsubtype 418FROM pg_range as p1 419WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0; 420 421-- rngcollation should be specified iff subtype is collatable 422 423SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation 424FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype 425WHERE (rngcollation = 0) != (typcollation = 0); 426 427-- opclass had better be a btree opclass accepting the subtype. 428-- We must allow anyarray matches, cf opr_sanity's binary_coercible() 429 430SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname 431FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc 432WHERE o.opcmethod != 403 OR 433 ((o.opcintype != p1.rngsubtype) AND NOT 434 (o.opcintype = 'pg_catalog.anyarray'::regtype AND 435 EXISTS(select 1 from pg_catalog.pg_type where 436 oid = p1.rngsubtype and typelem != 0 and typlen = -1))); 437 438-- canonical function, if any, had better match the range type 439 440SELECT p1.rngtypid, p1.rngsubtype, p.proname 441FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical 442WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid; 443 444-- subdiff function, if any, had better match the subtype 445 446SELECT p1.rngtypid, p1.rngsubtype, p.proname 447FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff 448WHERE pronargs != 2 449 OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype 450 OR prorettype != 'pg_catalog.float8'::regtype; 451