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