1-- 2-- OPR_SANITY 3-- Sanity checks for common errors in making operator/procedure system tables: 4-- pg_operator, pg_proc, pg_cast, pg_conversion, pg_aggregate, pg_am, 5-- pg_amop, pg_amproc, pg_opclass, pg_opfamily, pg_index. 6-- 7-- Every test failure in this file should be closely inspected. 8-- The description of the failing test should be read carefully before 9-- adjusting the expected output. In most cases, the queries should 10-- not find *any* matching entries. 11-- 12-- NB: we assume the oidjoins test will have caught any dangling links, 13-- that is OID or REGPROC fields that are not zero and do not match some 14-- row in the linked-to table. However, if we want to enforce that a link 15-- field can't be 0, we have to check it here. 16-- 17-- NB: run this test earlier than the create_operator test, because 18-- that test creates some bogus operators... 19 20 21-- Helper functions to deal with cases where binary-coercible matches are 22-- allowed. 23 24-- This should match IsBinaryCoercible() in parse_coerce.c. 25create function binary_coercible(oid, oid) returns bool as $$ 26begin 27 if $1 = $2 then return true; end if; 28 if EXISTS(select 1 from pg_catalog.pg_cast where 29 castsource = $1 and casttarget = $2 and 30 castmethod = 'b' and castcontext = 'i') 31 then return true; end if; 32 if $2 = 'pg_catalog.any'::pg_catalog.regtype then return true; end if; 33 if $2 = 'pg_catalog.anyarray'::pg_catalog.regtype then 34 if EXISTS(select 1 from pg_catalog.pg_type where 35 oid = $1 and typelem != 0 and typlen = -1) 36 then return true; end if; 37 end if; 38 if $2 = 'pg_catalog.anyrange'::pg_catalog.regtype then 39 if (select typtype from pg_catalog.pg_type where oid = $1) = 'r' 40 then return true; end if; 41 end if; 42 return false; 43end 44$$ language plpgsql strict stable; 45 46-- This one ignores castcontext, so it considers only physical equivalence 47-- and not whether the coercion can be invoked implicitly. 48create function physically_coercible(oid, oid) returns bool as $$ 49begin 50 if $1 = $2 then return true; end if; 51 if EXISTS(select 1 from pg_catalog.pg_cast where 52 castsource = $1 and casttarget = $2 and 53 castmethod = 'b') 54 then return true; end if; 55 if $2 = 'pg_catalog.any'::pg_catalog.regtype then return true; end if; 56 if $2 = 'pg_catalog.anyarray'::pg_catalog.regtype then 57 if EXISTS(select 1 from pg_catalog.pg_type where 58 oid = $1 and typelem != 0 and typlen = -1) 59 then return true; end if; 60 end if; 61 if $2 = 'pg_catalog.anyrange'::pg_catalog.regtype then 62 if (select typtype from pg_catalog.pg_type where oid = $1) = 'r' 63 then return true; end if; 64 end if; 65 return false; 66end 67$$ language plpgsql strict stable; 68 69 70-- **************** pg_proc **************** 71 72-- Look for illegal values in pg_proc fields. 73 74SELECT p1.oid, p1.proname 75FROM pg_proc as p1 76WHERE p1.prolang = 0 OR p1.prorettype = 0 OR 77 p1.pronargs < 0 OR 78 p1.pronargdefaults < 0 OR 79 p1.pronargdefaults > p1.pronargs OR 80 array_lower(p1.proargtypes, 1) != 0 OR 81 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR 82 0::oid = ANY (p1.proargtypes) OR 83 procost <= 0 OR 84 CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END OR 85 prokind NOT IN ('f', 'a', 'w', 'p') OR 86 provolatile NOT IN ('i', 's', 'v') OR 87 proparallel NOT IN ('s', 'r', 'u'); 88 89-- prosrc should never be null or empty 90SELECT p1.oid, p1.proname 91FROM pg_proc as p1 92WHERE prosrc IS NULL OR prosrc = '' OR prosrc = '-'; 93 94-- proretset should only be set for normal functions 95SELECT p1.oid, p1.proname 96FROM pg_proc AS p1 97WHERE proretset AND prokind != 'f'; 98 99-- currently, no built-in functions should be SECURITY DEFINER; 100-- this might change in future, but there will probably never be many. 101SELECT p1.oid, p1.proname 102FROM pg_proc AS p1 103WHERE prosecdef 104ORDER BY 1; 105 106-- pronargdefaults should be 0 iff proargdefaults is null 107SELECT p1.oid, p1.proname 108FROM pg_proc AS p1 109WHERE (pronargdefaults <> 0) != (proargdefaults IS NOT NULL); 110 111-- probin should be non-empty for C functions, null everywhere else 112SELECT p1.oid, p1.proname 113FROM pg_proc as p1 114WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-'); 115 116SELECT p1.oid, p1.proname 117FROM pg_proc as p1 118WHERE prolang != 13 AND probin IS NOT NULL; 119 120-- Look for conflicting proc definitions (same names and input datatypes). 121-- (This test should be dead code now that we have the unique index 122-- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.) 123 124SELECT p1.oid, p1.proname, p2.oid, p2.proname 125FROM pg_proc AS p1, pg_proc AS p2 126WHERE p1.oid != p2.oid AND 127 p1.proname = p2.proname AND 128 p1.pronargs = p2.pronargs AND 129 p1.proargtypes = p2.proargtypes; 130 131-- Considering only built-in procs (prolang = 12), look for multiple uses 132-- of the same internal function (ie, matching prosrc fields). It's OK to 133-- have several entries with different pronames for the same internal function, 134-- but conflicts in the number of arguments and other critical items should 135-- be complained of. (We don't check data types here; see next query.) 136-- Note: ignore aggregate functions here, since they all point to the same 137-- dummy built-in function. 138 139SELECT p1.oid, p1.proname, p2.oid, p2.proname 140FROM pg_proc AS p1, pg_proc AS p2 141WHERE p1.oid < p2.oid AND 142 p1.prosrc = p2.prosrc AND 143 p1.prolang = 12 AND p2.prolang = 12 AND 144 (p1.prokind != 'a' OR p2.prokind != 'a') AND 145 (p1.prolang != p2.prolang OR 146 p1.prokind != p2.prokind OR 147 p1.prosecdef != p2.prosecdef OR 148 p1.proleakproof != p2.proleakproof OR 149 p1.proisstrict != p2.proisstrict OR 150 p1.proretset != p2.proretset OR 151 p1.provolatile != p2.provolatile OR 152 p1.pronargs != p2.pronargs); 153 154-- Look for uses of different type OIDs in the argument/result type fields 155-- for different aliases of the same built-in function. 156-- This indicates that the types are being presumed to be binary-equivalent, 157-- or that the built-in function is prepared to deal with different types. 158-- That's not wrong, necessarily, but we make lists of all the types being 159-- so treated. Note that the expected output of this part of the test will 160-- need to be modified whenever new pairs of types are made binary-equivalent, 161-- or when new polymorphic built-in functions are added! 162-- Note: ignore aggregate functions here, since they all point to the same 163-- dummy built-in function. Likewise, ignore range constructor functions. 164 165SELECT DISTINCT p1.prorettype, p2.prorettype 166FROM pg_proc AS p1, pg_proc AS p2 167WHERE p1.oid != p2.oid AND 168 p1.prosrc = p2.prosrc AND 169 p1.prolang = 12 AND p2.prolang = 12 AND 170 p1.prokind != 'a' AND p2.prokind != 'a' AND 171 p1.prosrc NOT LIKE E'range\\_constructor_' AND 172 p2.prosrc NOT LIKE E'range\\_constructor_' AND 173 (p1.prorettype < p2.prorettype) 174ORDER BY 1, 2; 175 176SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0] 177FROM pg_proc AS p1, pg_proc AS p2 178WHERE p1.oid != p2.oid AND 179 p1.prosrc = p2.prosrc AND 180 p1.prolang = 12 AND p2.prolang = 12 AND 181 p1.prokind != 'a' AND p2.prokind != 'a' AND 182 p1.prosrc NOT LIKE E'range\\_constructor_' AND 183 p2.prosrc NOT LIKE E'range\\_constructor_' AND 184 (p1.proargtypes[0] < p2.proargtypes[0]) 185ORDER BY 1, 2; 186 187SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1] 188FROM pg_proc AS p1, pg_proc AS p2 189WHERE p1.oid != p2.oid AND 190 p1.prosrc = p2.prosrc AND 191 p1.prolang = 12 AND p2.prolang = 12 AND 192 p1.prokind != 'a' AND p2.prokind != 'a' AND 193 p1.prosrc NOT LIKE E'range\\_constructor_' AND 194 p2.prosrc NOT LIKE E'range\\_constructor_' AND 195 (p1.proargtypes[1] < p2.proargtypes[1]) 196ORDER BY 1, 2; 197 198SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2] 199FROM pg_proc AS p1, pg_proc AS p2 200WHERE p1.oid != p2.oid AND 201 p1.prosrc = p2.prosrc AND 202 p1.prolang = 12 AND p2.prolang = 12 AND 203 p1.prokind != 'a' AND p2.prokind != 'a' AND 204 (p1.proargtypes[2] < p2.proargtypes[2]) 205ORDER BY 1, 2; 206 207SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3] 208FROM pg_proc AS p1, pg_proc AS p2 209WHERE p1.oid != p2.oid AND 210 p1.prosrc = p2.prosrc AND 211 p1.prolang = 12 AND p2.prolang = 12 AND 212 p1.prokind != 'a' AND p2.prokind != 'a' AND 213 (p1.proargtypes[3] < p2.proargtypes[3]) 214ORDER BY 1, 2; 215 216SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4] 217FROM pg_proc AS p1, pg_proc AS p2 218WHERE p1.oid != p2.oid AND 219 p1.prosrc = p2.prosrc AND 220 p1.prolang = 12 AND p2.prolang = 12 AND 221 p1.prokind != 'a' AND p2.prokind != 'a' AND 222 (p1.proargtypes[4] < p2.proargtypes[4]) 223ORDER BY 1, 2; 224 225SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5] 226FROM pg_proc AS p1, pg_proc AS p2 227WHERE p1.oid != p2.oid AND 228 p1.prosrc = p2.prosrc AND 229 p1.prolang = 12 AND p2.prolang = 12 AND 230 p1.prokind != 'a' AND p2.prokind != 'a' AND 231 (p1.proargtypes[5] < p2.proargtypes[5]) 232ORDER BY 1, 2; 233 234SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6] 235FROM pg_proc AS p1, pg_proc AS p2 236WHERE p1.oid != p2.oid AND 237 p1.prosrc = p2.prosrc AND 238 p1.prolang = 12 AND p2.prolang = 12 AND 239 p1.prokind != 'a' AND p2.prokind != 'a' AND 240 (p1.proargtypes[6] < p2.proargtypes[6]) 241ORDER BY 1, 2; 242 243SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7] 244FROM pg_proc AS p1, pg_proc AS p2 245WHERE p1.oid != p2.oid AND 246 p1.prosrc = p2.prosrc AND 247 p1.prolang = 12 AND p2.prolang = 12 AND 248 p1.prokind != 'a' AND p2.prokind != 'a' AND 249 (p1.proargtypes[7] < p2.proargtypes[7]) 250ORDER BY 1, 2; 251 252-- Look for functions that return type "internal" and do not have any 253-- "internal" argument. Such a function would be a security hole since 254-- it might be used to call an internal function from an SQL command. 255-- As of 7.3 this query should find only internal_in, which is safe because 256-- it always throws an error when called. 257 258SELECT p1.oid, p1.proname 259FROM pg_proc as p1 260WHERE p1.prorettype = 'internal'::regtype AND NOT 261 'internal'::regtype = ANY (p1.proargtypes); 262 263-- Look for functions that return a polymorphic type and do not have any 264-- polymorphic argument. Calls of such functions would be unresolvable 265-- at parse time. As of 9.6 this query should find only some input functions 266-- and GiST support functions associated with these pseudotypes. 267 268SELECT p1.oid, p1.proname 269FROM pg_proc as p1 270WHERE p1.prorettype IN 271 ('anyelement'::regtype, 'anyarray'::regtype, 'anynonarray'::regtype, 272 'anyenum'::regtype, 'anyrange'::regtype) 273 AND NOT 274 ('anyelement'::regtype = ANY (p1.proargtypes) OR 275 'anyarray'::regtype = ANY (p1.proargtypes) OR 276 'anynonarray'::regtype = ANY (p1.proargtypes) OR 277 'anyenum'::regtype = ANY (p1.proargtypes) OR 278 'anyrange'::regtype = ANY (p1.proargtypes)) 279ORDER BY 2; 280 281-- Look for functions that accept cstring and are neither datatype input 282-- functions nor encoding conversion functions. It's almost never a good 283-- idea to use cstring input for a function meant to be called from SQL; 284-- text should be used instead, because cstring lacks suitable casts. 285-- As of 9.6 this query should find only cstring_out and cstring_send. 286-- However, we must manually exclude shell_in, which might or might not be 287-- rejected by the EXISTS clause depending on whether there are currently 288-- any shell types. 289 290SELECT p1.oid, p1.proname 291FROM pg_proc as p1 292WHERE 'cstring'::regtype = ANY (p1.proargtypes) 293 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typinput = p1.oid) 294 AND NOT EXISTS(SELECT 1 FROM pg_conversion WHERE conproc = p1.oid) 295 AND p1.oid != 'shell_in(cstring)'::regprocedure 296ORDER BY 1; 297 298-- Likewise, look for functions that return cstring and aren't datatype output 299-- functions nor typmod output functions. 300-- As of 9.6 this query should find only cstring_in and cstring_recv. 301-- However, we must manually exclude shell_out. 302 303SELECT p1.oid, p1.proname 304FROM pg_proc as p1 305WHERE p1.prorettype = 'cstring'::regtype 306 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typoutput = p1.oid) 307 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typmodout = p1.oid) 308 AND p1.oid != 'shell_out(opaque)'::regprocedure 309ORDER BY 1; 310 311-- Check for length inconsistencies between the various argument-info arrays. 312 313SELECT p1.oid, p1.proname 314FROM pg_proc as p1 315WHERE proallargtypes IS NOT NULL AND 316 array_length(proallargtypes,1) < array_length(proargtypes,1); 317 318SELECT p1.oid, p1.proname 319FROM pg_proc as p1 320WHERE proargmodes IS NOT NULL AND 321 array_length(proargmodes,1) < array_length(proargtypes,1); 322 323SELECT p1.oid, p1.proname 324FROM pg_proc as p1 325WHERE proargnames IS NOT NULL AND 326 array_length(proargnames,1) < array_length(proargtypes,1); 327 328SELECT p1.oid, p1.proname 329FROM pg_proc as p1 330WHERE proallargtypes IS NOT NULL AND proargmodes IS NOT NULL AND 331 array_length(proallargtypes,1) <> array_length(proargmodes,1); 332 333SELECT p1.oid, p1.proname 334FROM pg_proc as p1 335WHERE proallargtypes IS NOT NULL AND proargnames IS NOT NULL AND 336 array_length(proallargtypes,1) <> array_length(proargnames,1); 337 338SELECT p1.oid, p1.proname 339FROM pg_proc as p1 340WHERE proargmodes IS NOT NULL AND proargnames IS NOT NULL AND 341 array_length(proargmodes,1) <> array_length(proargnames,1); 342 343-- Check that proallargtypes matches proargtypes 344SELECT p1.oid, p1.proname, p1.proargtypes, p1.proallargtypes, p1.proargmodes 345FROM pg_proc as p1 346WHERE proallargtypes IS NOT NULL AND 347 ARRAY(SELECT unnest(proargtypes)) <> 348 ARRAY(SELECT proallargtypes[i] 349 FROM generate_series(1, array_length(proallargtypes, 1)) g(i) 350 WHERE proargmodes IS NULL OR proargmodes[i] IN ('i', 'b', 'v')); 351 352-- Check for protransform functions with the wrong signature 353SELECT p1.oid, p1.proname, p2.oid, p2.proname 354FROM pg_proc AS p1, pg_proc AS p2 355WHERE p2.oid = p1.protransform AND 356 (p2.prorettype != 'internal'::regtype OR p2.proretset OR p2.pronargs != 1 357 OR p2.proargtypes[0] != 'internal'::regtype); 358 359-- Insist that all built-in pg_proc entries have descriptions 360SELECT p1.oid, p1.proname 361FROM pg_proc as p1 LEFT JOIN pg_description as d 362 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0 363WHERE d.classoid IS NULL AND p1.oid <= 9999; 364 365-- List of built-in leakproof functions 366-- 367-- Leakproof functions should only be added after carefully 368-- scrutinizing all possibly executed codepaths for possible 369-- information leaks. Don't add functions here unless you know what a 370-- leakproof function is. If unsure, don't mark it as such. 371 372-- temporarily disable fancy output, so catalog changes create less diff noise 373\a\t 374 375SELECT p1.oid::regprocedure 376FROM pg_proc p1 JOIN pg_namespace pn 377 ON pronamespace = pn.oid 378WHERE nspname = 'pg_catalog' AND proleakproof 379ORDER BY 1; 380 381-- restore normal output mode 382\a\t 383 384-- List of functions used by libpq's fe-lobj.c 385-- 386-- If the output of this query changes, you probably broke libpq. 387-- lo_initialize() assumes that there will be at most one match for 388-- each listed name. 389select proname, oid from pg_catalog.pg_proc 390where proname in ( 391 'lo_open', 392 'lo_close', 393 'lo_creat', 394 'lo_create', 395 'lo_unlink', 396 'lo_lseek', 397 'lo_lseek64', 398 'lo_tell', 399 'lo_tell64', 400 'lo_truncate', 401 'lo_truncate64', 402 'loread', 403 'lowrite') 404and pronamespace = (select oid from pg_catalog.pg_namespace 405 where nspname = 'pg_catalog') 406order by 1; 407 408-- Check that all immutable functions are marked parallel safe 409SELECT p1.oid, p1.proname 410FROM pg_proc AS p1 411WHERE provolatile = 'i' AND proparallel = 'u'; 412 413 414-- **************** pg_cast **************** 415 416-- Catch bogus values in pg_cast columns (other than cases detected by 417-- oidjoins test). 418 419SELECT * 420FROM pg_cast c 421WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i') 422 OR castmethod NOT IN ('f', 'b' ,'i'); 423 424-- Check that castfunc is nonzero only for cast methods that need a function, 425-- and zero otherwise 426 427SELECT * 428FROM pg_cast c 429WHERE (castmethod = 'f' AND castfunc = 0) 430 OR (castmethod IN ('b', 'i') AND castfunc <> 0); 431 432-- Look for casts to/from the same type that aren't length coercion functions. 433-- (We assume they are length coercions if they take multiple arguments.) 434-- Such entries are not necessarily harmful, but they are useless. 435 436SELECT * 437FROM pg_cast c 438WHERE castsource = casttarget AND castfunc = 0; 439 440SELECT c.* 441FROM pg_cast c, pg_proc p 442WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget; 443 444-- Look for cast functions that don't have the right signature. The 445-- argument and result types in pg_proc must be the same as, or binary 446-- compatible with, what it says in pg_cast. 447-- As a special case, we allow casts from CHAR(n) that use functions 448-- declared to take TEXT. This does not pass the binary-coercibility test 449-- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results 450-- are the same, so long as the function is one that ignores trailing blanks. 451 452SELECT c.* 453FROM pg_cast c, pg_proc p 454WHERE c.castfunc = p.oid AND 455 (p.pronargs < 1 OR p.pronargs > 3 456 OR NOT (binary_coercible(c.castsource, p.proargtypes[0]) 457 OR (c.castsource = 'character'::regtype AND 458 p.proargtypes[0] = 'text'::regtype)) 459 OR NOT binary_coercible(p.prorettype, c.casttarget)); 460 461SELECT c.* 462FROM pg_cast c, pg_proc p 463WHERE c.castfunc = p.oid AND 464 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR 465 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype)); 466 467-- Look for binary compatible casts that do not have the reverse 468-- direction registered as well, or where the reverse direction is not 469-- also binary compatible. This is legal, but usually not intended. 470 471-- As of 7.4, this finds the casts from text and varchar to bpchar, because 472-- those are binary-compatible while the reverse way goes through rtrim(). 473 474-- As of 8.2, this finds the cast from cidr to inet, because that is a 475-- trivial binary coercion while the other way goes through inet_to_cidr(). 476 477-- As of 8.3, this finds the casts from xml to text, varchar, and bpchar, 478-- because those are binary-compatible while the reverse goes through 479-- texttoxml(), which does an XML syntax check. 480 481-- As of 9.1, this finds the cast from pg_node_tree to text, which we 482-- intentionally do not provide a reverse pathway for. 483 484SELECT castsource::regtype, casttarget::regtype, castfunc, castcontext 485FROM pg_cast c 486WHERE c.castmethod = 'b' AND 487 NOT EXISTS (SELECT 1 FROM pg_cast k 488 WHERE k.castmethod = 'b' AND 489 k.castsource = c.casttarget AND 490 k.casttarget = c.castsource); 491 492 493-- **************** pg_conversion **************** 494 495-- Look for illegal values in pg_conversion fields. 496 497SELECT p1.oid, p1.conname 498FROM pg_conversion as p1 499WHERE p1.conproc = 0 OR 500 pg_encoding_to_char(conforencoding) = '' OR 501 pg_encoding_to_char(contoencoding) = ''; 502 503-- Look for conprocs that don't have the expected signature. 504 505SELECT p.oid, p.proname, c.oid, c.conname 506FROM pg_proc p, pg_conversion c 507WHERE p.oid = c.conproc AND 508 (p.prorettype != 'void'::regtype OR p.proretset OR 509 p.pronargs != 5 OR 510 p.proargtypes[0] != 'int4'::regtype OR 511 p.proargtypes[1] != 'int4'::regtype OR 512 p.proargtypes[2] != 'cstring'::regtype OR 513 p.proargtypes[3] != 'internal'::regtype OR 514 p.proargtypes[4] != 'int4'::regtype); 515 516-- Check for conprocs that don't perform the specific conversion that 517-- pg_conversion alleges they do, by trying to invoke each conversion 518-- on some simple ASCII data. (The conproc should throw an error if 519-- it doesn't accept the encodings that are passed to it.) 520-- Unfortunately, we can't test non-default conprocs this way, because 521-- there is no way to ask convert() to invoke them, and we cannot call 522-- them directly from SQL. But there are no non-default built-in 523-- conversions anyway. 524-- (Similarly, this doesn't cope with any search path issues.) 525 526SELECT p1.oid, p1.conname 527FROM pg_conversion as p1 528WHERE condefault AND 529 convert('ABC'::bytea, pg_encoding_to_char(conforencoding), 530 pg_encoding_to_char(contoencoding)) != 'ABC'; 531 532 533-- **************** pg_operator **************** 534 535-- Look for illegal values in pg_operator fields. 536 537SELECT p1.oid, p1.oprname 538FROM pg_operator as p1 539WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR 540 p1.oprresult = 0 OR p1.oprcode = 0; 541 542-- Look for missing or unwanted operand types 543 544SELECT p1.oid, p1.oprname 545FROM pg_operator as p1 546WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR 547 (p1.oprleft != 0 and p1.oprkind = 'l') OR 548 (p1.oprright = 0 and p1.oprkind != 'r') OR 549 (p1.oprright != 0 and p1.oprkind = 'r'); 550 551-- Look for conflicting operator definitions (same names and input datatypes). 552 553SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode 554FROM pg_operator AS p1, pg_operator AS p2 555WHERE p1.oid != p2.oid AND 556 p1.oprname = p2.oprname AND 557 p1.oprkind = p2.oprkind AND 558 p1.oprleft = p2.oprleft AND 559 p1.oprright = p2.oprright; 560 561-- Look for commutative operators that don't commute. 562-- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x. 563-- We expect that B will always say that B.oprcom = A as well; that's not 564-- inherently essential, but it would be inefficient not to mark it so. 565 566SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode 567FROM pg_operator AS p1, pg_operator AS p2 568WHERE p1.oprcom = p2.oid AND 569 (p1.oprkind != 'b' OR 570 p1.oprleft != p2.oprright OR 571 p1.oprright != p2.oprleft OR 572 p1.oprresult != p2.oprresult OR 573 p1.oid != p2.oprcom); 574 575-- Look for negatory operators that don't agree. 576-- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield 577-- boolean results, and (x A y) == ! (x B y), or the equivalent for 578-- single-operand operators. 579-- We expect that B will always say that B.oprnegate = A as well; that's not 580-- inherently essential, but it would be inefficient not to mark it so. 581-- Also, A and B had better not be the same operator. 582 583SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode 584FROM pg_operator AS p1, pg_operator AS p2 585WHERE p1.oprnegate = p2.oid AND 586 (p1.oprkind != p2.oprkind OR 587 p1.oprleft != p2.oprleft OR 588 p1.oprright != p2.oprright OR 589 p1.oprresult != 'bool'::regtype OR 590 p2.oprresult != 'bool'::regtype OR 591 p1.oid != p2.oprnegate OR 592 p1.oid = p2.oid); 593 594-- Make a list of the names of operators that are claimed to be commutator 595-- pairs. This list will grow over time, but before accepting a new entry 596-- make sure you didn't link the wrong operators. 597 598SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2 599FROM pg_operator o1, pg_operator o2 600WHERE o1.oprcom = o2.oid AND o1.oprname <= o2.oprname 601ORDER BY 1, 2; 602 603-- Likewise for negator pairs. 604 605SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2 606FROM pg_operator o1, pg_operator o2 607WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname 608ORDER BY 1, 2; 609 610-- A mergejoinable or hashjoinable operator must be binary, must return 611-- boolean, and must have a commutator (itself, unless it's a cross-type 612-- operator). 613 614SELECT p1.oid, p1.oprname FROM pg_operator AS p1 615WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT 616 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0); 617 618-- What's more, the commutator had better be mergejoinable/hashjoinable too. 619 620SELECT p1.oid, p1.oprname, p2.oid, p2.oprname 621FROM pg_operator AS p1, pg_operator AS p2 622WHERE p1.oprcom = p2.oid AND 623 (p1.oprcanmerge != p2.oprcanmerge OR 624 p1.oprcanhash != p2.oprcanhash); 625 626-- Mergejoinable operators should appear as equality members of btree index 627-- opfamilies. 628 629SELECT p1.oid, p1.oprname 630FROM pg_operator AS p1 631WHERE p1.oprcanmerge AND NOT EXISTS 632 (SELECT 1 FROM pg_amop 633 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND 634 amopopr = p1.oid AND amopstrategy = 3); 635 636-- And the converse. 637 638SELECT p1.oid, p1.oprname, p.amopfamily 639FROM pg_operator AS p1, pg_amop p 640WHERE amopopr = p1.oid 641 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') 642 AND amopstrategy = 3 643 AND NOT p1.oprcanmerge; 644 645-- Hashable operators should appear as members of hash index opfamilies. 646 647SELECT p1.oid, p1.oprname 648FROM pg_operator AS p1 649WHERE p1.oprcanhash AND NOT EXISTS 650 (SELECT 1 FROM pg_amop 651 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND 652 amopopr = p1.oid AND amopstrategy = 1); 653 654-- And the converse. 655 656SELECT p1.oid, p1.oprname, p.amopfamily 657FROM pg_operator AS p1, pg_amop p 658WHERE amopopr = p1.oid 659 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') 660 AND NOT p1.oprcanhash; 661 662-- Check that each operator defined in pg_operator matches its oprcode entry 663-- in pg_proc. Easiest to do this separately for each oprkind. 664 665SELECT p1.oid, p1.oprname, p2.oid, p2.proname 666FROM pg_operator AS p1, pg_proc AS p2 667WHERE p1.oprcode = p2.oid AND 668 p1.oprkind = 'b' AND 669 (p2.pronargs != 2 670 OR NOT binary_coercible(p2.prorettype, p1.oprresult) 671 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) 672 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1])); 673 674SELECT p1.oid, p1.oprname, p2.oid, p2.proname 675FROM pg_operator AS p1, pg_proc AS p2 676WHERE p1.oprcode = p2.oid AND 677 p1.oprkind = 'l' AND 678 (p2.pronargs != 1 679 OR NOT binary_coercible(p2.prorettype, p1.oprresult) 680 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0]) 681 OR p1.oprleft != 0); 682 683SELECT p1.oid, p1.oprname, p2.oid, p2.proname 684FROM pg_operator AS p1, pg_proc AS p2 685WHERE p1.oprcode = p2.oid AND 686 p1.oprkind = 'r' AND 687 (p2.pronargs != 1 688 OR NOT binary_coercible(p2.prorettype, p1.oprresult) 689 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) 690 OR p1.oprright != 0); 691 692-- If the operator is mergejoinable or hashjoinable, its underlying function 693-- should not be volatile. 694 695SELECT p1.oid, p1.oprname, p2.oid, p2.proname 696FROM pg_operator AS p1, pg_proc AS p2 697WHERE p1.oprcode = p2.oid AND 698 (p1.oprcanmerge OR p1.oprcanhash) AND 699 p2.provolatile = 'v'; 700 701-- If oprrest is set, the operator must return boolean, 702-- and it must link to a proc with the right signature 703-- to be a restriction selectivity estimator. 704-- The proc signature we want is: float8 proc(internal, oid, internal, int4) 705 706SELECT p1.oid, p1.oprname, p2.oid, p2.proname 707FROM pg_operator AS p1, pg_proc AS p2 708WHERE p1.oprrest = p2.oid AND 709 (p1.oprresult != 'bool'::regtype OR 710 p2.prorettype != 'float8'::regtype OR p2.proretset OR 711 p2.pronargs != 4 OR 712 p2.proargtypes[0] != 'internal'::regtype OR 713 p2.proargtypes[1] != 'oid'::regtype OR 714 p2.proargtypes[2] != 'internal'::regtype OR 715 p2.proargtypes[3] != 'int4'::regtype); 716 717-- If oprjoin is set, the operator must be a binary boolean op, 718-- and it must link to a proc with the right signature 719-- to be a join selectivity estimator. 720-- The proc signature we want is: float8 proc(internal, oid, internal, int2, internal) 721-- (Note: the old signature with only 4 args is still allowed, but no core 722-- estimator should be using it.) 723 724SELECT p1.oid, p1.oprname, p2.oid, p2.proname 725FROM pg_operator AS p1, pg_proc AS p2 726WHERE p1.oprjoin = p2.oid AND 727 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR 728 p2.prorettype != 'float8'::regtype OR p2.proretset OR 729 p2.pronargs != 5 OR 730 p2.proargtypes[0] != 'internal'::regtype OR 731 p2.proargtypes[1] != 'oid'::regtype OR 732 p2.proargtypes[2] != 'internal'::regtype OR 733 p2.proargtypes[3] != 'int2'::regtype OR 734 p2.proargtypes[4] != 'internal'::regtype); 735 736-- Insist that all built-in pg_operator entries have descriptions 737SELECT p1.oid, p1.oprname 738FROM pg_operator as p1 LEFT JOIN pg_description as d 739 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0 740WHERE d.classoid IS NULL AND p1.oid <= 9999; 741 742-- Check that operators' underlying functions have suitable comments, 743-- namely 'implementation of XXX operator'. (Note: it's not necessary to 744-- put such comments into pg_proc.h; initdb will generate them as needed.) 745-- In some cases involving legacy names for operators, there are multiple 746-- operators referencing the same pg_proc entry, so ignore operators whose 747-- comments say they are deprecated. 748-- We also have a few functions that are both operator support and meant to 749-- be called directly; those should have comments matching their operator. 750WITH funcdescs AS ( 751 SELECT p.oid as p_oid, proname, o.oid as o_oid, 752 pd.description as prodesc, 753 'implementation of ' || oprname || ' operator' as expecteddesc, 754 od.description as oprdesc 755 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid 756 LEFT JOIN pg_description pd ON 757 (pd.objoid = p.oid and pd.classoid = p.tableoid and pd.objsubid = 0) 758 LEFT JOIN pg_description od ON 759 (od.objoid = o.oid and od.classoid = o.tableoid and od.objsubid = 0) 760 WHERE o.oid <= 9999 761) 762SELECT * FROM funcdescs 763 WHERE prodesc IS DISTINCT FROM expecteddesc 764 AND oprdesc NOT LIKE 'deprecated%' 765 AND prodesc IS DISTINCT FROM oprdesc; 766 767-- Show all the operator-implementation functions that have their own 768-- comments. This should happen only in cases where the function and 769-- operator syntaxes are both documented at the user level. 770-- This should be a pretty short list; it's mostly legacy cases. 771WITH funcdescs AS ( 772 SELECT p.oid as p_oid, proname, o.oid as o_oid, 773 pd.description as prodesc, 774 'implementation of ' || oprname || ' operator' as expecteddesc, 775 od.description as oprdesc 776 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid 777 LEFT JOIN pg_description pd ON 778 (pd.objoid = p.oid and pd.classoid = p.tableoid and pd.objsubid = 0) 779 LEFT JOIN pg_description od ON 780 (od.objoid = o.oid and od.classoid = o.tableoid and od.objsubid = 0) 781 WHERE o.oid <= 9999 782) 783SELECT p_oid, proname, prodesc FROM funcdescs 784 WHERE prodesc IS DISTINCT FROM expecteddesc 785 AND oprdesc NOT LIKE 'deprecated%' 786ORDER BY 1; 787 788 789-- **************** pg_aggregate **************** 790 791-- Look for illegal values in pg_aggregate fields. 792 793SELECT ctid, aggfnoid::oid 794FROM pg_aggregate as p1 795WHERE aggfnoid = 0 OR aggtransfn = 0 OR 796 aggkind NOT IN ('n', 'o', 'h') OR 797 aggnumdirectargs < 0 OR 798 (aggkind = 'n' AND aggnumdirectargs > 0) OR 799 aggfinalmodify NOT IN ('r', 's', 'w') OR 800 aggmfinalmodify NOT IN ('r', 's', 'w') OR 801 aggtranstype = 0 OR aggtransspace < 0 OR aggmtransspace < 0; 802 803-- Make sure the matching pg_proc entry is sensible, too. 804 805SELECT a.aggfnoid::oid, p.proname 806FROM pg_aggregate as a, pg_proc as p 807WHERE a.aggfnoid = p.oid AND 808 (p.prokind != 'a' OR p.proretset OR p.pronargs < a.aggnumdirectargs); 809 810-- Make sure there are no prokind = PROKIND_AGGREGATE pg_proc entries without matches. 811 812SELECT oid, proname 813FROM pg_proc as p 814WHERE p.prokind = 'a' AND 815 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid); 816 817-- If there is no finalfn then the output type must be the transtype. 818 819SELECT a.aggfnoid::oid, p.proname 820FROM pg_aggregate as a, pg_proc as p 821WHERE a.aggfnoid = p.oid AND 822 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype; 823 824-- Cross-check transfn against its entry in pg_proc. 825-- NOTE: use physically_coercible here, not binary_coercible, because 826-- max and min on abstime are implemented using int4larger/int4smaller. 827SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 828FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 829WHERE a.aggfnoid = p.oid AND 830 a.aggtransfn = ptr.oid AND 831 (ptr.proretset 832 OR NOT (ptr.pronargs = 833 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1 834 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END) 835 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype) 836 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0]) 837 OR (p.pronargs > 0 AND 838 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) 839 OR (p.pronargs > 1 AND 840 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2])) 841 OR (p.pronargs > 2 AND 842 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3])) 843 -- we could carry the check further, but 3 args is enough for now 844 ); 845 846-- Cross-check finalfn (if present) against its entry in pg_proc. 847 848SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname 849FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn 850WHERE a.aggfnoid = p.oid AND 851 a.aggfinalfn = pfn.oid AND 852 (pfn.proretset OR 853 NOT binary_coercible(pfn.prorettype, p.prorettype) OR 854 NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]) OR 855 CASE WHEN a.aggfinalextra THEN pfn.pronargs != p.pronargs + 1 856 ELSE pfn.pronargs != a.aggnumdirectargs + 1 END 857 OR (pfn.pronargs > 1 AND 858 NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1])) 859 OR (pfn.pronargs > 2 AND 860 NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2])) 861 OR (pfn.pronargs > 3 AND 862 NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3])) 863 -- we could carry the check further, but 3 args is enough for now 864 ); 865 866-- If transfn is strict then either initval should be non-NULL, or 867-- input type should match transtype so that the first non-null input 868-- can be assigned as the state value. 869 870SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 871FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 872WHERE a.aggfnoid = p.oid AND 873 a.aggtransfn = ptr.oid AND ptr.proisstrict AND 874 a.agginitval IS NULL AND 875 NOT binary_coercible(p.proargtypes[0], a.aggtranstype); 876 877-- Check for inconsistent specifications of moving-aggregate columns. 878 879SELECT ctid, aggfnoid::oid 880FROM pg_aggregate as p1 881WHERE aggmtranstype != 0 AND 882 (aggmtransfn = 0 OR aggminvtransfn = 0); 883 884SELECT ctid, aggfnoid::oid 885FROM pg_aggregate as p1 886WHERE aggmtranstype = 0 AND 887 (aggmtransfn != 0 OR aggminvtransfn != 0 OR aggmfinalfn != 0 OR 888 aggmtransspace != 0 OR aggminitval IS NOT NULL); 889 890-- If there is no mfinalfn then the output type must be the mtranstype. 891 892SELECT a.aggfnoid::oid, p.proname 893FROM pg_aggregate as a, pg_proc as p 894WHERE a.aggfnoid = p.oid AND 895 a.aggmtransfn != 0 AND 896 a.aggmfinalfn = 0 AND p.prorettype != a.aggmtranstype; 897 898-- Cross-check mtransfn (if present) against its entry in pg_proc. 899SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 900FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 901WHERE a.aggfnoid = p.oid AND 902 a.aggmtransfn = ptr.oid AND 903 (ptr.proretset 904 OR NOT (ptr.pronargs = 905 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1 906 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END) 907 OR NOT physically_coercible(ptr.prorettype, a.aggmtranstype) 908 OR NOT physically_coercible(a.aggmtranstype, ptr.proargtypes[0]) 909 OR (p.pronargs > 0 AND 910 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) 911 OR (p.pronargs > 1 AND 912 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2])) 913 OR (p.pronargs > 2 AND 914 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3])) 915 -- we could carry the check further, but 3 args is enough for now 916 ); 917 918-- Cross-check minvtransfn (if present) against its entry in pg_proc. 919SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 920FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 921WHERE a.aggfnoid = p.oid AND 922 a.aggminvtransfn = ptr.oid AND 923 (ptr.proretset 924 OR NOT (ptr.pronargs = 925 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1 926 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END) 927 OR NOT physically_coercible(ptr.prorettype, a.aggmtranstype) 928 OR NOT physically_coercible(a.aggmtranstype, ptr.proargtypes[0]) 929 OR (p.pronargs > 0 AND 930 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) 931 OR (p.pronargs > 1 AND 932 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2])) 933 OR (p.pronargs > 2 AND 934 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3])) 935 -- we could carry the check further, but 3 args is enough for now 936 ); 937 938-- Cross-check mfinalfn (if present) against its entry in pg_proc. 939 940SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname 941FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn 942WHERE a.aggfnoid = p.oid AND 943 a.aggmfinalfn = pfn.oid AND 944 (pfn.proretset OR 945 NOT binary_coercible(pfn.prorettype, p.prorettype) OR 946 NOT binary_coercible(a.aggmtranstype, pfn.proargtypes[0]) OR 947 CASE WHEN a.aggmfinalextra THEN pfn.pronargs != p.pronargs + 1 948 ELSE pfn.pronargs != a.aggnumdirectargs + 1 END 949 OR (pfn.pronargs > 1 AND 950 NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1])) 951 OR (pfn.pronargs > 2 AND 952 NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2])) 953 OR (pfn.pronargs > 3 AND 954 NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3])) 955 -- we could carry the check further, but 3 args is enough for now 956 ); 957 958-- If mtransfn is strict then either minitval should be non-NULL, or 959-- input type should match mtranstype so that the first non-null input 960-- can be assigned as the state value. 961 962SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 963FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 964WHERE a.aggfnoid = p.oid AND 965 a.aggmtransfn = ptr.oid AND ptr.proisstrict AND 966 a.aggminitval IS NULL AND 967 NOT binary_coercible(p.proargtypes[0], a.aggmtranstype); 968 969-- mtransfn and minvtransfn should have same strictness setting. 970 971SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname, iptr.oid, iptr.proname 972FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr, pg_proc AS iptr 973WHERE a.aggfnoid = p.oid AND 974 a.aggmtransfn = ptr.oid AND 975 a.aggminvtransfn = iptr.oid AND 976 ptr.proisstrict != iptr.proisstrict; 977 978-- Check that all combine functions have signature 979-- combine(transtype, transtype) returns transtype 980-- NOTE: use physically_coercible here, not binary_coercible, because 981-- max and min on abstime are implemented using int4larger/int4smaller. 982 983SELECT a.aggfnoid, p.proname 984FROM pg_aggregate as a, pg_proc as p 985WHERE a.aggcombinefn = p.oid AND 986 (p.pronargs != 2 OR 987 p.prorettype != p.proargtypes[0] OR 988 p.prorettype != p.proargtypes[1] OR 989 NOT physically_coercible(a.aggtranstype, p.proargtypes[0])); 990 991-- Check that no combine function for an INTERNAL transtype is strict. 992 993SELECT a.aggfnoid, p.proname 994FROM pg_aggregate as a, pg_proc as p 995WHERE a.aggcombinefn = p.oid AND 996 a.aggtranstype = 'internal'::regtype AND p.proisstrict; 997 998-- serialize/deserialize functions should be specified only for aggregates 999-- with transtype internal and a combine function, and we should have both 1000-- or neither of them. 1001 1002SELECT aggfnoid, aggtranstype, aggserialfn, aggdeserialfn 1003FROM pg_aggregate 1004WHERE (aggserialfn != 0 OR aggdeserialfn != 0) 1005 AND (aggtranstype != 'internal'::regtype OR aggcombinefn = 0 OR 1006 aggserialfn = 0 OR aggdeserialfn = 0); 1007 1008-- Check that all serialization functions have signature 1009-- serialize(internal) returns bytea 1010-- Also insist that they be strict; it's wasteful to run them on NULLs. 1011 1012SELECT a.aggfnoid, p.proname 1013FROM pg_aggregate as a, pg_proc as p 1014WHERE a.aggserialfn = p.oid AND 1015 (p.prorettype != 'bytea'::regtype OR p.pronargs != 1 OR 1016 p.proargtypes[0] != 'internal'::regtype OR 1017 NOT p.proisstrict); 1018 1019-- Check that all deserialization functions have signature 1020-- deserialize(bytea, internal) returns internal 1021-- Also insist that they be strict; it's wasteful to run them on NULLs. 1022 1023SELECT a.aggfnoid, p.proname 1024FROM pg_aggregate as a, pg_proc as p 1025WHERE a.aggdeserialfn = p.oid AND 1026 (p.prorettype != 'internal'::regtype OR p.pronargs != 2 OR 1027 p.proargtypes[0] != 'bytea'::regtype OR 1028 p.proargtypes[1] != 'internal'::regtype OR 1029 NOT p.proisstrict); 1030 1031-- Check that aggregates which have the same transition function also have 1032-- the same combine, serialization, and deserialization functions. 1033-- While that isn't strictly necessary, it's fishy if they don't. 1034 1035SELECT a.aggfnoid, a.aggcombinefn, a.aggserialfn, a.aggdeserialfn, 1036 b.aggfnoid, b.aggcombinefn, b.aggserialfn, b.aggdeserialfn 1037FROM 1038 pg_aggregate a, pg_aggregate b 1039WHERE 1040 a.aggfnoid < b.aggfnoid AND a.aggtransfn = b.aggtransfn AND 1041 (a.aggcombinefn != b.aggcombinefn OR a.aggserialfn != b.aggserialfn 1042 OR a.aggdeserialfn != b.aggdeserialfn); 1043 1044-- Cross-check aggsortop (if present) against pg_operator. 1045-- We expect to find entries for bool_and, bool_or, every, max, and min. 1046 1047SELECT DISTINCT proname, oprname 1048FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p 1049WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid 1050ORDER BY 1, 2; 1051 1052-- Check datatypes match 1053 1054SELECT a.aggfnoid::oid, o.oid 1055FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p 1056WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND 1057 (oprkind != 'b' OR oprresult != 'boolean'::regtype 1058 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]); 1059 1060-- Check operator is a suitable btree opfamily member 1061 1062SELECT a.aggfnoid::oid, o.oid 1063FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p 1064WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND 1065 NOT EXISTS(SELECT 1 FROM pg_amop 1066 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') 1067 AND amopopr = o.oid 1068 AND amoplefttype = o.oprleft 1069 AND amoprighttype = o.oprright); 1070 1071-- Check correspondence of btree strategies and names 1072 1073SELECT DISTINCT proname, oprname, amopstrategy 1074FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p, 1075 pg_amop as ao 1076WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND 1077 amopopr = o.oid AND 1078 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') 1079ORDER BY 1, 2; 1080 1081-- Check that there are not aggregates with the same name and different 1082-- numbers of arguments. While not technically wrong, we have a project policy 1083-- to avoid this because it opens the door for confusion in connection with 1084-- ORDER BY: novices frequently put the ORDER BY in the wrong place. 1085-- See the fate of the single-argument form of string_agg() for history. 1086-- (Note: we don't forbid users from creating such aggregates; the policy is 1087-- just to think twice before creating built-in aggregates like this.) 1088-- The only aggregates that should show up here are count(x) and count(*). 1089 1090SELECT p1.oid::regprocedure, p2.oid::regprocedure 1091FROM pg_proc AS p1, pg_proc AS p2 1092WHERE p1.oid < p2.oid AND p1.proname = p2.proname AND 1093 p1.prokind = 'a' AND p2.prokind = 'a' AND 1094 array_dims(p1.proargtypes) != array_dims(p2.proargtypes) 1095ORDER BY 1; 1096 1097-- For the same reason, built-in aggregates with default arguments are no good. 1098 1099SELECT oid, proname 1100FROM pg_proc AS p 1101WHERE prokind = 'a' AND proargdefaults IS NOT NULL; 1102 1103-- For the same reason, we avoid creating built-in variadic aggregates, except 1104-- that variadic ordered-set aggregates are OK (since they have special syntax 1105-- that is not subject to the misplaced ORDER BY issue). 1106 1107SELECT p.oid, proname 1108FROM pg_proc AS p JOIN pg_aggregate AS a ON a.aggfnoid = p.oid 1109WHERE prokind = 'a' AND provariadic != 0 AND a.aggkind = 'n'; 1110 1111 1112-- **************** pg_opfamily **************** 1113 1114-- Look for illegal values in pg_opfamily fields 1115 1116SELECT p1.oid 1117FROM pg_opfamily as p1 1118WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0; 1119 1120 1121-- **************** pg_opclass **************** 1122 1123-- Look for illegal values in pg_opclass fields 1124 1125SELECT p1.oid 1126FROM pg_opclass AS p1 1127WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0 1128 OR p1.opcintype = 0; 1129 1130-- opcmethod must match owning opfamily's opfmethod 1131 1132SELECT p1.oid, p2.oid 1133FROM pg_opclass AS p1, pg_opfamily AS p2 1134WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod; 1135 1136-- There should not be multiple entries in pg_opclass with opcdefault true 1137-- and the same opcmethod/opcintype combination. 1138 1139SELECT p1.oid, p2.oid 1140FROM pg_opclass AS p1, pg_opclass AS p2 1141WHERE p1.oid != p2.oid AND 1142 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND 1143 p1.opcdefault AND p2.opcdefault; 1144 1145-- Ask access methods to validate opclasses 1146-- (this replaces a lot of SQL-level checks that used to be done in this file) 1147 1148SELECT oid, opcname FROM pg_opclass WHERE NOT amvalidate(oid); 1149 1150 1151-- **************** pg_am **************** 1152 1153-- Look for illegal values in pg_am fields 1154 1155SELECT p1.oid, p1.amname 1156FROM pg_am AS p1 1157WHERE p1.amhandler = 0; 1158 1159-- Check for amhandler functions with the wrong signature 1160 1161SELECT p1.oid, p1.amname, p2.oid, p2.proname 1162FROM pg_am AS p1, pg_proc AS p2 1163WHERE p2.oid = p1.amhandler AND 1164 (p2.prorettype != 'index_am_handler'::regtype OR p2.proretset 1165 OR p2.pronargs != 1 1166 OR p2.proargtypes[0] != 'internal'::regtype); 1167 1168 1169-- **************** pg_amop **************** 1170 1171-- Look for illegal values in pg_amop fields 1172 1173SELECT p1.amopfamily, p1.amopstrategy 1174FROM pg_amop as p1 1175WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0 1176 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1; 1177 1178SELECT p1.amopfamily, p1.amopstrategy 1179FROM pg_amop as p1 1180WHERE NOT ((p1.amoppurpose = 's' AND p1.amopsortfamily = 0) OR 1181 (p1.amoppurpose = 'o' AND p1.amopsortfamily <> 0)); 1182 1183-- amopmethod must match owning opfamily's opfmethod 1184 1185SELECT p1.oid, p2.oid 1186FROM pg_amop AS p1, pg_opfamily AS p2 1187WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod; 1188 1189-- Make a list of all the distinct operator names being used in particular 1190-- strategy slots. This is a bit hokey, since the list might need to change 1191-- in future releases, but it's an effective way of spotting mistakes such as 1192-- swapping two operators within a family. 1193 1194SELECT DISTINCT amopmethod, amopstrategy, oprname 1195FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid 1196ORDER BY 1, 2, 3; 1197 1198-- Check that all opclass search operators have selectivity estimators. 1199-- This is not absolutely required, but it seems a reasonable thing 1200-- to insist on for all standard datatypes. 1201 1202SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname 1203FROM pg_amop AS p1, pg_operator AS p2 1204WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND 1205 (p2.oprrest = 0 OR p2.oprjoin = 0); 1206 1207-- Check that each opclass in an opfamily has associated operators, that is 1208-- ones whose oprleft matches opcintype (possibly by coercion). 1209 1210SELECT p1.opcname, p1.opcfamily 1211FROM pg_opclass AS p1 1212WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2 1213 WHERE p2.amopfamily = p1.opcfamily 1214 AND binary_coercible(p1.opcintype, p2.amoplefttype)); 1215 1216-- Check that each operator listed in pg_amop has an associated opclass, 1217-- that is one whose opcintype matches oprleft (possibly by coercion). 1218-- Otherwise the operator is useless because it cannot be matched to an index. 1219-- (In principle it could be useful to list such operators in multiple-datatype 1220-- btree opfamilies, but in practice you'd expect there to be an opclass for 1221-- every datatype the family knows about.) 1222 1223SELECT p1.amopfamily, p1.amopstrategy, p1.amopopr 1224FROM pg_amop AS p1 1225WHERE NOT EXISTS(SELECT 1 FROM pg_opclass AS p2 1226 WHERE p2.opcfamily = p1.amopfamily 1227 AND binary_coercible(p2.opcintype, p1.amoplefttype)); 1228 1229-- Operators that are primary members of opclasses must be immutable (else 1230-- it suggests that the index ordering isn't fixed). Operators that are 1231-- cross-type members need only be stable, since they are just shorthands 1232-- for index probe queries. 1233 1234SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc 1235FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3 1236WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND 1237 p1.amoplefttype = p1.amoprighttype AND 1238 p3.provolatile != 'i'; 1239 1240SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc 1241FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3 1242WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND 1243 p1.amoplefttype != p1.amoprighttype AND 1244 p3.provolatile = 'v'; 1245 1246 1247-- **************** pg_amproc **************** 1248 1249-- Look for illegal values in pg_amproc fields 1250 1251SELECT p1.amprocfamily, p1.amprocnum 1252FROM pg_amproc as p1 1253WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0 1254 OR p1.amprocnum < 1 OR p1.amproc = 0; 1255 1256-- Support routines that are primary members of opfamilies must be immutable 1257-- (else it suggests that the index ordering isn't fixed). But cross-type 1258-- members need only be stable, since they are just shorthands 1259-- for index probe queries. 1260 1261SELECT p1.amprocfamily, p1.amproc, p2.prosrc 1262FROM pg_amproc AS p1, pg_proc AS p2 1263WHERE p1.amproc = p2.oid AND 1264 p1.amproclefttype = p1.amprocrighttype AND 1265 p2.provolatile != 'i'; 1266 1267SELECT p1.amprocfamily, p1.amproc, p2.prosrc 1268FROM pg_amproc AS p1, pg_proc AS p2 1269WHERE p1.amproc = p2.oid AND 1270 p1.amproclefttype != p1.amprocrighttype AND 1271 p2.provolatile = 'v'; 1272 1273 1274-- **************** pg_index **************** 1275 1276-- Look for illegal values in pg_index fields. 1277 1278SELECT p1.indexrelid, p1.indrelid 1279FROM pg_index as p1 1280WHERE p1.indexrelid = 0 OR p1.indrelid = 0 OR 1281 p1.indnatts <= 0 OR p1.indnatts > 32; 1282 1283-- oidvector and int2vector fields should be of length indnatts. 1284 1285SELECT p1.indexrelid, p1.indrelid 1286FROM pg_index as p1 1287WHERE array_lower(indkey, 1) != 0 OR array_upper(indkey, 1) != indnatts-1 OR 1288 array_lower(indclass, 1) != 0 OR array_upper(indclass, 1) != indnatts-1 OR 1289 array_lower(indcollation, 1) != 0 OR array_upper(indcollation, 1) != indnatts-1 OR 1290 array_lower(indoption, 1) != 0 OR array_upper(indoption, 1) != indnatts-1; 1291 1292-- Check that opclasses and collations match the underlying columns. 1293-- (As written, this test ignores expression indexes.) 1294 1295SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname 1296FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey, 1297 unnest(indclass) as iclass, unnest(indcollation) as icoll 1298 FROM pg_index) ss, 1299 pg_attribute a, 1300 pg_opclass opc 1301WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND 1302 (NOT binary_coercible(atttypid, opcintype) OR icoll != attcollation); 1303 1304-- For system catalogs, be even tighter: nearly all indexes should be 1305-- exact type matches not binary-coercible matches. At this writing 1306-- the only exception is an OID index on a regproc column. 1307 1308SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname 1309FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey, 1310 unnest(indclass) as iclass, unnest(indcollation) as icoll 1311 FROM pg_index 1312 WHERE indrelid < 16384) ss, 1313 pg_attribute a, 1314 pg_opclass opc 1315WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND 1316 (opcintype != atttypid OR icoll != attcollation) 1317ORDER BY 1; 1318 1319-- Check for system catalogs with collation-sensitive ordering. This is not 1320-- a representational error in pg_index, but simply wrong catalog design. 1321-- It's bad because we expect to be able to clone template0 and assign the 1322-- copy a different database collation. It would especially not work for 1323-- shared catalogs. Note that although text columns will show a collation 1324-- in indcollation, they're still okay to index with text_pattern_ops, 1325-- so allow that case. 1326 1327SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll 1328FROM (SELECT indexrelid, indrelid, 1329 unnest(indclass) as iclass, unnest(indcollation) as icoll 1330 FROM pg_index 1331 WHERE indrelid < 16384) ss 1332WHERE icoll != 0 AND iclass != 1333 (SELECT oid FROM pg_opclass 1334 WHERE opcname = 'text_pattern_ops' AND opcmethod = 1335 (SELECT oid FROM pg_am WHERE amname = 'btree')); 1336