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 9.1, this check finds pg_node_tree, smgr, and unknown. 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-- As of 8.0, this check finds refcursor, which is borrowing 108-- other types' I/O routines 109SELECT p1.oid, p1.typname, p2.oid, p2.proname 110FROM pg_type AS p1, pg_proc AS p2 111WHERE 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) 114ORDER BY 1; 115 116-- Varlena array types will point to array_in 117-- Exception as of 8.1: int2vector and oidvector have their own I/O routines 118SELECT p1.oid, p1.typname, p2.oid, p2.proname 119FROM pg_type AS p1, pg_proc AS p2 120WHERE p1.typinput = p2.oid AND 121 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 122 (p2.oid = 'array_in'::regproc) 123ORDER BY 1; 124 125-- typinput routines should not be volatile 126SELECT p1.oid, p1.typname, p2.oid, p2.proname 127FROM pg_type AS p1, pg_proc AS p2 128WHERE p1.typinput = p2.oid AND p2.provolatile NOT IN ('i', 's'); 129 130-- Composites, domains, enums, ranges should all use the same input routines 131SELECT DISTINCT typtype, typinput 132FROM pg_type AS p1 133WHERE p1.typtype not in ('b', 'p') 134ORDER BY 1; 135 136-- Check for bogus typoutput routines 137 138-- As of 8.0, this check finds refcursor, which is borrowing 139-- other types' I/O routines 140SELECT p1.oid, p1.typname, p2.oid, p2.proname 141FROM pg_type AS p1, pg_proc AS p2 142WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT 143 (p2.pronargs = 1 AND 144 (p2.proargtypes[0] = p1.oid OR 145 (p2.oid = 'array_out'::regproc AND 146 p1.typelem != 0 AND p1.typlen = -1))) 147ORDER BY 1; 148 149SELECT p1.oid, p1.typname, p2.oid, p2.proname 150FROM pg_type AS p1, pg_proc AS p2 151WHERE p1.typoutput = p2.oid AND NOT 152 (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset); 153 154-- typoutput routines should not be volatile 155SELECT p1.oid, p1.typname, p2.oid, p2.proname 156FROM pg_type AS p1, pg_proc AS p2 157WHERE p1.typoutput = p2.oid AND p2.provolatile NOT IN ('i', 's'); 158 159-- Composites, enums, ranges should all use the same output routines 160SELECT DISTINCT typtype, typoutput 161FROM pg_type AS p1 162WHERE p1.typtype not in ('b', 'd', 'p') 163ORDER BY 1; 164 165-- Domains should have same typoutput as their base types 166SELECT p1.oid, p1.typname, p2.oid, p2.typname 167FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid 168WHERE p1.typtype = 'd' AND p1.typoutput IS DISTINCT FROM p2.typoutput; 169 170-- Check for bogus typreceive routines 171 172SELECT p1.oid, p1.typname, p2.oid, p2.proname 173FROM pg_type AS p1, pg_proc AS p2 174WHERE p1.typreceive = p2.oid AND NOT 175 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR 176 (p2.pronargs = 2 AND p2.proargtypes[0] = 'internal'::regtype AND 177 p2.proargtypes[1] = 'oid'::regtype) OR 178 (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND 179 p2.proargtypes[1] = 'oid'::regtype AND 180 p2.proargtypes[2] = 'int4'::regtype)); 181 182-- As of 7.4, this check finds refcursor, which is borrowing 183-- other types' I/O routines 184SELECT p1.oid, p1.typname, p2.oid, p2.proname 185FROM pg_type AS p1, pg_proc AS p2 186WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT 187 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 188 (p2.prorettype = p1.oid AND NOT p2.proretset) 189ORDER BY 1; 190 191-- Varlena array types will point to array_recv 192-- Exception as of 8.1: int2vector and oidvector have their own I/O routines 193SELECT p1.oid, p1.typname, p2.oid, p2.proname 194FROM pg_type AS p1, pg_proc AS p2 195WHERE p1.typreceive = p2.oid AND 196 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 197 (p2.oid = 'array_recv'::regproc) 198ORDER BY 1; 199 200-- Suspicious if typreceive doesn't take same number of args as typinput 201SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname 202FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3 203WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND 204 p2.pronargs != p3.pronargs; 205 206-- typreceive routines should not be volatile 207SELECT p1.oid, p1.typname, p2.oid, p2.proname 208FROM pg_type AS p1, pg_proc AS p2 209WHERE p1.typreceive = p2.oid AND p2.provolatile NOT IN ('i', 's'); 210 211-- Composites, domains, enums, ranges should all use the same receive routines 212SELECT DISTINCT typtype, typreceive 213FROM pg_type AS p1 214WHERE p1.typtype not in ('b', 'p') 215ORDER BY 1; 216 217-- Check for bogus typsend routines 218 219-- As of 7.4, this check finds refcursor, which is borrowing 220-- other types' I/O routines 221SELECT p1.oid, p1.typname, p2.oid, p2.proname 222FROM pg_type AS p1, pg_proc AS p2 223WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT 224 (p2.pronargs = 1 AND 225 (p2.proargtypes[0] = p1.oid OR 226 (p2.oid = 'array_send'::regproc AND 227 p1.typelem != 0 AND p1.typlen = -1))) 228ORDER BY 1; 229 230SELECT p1.oid, p1.typname, p2.oid, p2.proname 231FROM pg_type AS p1, pg_proc AS p2 232WHERE p1.typsend = p2.oid AND NOT 233 (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset); 234 235-- typsend routines should not be volatile 236SELECT p1.oid, p1.typname, p2.oid, p2.proname 237FROM pg_type AS p1, pg_proc AS p2 238WHERE p1.typsend = p2.oid AND p2.provolatile NOT IN ('i', 's'); 239 240-- Composites, enums, ranges should all use the same send routines 241SELECT DISTINCT typtype, typsend 242FROM pg_type AS p1 243WHERE p1.typtype not in ('b', 'd', 'p') 244ORDER BY 1; 245 246-- Domains should have same typsend as their base types 247SELECT p1.oid, p1.typname, p2.oid, p2.typname 248FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid 249WHERE p1.typtype = 'd' AND p1.typsend IS DISTINCT FROM p2.typsend; 250 251-- Check for bogus typmodin routines 252 253SELECT p1.oid, p1.typname, p2.oid, p2.proname 254FROM pg_type AS p1, pg_proc AS p2 255WHERE p1.typmodin = p2.oid AND NOT 256 (p2.pronargs = 1 AND 257 p2.proargtypes[0] = 'cstring[]'::regtype AND 258 p2.prorettype = 'int4'::regtype AND NOT p2.proretset); 259 260-- typmodin routines should not be volatile 261SELECT p1.oid, p1.typname, p2.oid, p2.proname 262FROM pg_type AS p1, pg_proc AS p2 263WHERE p1.typmodin = p2.oid AND p2.provolatile NOT IN ('i', 's'); 264 265-- Check for bogus typmodout routines 266 267SELECT p1.oid, p1.typname, p2.oid, p2.proname 268FROM pg_type AS p1, pg_proc AS p2 269WHERE p1.typmodout = p2.oid AND NOT 270 (p2.pronargs = 1 AND 271 p2.proargtypes[0] = 'int4'::regtype AND 272 p2.prorettype = 'cstring'::regtype AND NOT p2.proretset); 273 274-- typmodout routines should not be volatile 275SELECT p1.oid, p1.typname, p2.oid, p2.proname 276FROM pg_type AS p1, pg_proc AS p2 277WHERE p1.typmodout = p2.oid AND p2.provolatile NOT IN ('i', 's'); 278 279-- Array types should have same typmodin/out as their element types 280 281SELECT p1.oid, p1.typname, p2.oid, p2.typname 282FROM pg_type AS p1, pg_type AS p2 283WHERE p1.typelem = p2.oid AND NOT 284 (p1.typmodin = p2.typmodin AND p1.typmodout = p2.typmodout); 285 286-- Array types should have same typdelim as their element types 287 288SELECT p1.oid, p1.typname, p2.oid, p2.typname 289FROM pg_type AS p1, pg_type AS p2 290WHERE p1.typarray = p2.oid AND NOT (p1.typdelim = p2.typdelim); 291 292-- Look for array types whose typalign isn't sufficient 293 294SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign 295FROM pg_type AS p1, pg_type AS p2 296WHERE p1.typarray = p2.oid AND 297 p2.typalign != (CASE WHEN p1.typalign = 'd' THEN 'd'::"char" 298 ELSE 'i'::"char" END); 299 300-- Check for bogus typanalyze routines 301 302SELECT p1.oid, p1.typname, p2.oid, p2.proname 303FROM pg_type AS p1, pg_proc AS p2 304WHERE p1.typanalyze = p2.oid AND NOT 305 (p2.pronargs = 1 AND 306 p2.proargtypes[0] = 'internal'::regtype AND 307 p2.prorettype = 'bool'::regtype AND NOT p2.proretset); 308 309-- there does not seem to be a reason to care about volatility of typanalyze 310 311-- domains inherit their base type's typanalyze 312 313SELECT d.oid, d.typname, d.typanalyze, t.oid, t.typname, t.typanalyze 314FROM pg_type d JOIN pg_type t ON d.typbasetype = t.oid 315WHERE d.typanalyze != t.typanalyze; 316 317-- range_typanalyze should be used for all and only range types 318-- (but exclude domains, which we checked above) 319 320SELECT t.oid, t.typname, t.typanalyze 321FROM pg_type t LEFT JOIN pg_range r on t.oid = r.rngtypid 322WHERE t.typbasetype = 0 AND 323 (t.typanalyze = 'range_typanalyze'::regproc) != (r.rngtypid IS NOT NULL); 324 325-- array_typanalyze should be used for all and only array types 326-- (but exclude domains, which we checked above) 327-- As of 9.2 this finds int2vector and oidvector, which are weird anyway 328 329SELECT t.oid, t.typname, t.typanalyze 330FROM pg_type t 331WHERE t.typbasetype = 0 AND 332 (t.typanalyze = 'array_typanalyze'::regproc) != 333 (typelem != 0 AND typlen < 0) 334ORDER BY 1; 335 336-- **************** pg_class **************** 337 338-- Look for illegal values in pg_class fields 339 340SELECT p1.oid, p1.relname 341FROM pg_class as p1 342WHERE relkind NOT IN ('r', 'i', 's', 'S', 'c', 't', 'v', 'f') OR 343 relpersistence NOT IN ('p', 'u', 't') OR 344 relreplident NOT IN ('d', 'n', 'f', 'i'); 345 346-- Indexes should have an access method, others not. 347 348SELECT p1.oid, p1.relname 349FROM pg_class as p1 350WHERE (p1.relkind = 'i' AND p1.relam = 0) OR 351 (p1.relkind != 'i' AND p1.relam != 0); 352 353-- **************** pg_attribute **************** 354 355-- Look for illegal values in pg_attribute fields 356 357SELECT p1.attrelid, p1.attname 358FROM pg_attribute as p1 359WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR 360 p1.attcacheoff != -1 OR p1.attinhcount < 0 OR 361 (p1.attinhcount = 0 AND NOT p1.attislocal); 362 363-- Cross-check attnum against parent relation 364 365SELECT p1.attrelid, p1.attname, p2.oid, p2.relname 366FROM pg_attribute AS p1, pg_class AS p2 367WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts; 368 369-- Detect missing pg_attribute entries: should have as many non-system 370-- attributes as parent relation expects 371 372SELECT p1.oid, p1.relname 373FROM pg_class AS p1 374WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2 375 WHERE p2.attrelid = p1.oid AND p2.attnum > 0); 376 377-- Cross-check against pg_type entry 378-- NOTE: we allow attstorage to be 'plain' even when typstorage is not; 379-- this is mainly for toast tables. 380 381SELECT p1.attrelid, p1.attname, p2.oid, p2.typname 382FROM pg_attribute AS p1, pg_type AS p2 383WHERE p1.atttypid = p2.oid AND 384 (p1.attlen != p2.typlen OR 385 p1.attalign != p2.typalign OR 386 p1.attbyval != p2.typbyval OR 387 (p1.attstorage != p2.typstorage AND p1.attstorage != 'p')); 388 389-- **************** pg_range **************** 390 391-- Look for illegal values in pg_range fields. 392 393SELECT p1.rngtypid, p1.rngsubtype 394FROM pg_range as p1 395WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0; 396 397-- rngcollation should be specified iff subtype is collatable 398 399SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation 400FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype 401WHERE (rngcollation = 0) != (typcollation = 0); 402 403-- opclass had better be a btree opclass accepting the subtype. 404-- We must allow anyarray matches, cf opr_sanity's binary_coercible() 405 406SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname 407FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc 408WHERE o.opcmethod != 403 OR 409 ((o.opcintype != p1.rngsubtype) AND NOT 410 (o.opcintype = 'pg_catalog.anyarray'::regtype AND 411 EXISTS(select 1 from pg_catalog.pg_type where 412 oid = p1.rngsubtype and typelem != 0 and typlen = -1))); 413 414-- canonical function, if any, had better match the range type 415 416SELECT p1.rngtypid, p1.rngsubtype, p.proname 417FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical 418WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid; 419 420-- subdiff function, if any, had better match the subtype 421 422SELECT p1.rngtypid, p1.rngsubtype, p.proname 423FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff 424WHERE pronargs != 2 425 OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype 426 OR prorettype != 'pg_catalog.float8'::regtype; 427