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-- Helper functions to deal with cases where binary-coercible matches are 20-- allowed. 21-- This should match IsBinaryCoercible() in parse_coerce.c. 22create function binary_coercible(oid, oid) returns bool as $$ 23begin 24 if $1 = $2 then return true; end if; 25 if EXISTS(select 1 from pg_catalog.pg_cast where 26 castsource = $1 and casttarget = $2 and 27 castmethod = 'b' and castcontext = 'i') 28 then return true; end if; 29 if $2 = 'pg_catalog.any'::pg_catalog.regtype then return true; end if; 30 if $2 = 'pg_catalog.anyarray'::pg_catalog.regtype then 31 if EXISTS(select 1 from pg_catalog.pg_type where 32 oid = $1 and typelem != 0 and typlen = -1) 33 then return true; end if; 34 end if; 35 if $2 = 'pg_catalog.anyrange'::pg_catalog.regtype then 36 if (select typtype from pg_catalog.pg_type where oid = $1) = 'r' 37 then return true; end if; 38 end if; 39 return false; 40end 41$$ language plpgsql strict stable; 42-- This one ignores castcontext, so it considers only physical equivalence 43-- and not whether the coercion can be invoked implicitly. 44create function physically_coercible(oid, oid) returns bool as $$ 45begin 46 if $1 = $2 then return true; end if; 47 if EXISTS(select 1 from pg_catalog.pg_cast where 48 castsource = $1 and casttarget = $2 and 49 castmethod = 'b') 50 then return true; end if; 51 if $2 = 'pg_catalog.any'::pg_catalog.regtype then return true; end if; 52 if $2 = 'pg_catalog.anyarray'::pg_catalog.regtype then 53 if EXISTS(select 1 from pg_catalog.pg_type where 54 oid = $1 and typelem != 0 and typlen = -1) 55 then return true; end if; 56 end if; 57 if $2 = 'pg_catalog.anyrange'::pg_catalog.regtype then 58 if (select typtype from pg_catalog.pg_type where oid = $1) = 'r' 59 then return true; end if; 60 end if; 61 return false; 62end 63$$ language plpgsql strict stable; 64-- **************** pg_proc **************** 65-- Look for illegal values in pg_proc fields. 66SELECT p1.oid, p1.proname 67FROM pg_proc as p1 68WHERE p1.prolang = 0 OR p1.prorettype = 0 OR 69 p1.pronargs < 0 OR 70 p1.pronargdefaults < 0 OR 71 p1.pronargdefaults > p1.pronargs OR 72 array_lower(p1.proargtypes, 1) != 0 OR 73 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR 74 0::oid = ANY (p1.proargtypes) OR 75 procost <= 0 OR 76 CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END OR 77 prokind NOT IN ('f', 'a', 'w', 'p') OR 78 provolatile NOT IN ('i', 's', 'v') OR 79 proparallel NOT IN ('s', 'r', 'u'); 80 oid | proname 81-----+--------- 82(0 rows) 83 84-- prosrc should never be null or empty 85SELECT p1.oid, p1.proname 86FROM pg_proc as p1 87WHERE prosrc IS NULL OR prosrc = '' OR prosrc = '-'; 88 oid | proname 89-----+--------- 90(0 rows) 91 92-- proretset should only be set for normal functions 93SELECT p1.oid, p1.proname 94FROM pg_proc AS p1 95WHERE proretset AND prokind != 'f'; 96 oid | proname 97-----+--------- 98(0 rows) 99 100-- currently, no built-in functions should be SECURITY DEFINER; 101-- this might change in future, but there will probably never be many. 102SELECT p1.oid, p1.proname 103FROM pg_proc AS p1 104WHERE prosecdef 105ORDER BY 1; 106 oid | proname 107-----+--------- 108(0 rows) 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 oid | proname 115-----+--------- 116(0 rows) 117 118-- probin should be non-empty for C functions, null everywhere else 119SELECT p1.oid, p1.proname 120FROM pg_proc as p1 121WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-'); 122 oid | proname 123-----+--------- 124(0 rows) 125 126SELECT p1.oid, p1.proname 127FROM pg_proc as p1 128WHERE prolang != 13 AND probin IS NOT NULL; 129 oid | proname 130-----+--------- 131(0 rows) 132 133-- Look for conflicting proc definitions (same names and input datatypes). 134-- (This test should be dead code now that we have the unique index 135-- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.) 136SELECT p1.oid, p1.proname, p2.oid, p2.proname 137FROM pg_proc AS p1, pg_proc AS p2 138WHERE p1.oid != p2.oid AND 139 p1.proname = p2.proname AND 140 p1.pronargs = p2.pronargs AND 141 p1.proargtypes = p2.proargtypes; 142 oid | proname | oid | proname 143-----+---------+-----+--------- 144(0 rows) 145 146-- Considering only built-in procs (prolang = 12), look for multiple uses 147-- of the same internal function (ie, matching prosrc fields). It's OK to 148-- have several entries with different pronames for the same internal function, 149-- but conflicts in the number of arguments and other critical items should 150-- be complained of. (We don't check data types here; see next query.) 151-- Note: ignore aggregate functions here, since they all point to the same 152-- dummy built-in function. 153SELECT p1.oid, p1.proname, p2.oid, p2.proname 154FROM pg_proc AS p1, pg_proc AS p2 155WHERE p1.oid < p2.oid AND 156 p1.prosrc = p2.prosrc AND 157 p1.prolang = 12 AND p2.prolang = 12 AND 158 (p1.prokind != 'a' OR p2.prokind != 'a') AND 159 (p1.prolang != p2.prolang OR 160 p1.prokind != p2.prokind OR 161 p1.prosecdef != p2.prosecdef OR 162 p1.proleakproof != p2.proleakproof OR 163 p1.proisstrict != p2.proisstrict OR 164 p1.proretset != p2.proretset OR 165 p1.provolatile != p2.provolatile OR 166 p1.pronargs != p2.pronargs); 167 oid | proname | oid | proname 168-----+---------+-----+--------- 169(0 rows) 170 171-- Look for uses of different type OIDs in the argument/result type fields 172-- for different aliases of the same built-in function. 173-- This indicates that the types are being presumed to be binary-equivalent, 174-- or that the built-in function is prepared to deal with different types. 175-- That's not wrong, necessarily, but we make lists of all the types being 176-- so treated. Note that the expected output of this part of the test will 177-- need to be modified whenever new pairs of types are made binary-equivalent, 178-- or when new polymorphic built-in functions are added! 179-- Note: ignore aggregate functions here, since they all point to the same 180-- dummy built-in function. Likewise, ignore range constructor functions. 181SELECT DISTINCT p1.prorettype, p2.prorettype 182FROM pg_proc AS p1, pg_proc AS p2 183WHERE p1.oid != p2.oid AND 184 p1.prosrc = p2.prosrc AND 185 p1.prolang = 12 AND p2.prolang = 12 AND 186 p1.prokind != 'a' AND p2.prokind != 'a' AND 187 p1.prosrc NOT LIKE E'range\\_constructor_' AND 188 p2.prosrc NOT LIKE E'range\\_constructor_' AND 189 (p1.prorettype < p2.prorettype) 190ORDER BY 1, 2; 191 prorettype | prorettype 192------------+------------ 193 25 | 1043 194 1114 | 1184 195(2 rows) 196 197SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0] 198FROM pg_proc AS p1, pg_proc AS p2 199WHERE p1.oid != p2.oid AND 200 p1.prosrc = p2.prosrc AND 201 p1.prolang = 12 AND p2.prolang = 12 AND 202 p1.prokind != 'a' AND p2.prokind != 'a' AND 203 p1.prosrc NOT LIKE E'range\\_constructor_' AND 204 p2.prosrc NOT LIKE E'range\\_constructor_' AND 205 (p1.proargtypes[0] < p2.proargtypes[0]) 206ORDER BY 1, 2; 207 proargtypes | proargtypes 208-------------+------------- 209 25 | 1042 210 25 | 1043 211 1114 | 1184 212 1560 | 1562 213(4 rows) 214 215SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1] 216FROM pg_proc AS p1, pg_proc AS p2 217WHERE p1.oid != p2.oid AND 218 p1.prosrc = p2.prosrc AND 219 p1.prolang = 12 AND p2.prolang = 12 AND 220 p1.prokind != 'a' AND p2.prokind != 'a' AND 221 p1.prosrc NOT LIKE E'range\\_constructor_' AND 222 p2.prosrc NOT LIKE E'range\\_constructor_' AND 223 (p1.proargtypes[1] < p2.proargtypes[1]) 224ORDER BY 1, 2; 225 proargtypes | proargtypes 226-------------+------------- 227 23 | 28 228 1114 | 1184 229 1560 | 1562 230(3 rows) 231 232SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2] 233FROM pg_proc AS p1, pg_proc AS p2 234WHERE p1.oid != p2.oid AND 235 p1.prosrc = p2.prosrc AND 236 p1.prolang = 12 AND p2.prolang = 12 AND 237 p1.prokind != 'a' AND p2.prokind != 'a' AND 238 (p1.proargtypes[2] < p2.proargtypes[2]) 239ORDER BY 1, 2; 240 proargtypes | proargtypes 241-------------+------------- 242 1114 | 1184 243(1 row) 244 245SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3] 246FROM pg_proc AS p1, pg_proc AS p2 247WHERE p1.oid != p2.oid AND 248 p1.prosrc = p2.prosrc AND 249 p1.prolang = 12 AND p2.prolang = 12 AND 250 p1.prokind != 'a' AND p2.prokind != 'a' AND 251 (p1.proargtypes[3] < p2.proargtypes[3]) 252ORDER BY 1, 2; 253 proargtypes | proargtypes 254-------------+------------- 255 1114 | 1184 256(1 row) 257 258SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4] 259FROM pg_proc AS p1, pg_proc AS p2 260WHERE p1.oid != p2.oid AND 261 p1.prosrc = p2.prosrc AND 262 p1.prolang = 12 AND p2.prolang = 12 AND 263 p1.prokind != 'a' AND p2.prokind != 'a' AND 264 (p1.proargtypes[4] < p2.proargtypes[4]) 265ORDER BY 1, 2; 266 proargtypes | proargtypes 267-------------+------------- 268(0 rows) 269 270SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5] 271FROM pg_proc AS p1, pg_proc AS p2 272WHERE p1.oid != p2.oid AND 273 p1.prosrc = p2.prosrc AND 274 p1.prolang = 12 AND p2.prolang = 12 AND 275 p1.prokind != 'a' AND p2.prokind != 'a' AND 276 (p1.proargtypes[5] < p2.proargtypes[5]) 277ORDER BY 1, 2; 278 proargtypes | proargtypes 279-------------+------------- 280(0 rows) 281 282SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6] 283FROM pg_proc AS p1, pg_proc AS p2 284WHERE p1.oid != p2.oid AND 285 p1.prosrc = p2.prosrc AND 286 p1.prolang = 12 AND p2.prolang = 12 AND 287 p1.prokind != 'a' AND p2.prokind != 'a' AND 288 (p1.proargtypes[6] < p2.proargtypes[6]) 289ORDER BY 1, 2; 290 proargtypes | proargtypes 291-------------+------------- 292(0 rows) 293 294SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7] 295FROM pg_proc AS p1, pg_proc AS p2 296WHERE p1.oid != p2.oid AND 297 p1.prosrc = p2.prosrc AND 298 p1.prolang = 12 AND p2.prolang = 12 AND 299 p1.prokind != 'a' AND p2.prokind != 'a' AND 300 (p1.proargtypes[7] < p2.proargtypes[7]) 301ORDER BY 1, 2; 302 proargtypes | proargtypes 303-------------+------------- 304(0 rows) 305 306-- Look for functions that return type "internal" and do not have any 307-- "internal" argument. Such a function would be a security hole since 308-- it might be used to call an internal function from an SQL command. 309-- As of 7.3 this query should find only internal_in, which is safe because 310-- it always throws an error when called. 311SELECT p1.oid, p1.proname 312FROM pg_proc as p1 313WHERE p1.prorettype = 'internal'::regtype AND NOT 314 'internal'::regtype = ANY (p1.proargtypes); 315 oid | proname 316------+------------- 317 2304 | internal_in 318(1 row) 319 320-- Look for functions that return a polymorphic type and do not have any 321-- polymorphic argument. Calls of such functions would be unresolvable 322-- at parse time. As of 9.6 this query should find only some input functions 323-- and GiST support functions associated with these pseudotypes. 324SELECT p1.oid, p1.proname 325FROM pg_proc as p1 326WHERE p1.prorettype IN 327 ('anyelement'::regtype, 'anyarray'::regtype, 'anynonarray'::regtype, 328 'anyenum'::regtype, 'anyrange'::regtype) 329 AND NOT 330 ('anyelement'::regtype = ANY (p1.proargtypes) OR 331 'anyarray'::regtype = ANY (p1.proargtypes) OR 332 'anynonarray'::regtype = ANY (p1.proargtypes) OR 333 'anyenum'::regtype = ANY (p1.proargtypes) OR 334 'anyrange'::regtype = ANY (p1.proargtypes)) 335ORDER BY 2; 336 oid | proname 337------+------------------ 338 2296 | anyarray_in 339 2502 | anyarray_recv 340 2312 | anyelement_in 341 3504 | anyenum_in 342 2777 | anynonarray_in 343 3832 | anyrange_in 344 750 | array_in 345 2400 | array_recv 346 3506 | enum_in 347 3532 | enum_recv 348 3876 | range_gist_union 349 3834 | range_in 350 3836 | range_recv 351(13 rows) 352 353-- Look for functions that accept cstring and are neither datatype input 354-- functions nor encoding conversion functions. It's almost never a good 355-- idea to use cstring input for a function meant to be called from SQL; 356-- text should be used instead, because cstring lacks suitable casts. 357-- As of 9.6 this query should find only cstring_out and cstring_send. 358-- However, we must manually exclude shell_in, which might or might not be 359-- rejected by the EXISTS clause depending on whether there are currently 360-- any shell types. 361SELECT p1.oid, p1.proname 362FROM pg_proc as p1 363WHERE 'cstring'::regtype = ANY (p1.proargtypes) 364 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typinput = p1.oid) 365 AND NOT EXISTS(SELECT 1 FROM pg_conversion WHERE conproc = p1.oid) 366 AND p1.oid != 'shell_in(cstring)'::regprocedure 367ORDER BY 1; 368 oid | proname 369------+-------------- 370 2293 | cstring_out 371 2501 | cstring_send 372(2 rows) 373 374-- Likewise, look for functions that return cstring and aren't datatype output 375-- functions nor typmod output functions. 376-- As of 9.6 this query should find only cstring_in and cstring_recv. 377-- However, we must manually exclude shell_out. 378SELECT p1.oid, p1.proname 379FROM pg_proc as p1 380WHERE p1.prorettype = 'cstring'::regtype 381 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typoutput = p1.oid) 382 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typmodout = p1.oid) 383 AND p1.oid != 'shell_out(opaque)'::regprocedure 384ORDER BY 1; 385 oid | proname 386------+-------------- 387 2292 | cstring_in 388 2500 | cstring_recv 389(2 rows) 390 391-- Check for length inconsistencies between the various argument-info arrays. 392SELECT p1.oid, p1.proname 393FROM pg_proc as p1 394WHERE proallargtypes IS NOT NULL AND 395 array_length(proallargtypes,1) < array_length(proargtypes,1); 396 oid | proname 397-----+--------- 398(0 rows) 399 400SELECT p1.oid, p1.proname 401FROM pg_proc as p1 402WHERE proargmodes IS NOT NULL AND 403 array_length(proargmodes,1) < array_length(proargtypes,1); 404 oid | proname 405-----+--------- 406(0 rows) 407 408SELECT p1.oid, p1.proname 409FROM pg_proc as p1 410WHERE proargnames IS NOT NULL AND 411 array_length(proargnames,1) < array_length(proargtypes,1); 412 oid | proname 413-----+--------- 414(0 rows) 415 416SELECT p1.oid, p1.proname 417FROM pg_proc as p1 418WHERE proallargtypes IS NOT NULL AND proargmodes IS NOT NULL AND 419 array_length(proallargtypes,1) <> array_length(proargmodes,1); 420 oid | proname 421-----+--------- 422(0 rows) 423 424SELECT p1.oid, p1.proname 425FROM pg_proc as p1 426WHERE proallargtypes IS NOT NULL AND proargnames IS NOT NULL AND 427 array_length(proallargtypes,1) <> array_length(proargnames,1); 428 oid | proname 429-----+--------- 430(0 rows) 431 432SELECT p1.oid, p1.proname 433FROM pg_proc as p1 434WHERE proargmodes IS NOT NULL AND proargnames IS NOT NULL AND 435 array_length(proargmodes,1) <> array_length(proargnames,1); 436 oid | proname 437-----+--------- 438(0 rows) 439 440-- Check that proallargtypes matches proargtypes 441SELECT p1.oid, p1.proname, p1.proargtypes, p1.proallargtypes, p1.proargmodes 442FROM pg_proc as p1 443WHERE proallargtypes IS NOT NULL AND 444 ARRAY(SELECT unnest(proargtypes)) <> 445 ARRAY(SELECT proallargtypes[i] 446 FROM generate_series(1, array_length(proallargtypes, 1)) g(i) 447 WHERE proargmodes IS NULL OR proargmodes[i] IN ('i', 'b', 'v')); 448 oid | proname | proargtypes | proallargtypes | proargmodes 449-----+---------+-------------+----------------+------------- 450(0 rows) 451 452-- Check for protransform functions with the wrong signature 453SELECT p1.oid, p1.proname, p2.oid, p2.proname 454FROM pg_proc AS p1, pg_proc AS p2 455WHERE p2.oid = p1.protransform AND 456 (p2.prorettype != 'internal'::regtype OR p2.proretset OR p2.pronargs != 1 457 OR p2.proargtypes[0] != 'internal'::regtype); 458 oid | proname | oid | proname 459-----+---------+-----+--------- 460(0 rows) 461 462-- Insist that all built-in pg_proc entries have descriptions 463SELECT p1.oid, p1.proname 464FROM pg_proc as p1 LEFT JOIN pg_description as d 465 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0 466WHERE d.classoid IS NULL AND p1.oid <= 9999; 467 oid | proname 468-----+--------- 469(0 rows) 470 471-- List of built-in leakproof functions 472-- 473-- Leakproof functions should only be added after carefully 474-- scrutinizing all possibly executed codepaths for possible 475-- information leaks. Don't add functions here unless you know what a 476-- leakproof function is. If unsure, don't mark it as such. 477-- temporarily disable fancy output, so catalog changes create less diff noise 478\a\t 479SELECT p1.oid::regprocedure 480FROM pg_proc p1 JOIN pg_namespace pn 481 ON pronamespace = pn.oid 482WHERE nspname = 'pg_catalog' AND proleakproof 483ORDER BY 1; 484boollt(boolean,boolean) 485boolgt(boolean,boolean) 486booleq(boolean,boolean) 487chareq("char","char") 488nameeq(name,name) 489int2eq(smallint,smallint) 490int2lt(smallint,smallint) 491int4eq(integer,integer) 492int4lt(integer,integer) 493texteq(text,text) 494xideq(xid,xid) 495cideq(cid,cid) 496charne("char","char") 497charle("char","char") 498chargt("char","char") 499charge("char","char") 500boolne(boolean,boolean) 501int4ne(integer,integer) 502int2ne(smallint,smallint) 503int2gt(smallint,smallint) 504int4gt(integer,integer) 505int2le(smallint,smallint) 506int4le(integer,integer) 507int4ge(integer,integer) 508int2ge(smallint,smallint) 509textne(text,text) 510int24eq(smallint,integer) 511int42eq(integer,smallint) 512int24lt(smallint,integer) 513int42lt(integer,smallint) 514int24gt(smallint,integer) 515int42gt(integer,smallint) 516int24ne(smallint,integer) 517int42ne(integer,smallint) 518int24le(smallint,integer) 519int42le(integer,smallint) 520int24ge(smallint,integer) 521int42ge(integer,smallint) 522oideq(oid,oid) 523oidne(oid,oid) 524abstimeeq(abstime,abstime) 525abstimene(abstime,abstime) 526abstimelt(abstime,abstime) 527abstimegt(abstime,abstime) 528abstimele(abstime,abstime) 529abstimege(abstime,abstime) 530reltimeeq(reltime,reltime) 531reltimene(reltime,reltime) 532reltimelt(reltime,reltime) 533reltimegt(reltime,reltime) 534reltimele(reltime,reltime) 535reltimege(reltime,reltime) 536tintervalleneq(tinterval,reltime) 537tintervallenne(tinterval,reltime) 538tintervallenlt(tinterval,reltime) 539tintervallengt(tinterval,reltime) 540tintervallenle(tinterval,reltime) 541tintervallenge(tinterval,reltime) 542float4eq(real,real) 543float4ne(real,real) 544float4lt(real,real) 545float4le(real,real) 546float4gt(real,real) 547float4ge(real,real) 548float8eq(double precision,double precision) 549float8ne(double precision,double precision) 550float8lt(double precision,double precision) 551float8le(double precision,double precision) 552float8gt(double precision,double precision) 553float8ge(double precision,double precision) 554float48eq(real,double precision) 555float48ne(real,double precision) 556float48lt(real,double precision) 557float48le(real,double precision) 558float48gt(real,double precision) 559float48ge(real,double precision) 560float84eq(double precision,real) 561float84ne(double precision,real) 562float84lt(double precision,real) 563float84le(double precision,real) 564float84gt(double precision,real) 565float84ge(double precision,real) 566int8eq(bigint,bigint) 567int8ne(bigint,bigint) 568int8lt(bigint,bigint) 569int8gt(bigint,bigint) 570int8le(bigint,bigint) 571int8ge(bigint,bigint) 572int84eq(bigint,integer) 573int84ne(bigint,integer) 574int84lt(bigint,integer) 575int84gt(bigint,integer) 576int84le(bigint,integer) 577int84ge(bigint,integer) 578namelt(name,name) 579namele(name,name) 580namegt(name,name) 581namege(name,name) 582namene(name,name) 583oidlt(oid,oid) 584oidle(oid,oid) 585tintervaleq(tinterval,tinterval) 586tintervalne(tinterval,tinterval) 587tintervallt(tinterval,tinterval) 588tintervalgt(tinterval,tinterval) 589tintervalle(tinterval,tinterval) 590tintervalge(tinterval,tinterval) 591macaddr_eq(macaddr,macaddr) 592macaddr_lt(macaddr,macaddr) 593macaddr_le(macaddr,macaddr) 594macaddr_gt(macaddr,macaddr) 595macaddr_ge(macaddr,macaddr) 596macaddr_ne(macaddr,macaddr) 597int48eq(integer,bigint) 598int48ne(integer,bigint) 599int48lt(integer,bigint) 600int48gt(integer,bigint) 601int48le(integer,bigint) 602int48ge(integer,bigint) 603cash_eq(money,money) 604cash_ne(money,money) 605cash_lt(money,money) 606cash_le(money,money) 607cash_gt(money,money) 608cash_ge(money,money) 609network_eq(inet,inet) 610network_lt(inet,inet) 611network_le(inet,inet) 612network_gt(inet,inet) 613network_ge(inet,inet) 614network_ne(inet,inet) 615lseg_eq(lseg,lseg) 616bpchareq(character,character) 617bpcharne(character,character) 618date_eq(date,date) 619date_lt(date,date) 620date_le(date,date) 621date_gt(date,date) 622date_ge(date,date) 623date_ne(date,date) 624time_lt(time without time zone,time without time zone) 625time_le(time without time zone,time without time zone) 626time_gt(time without time zone,time without time zone) 627time_ge(time without time zone,time without time zone) 628time_ne(time without time zone,time without time zone) 629time_eq(time without time zone,time without time zone) 630timestamptz_eq(timestamp with time zone,timestamp with time zone) 631timestamptz_ne(timestamp with time zone,timestamp with time zone) 632timestamptz_lt(timestamp with time zone,timestamp with time zone) 633timestamptz_le(timestamp with time zone,timestamp with time zone) 634timestamptz_ge(timestamp with time zone,timestamp with time zone) 635timestamptz_gt(timestamp with time zone,timestamp with time zone) 636interval_eq(interval,interval) 637interval_ne(interval,interval) 638interval_lt(interval,interval) 639interval_le(interval,interval) 640interval_ge(interval,interval) 641interval_gt(interval,interval) 642charlt("char","char") 643tidne(tid,tid) 644tideq(tid,tid) 645xideqint4(xid,integer) 646timetz_eq(time with time zone,time with time zone) 647timetz_ne(time with time zone,time with time zone) 648timetz_lt(time with time zone,time with time zone) 649timetz_le(time with time zone,time with time zone) 650timetz_ge(time with time zone,time with time zone) 651timetz_gt(time with time zone,time with time zone) 652circle_eq(circle,circle) 653circle_ne(circle,circle) 654circle_lt(circle,circle) 655circle_gt(circle,circle) 656circle_le(circle,circle) 657circle_ge(circle,circle) 658lseg_ne(lseg,lseg) 659lseg_lt(lseg,lseg) 660lseg_le(lseg,lseg) 661lseg_gt(lseg,lseg) 662lseg_ge(lseg,lseg) 663biteq(bit,bit) 664bitne(bit,bit) 665bitge(bit,bit) 666bitgt(bit,bit) 667bitle(bit,bit) 668bitlt(bit,bit) 669oidgt(oid,oid) 670oidge(oid,oid) 671varbiteq(bit varying,bit varying) 672varbitne(bit varying,bit varying) 673varbitge(bit varying,bit varying) 674varbitgt(bit varying,bit varying) 675varbitle(bit varying,bit varying) 676varbitlt(bit varying,bit varying) 677boolle(boolean,boolean) 678boolge(boolean,boolean) 679int28eq(smallint,bigint) 680int28ne(smallint,bigint) 681int28lt(smallint,bigint) 682int28gt(smallint,bigint) 683int28le(smallint,bigint) 684int28ge(smallint,bigint) 685int82eq(bigint,smallint) 686int82ne(bigint,smallint) 687int82lt(bigint,smallint) 688int82gt(bigint,smallint) 689int82le(bigint,smallint) 690int82ge(bigint,smallint) 691byteaeq(bytea,bytea) 692bytealt(bytea,bytea) 693byteale(bytea,bytea) 694byteagt(bytea,bytea) 695byteage(bytea,bytea) 696byteane(bytea,bytea) 697timestamp_eq(timestamp without time zone,timestamp without time zone) 698timestamp_ne(timestamp without time zone,timestamp without time zone) 699timestamp_lt(timestamp without time zone,timestamp without time zone) 700timestamp_le(timestamp without time zone,timestamp without time zone) 701timestamp_ge(timestamp without time zone,timestamp without time zone) 702timestamp_gt(timestamp without time zone,timestamp without time zone) 703md5(text) 704md5(bytea) 705tidgt(tid,tid) 706tidlt(tid,tid) 707tidge(tid,tid) 708tidle(tid,tid) 709uuid_lt(uuid,uuid) 710uuid_le(uuid,uuid) 711uuid_eq(uuid,uuid) 712uuid_ge(uuid,uuid) 713uuid_gt(uuid,uuid) 714uuid_ne(uuid,uuid) 715xidneq(xid,xid) 716xidneqint4(xid,integer) 717sha224(bytea) 718sha256(bytea) 719sha384(bytea) 720sha512(bytea) 721starts_with(text,text) 722macaddr8_eq(macaddr8,macaddr8) 723macaddr8_lt(macaddr8,macaddr8) 724macaddr8_le(macaddr8,macaddr8) 725macaddr8_gt(macaddr8,macaddr8) 726macaddr8_ge(macaddr8,macaddr8) 727macaddr8_ne(macaddr8,macaddr8) 728-- restore normal output mode 729\a\t 730-- List of functions used by libpq's fe-lobj.c 731-- 732-- If the output of this query changes, you probably broke libpq. 733-- lo_initialize() assumes that there will be at most one match for 734-- each listed name. 735select proname, oid from pg_catalog.pg_proc 736where proname in ( 737 'lo_open', 738 'lo_close', 739 'lo_creat', 740 'lo_create', 741 'lo_unlink', 742 'lo_lseek', 743 'lo_lseek64', 744 'lo_tell', 745 'lo_tell64', 746 'lo_truncate', 747 'lo_truncate64', 748 'loread', 749 'lowrite') 750and pronamespace = (select oid from pg_catalog.pg_namespace 751 where nspname = 'pg_catalog') 752order by 1; 753 proname | oid 754---------------+------ 755 lo_close | 953 756 lo_creat | 957 757 lo_create | 715 758 lo_lseek | 956 759 lo_lseek64 | 3170 760 lo_open | 952 761 lo_tell | 958 762 lo_tell64 | 3171 763 lo_truncate | 1004 764 lo_truncate64 | 3172 765 lo_unlink | 964 766 loread | 954 767 lowrite | 955 768(13 rows) 769 770-- Check that all immutable functions are marked parallel safe 771SELECT p1.oid, p1.proname 772FROM pg_proc AS p1 773WHERE provolatile = 'i' AND proparallel = 'u'; 774 oid | proname 775-----+--------- 776(0 rows) 777 778-- **************** pg_cast **************** 779-- Catch bogus values in pg_cast columns (other than cases detected by 780-- oidjoins test). 781SELECT * 782FROM pg_cast c 783WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i') 784 OR castmethod NOT IN ('f', 'b' ,'i'); 785 castsource | casttarget | castfunc | castcontext | castmethod 786------------+------------+----------+-------------+------------ 787(0 rows) 788 789-- Check that castfunc is nonzero only for cast methods that need a function, 790-- and zero otherwise 791SELECT * 792FROM pg_cast c 793WHERE (castmethod = 'f' AND castfunc = 0) 794 OR (castmethod IN ('b', 'i') AND castfunc <> 0); 795 castsource | casttarget | castfunc | castcontext | castmethod 796------------+------------+----------+-------------+------------ 797(0 rows) 798 799-- Look for casts to/from the same type that aren't length coercion functions. 800-- (We assume they are length coercions if they take multiple arguments.) 801-- Such entries are not necessarily harmful, but they are useless. 802SELECT * 803FROM pg_cast c 804WHERE castsource = casttarget AND castfunc = 0; 805 castsource | casttarget | castfunc | castcontext | castmethod 806------------+------------+----------+-------------+------------ 807(0 rows) 808 809SELECT c.* 810FROM pg_cast c, pg_proc p 811WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget; 812 castsource | casttarget | castfunc | castcontext | castmethod 813------------+------------+----------+-------------+------------ 814(0 rows) 815 816-- Look for cast functions that don't have the right signature. The 817-- argument and result types in pg_proc must be the same as, or binary 818-- compatible with, what it says in pg_cast. 819-- As a special case, we allow casts from CHAR(n) that use functions 820-- declared to take TEXT. This does not pass the binary-coercibility test 821-- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results 822-- are the same, so long as the function is one that ignores trailing blanks. 823SELECT c.* 824FROM pg_cast c, pg_proc p 825WHERE c.castfunc = p.oid AND 826 (p.pronargs < 1 OR p.pronargs > 3 827 OR NOT (binary_coercible(c.castsource, p.proargtypes[0]) 828 OR (c.castsource = 'character'::regtype AND 829 p.proargtypes[0] = 'text'::regtype)) 830 OR NOT binary_coercible(p.prorettype, c.casttarget)); 831 castsource | casttarget | castfunc | castcontext | castmethod 832------------+------------+----------+-------------+------------ 833(0 rows) 834 835SELECT c.* 836FROM pg_cast c, pg_proc p 837WHERE c.castfunc = p.oid AND 838 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR 839 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype)); 840 castsource | casttarget | castfunc | castcontext | castmethod 841------------+------------+----------+-------------+------------ 842(0 rows) 843 844-- Look for binary compatible casts that do not have the reverse 845-- direction registered as well, or where the reverse direction is not 846-- also binary compatible. This is legal, but usually not intended. 847-- As of 7.4, this finds the casts from text and varchar to bpchar, because 848-- those are binary-compatible while the reverse way goes through rtrim(). 849-- As of 8.2, this finds the cast from cidr to inet, because that is a 850-- trivial binary coercion while the other way goes through inet_to_cidr(). 851-- As of 8.3, this finds the casts from xml to text, varchar, and bpchar, 852-- because those are binary-compatible while the reverse goes through 853-- texttoxml(), which does an XML syntax check. 854-- As of 9.1, this finds the cast from pg_node_tree to text, which we 855-- intentionally do not provide a reverse pathway for. 856SELECT castsource::regtype, casttarget::regtype, castfunc, castcontext 857FROM pg_cast c 858WHERE c.castmethod = 'b' AND 859 NOT EXISTS (SELECT 1 FROM pg_cast k 860 WHERE k.castmethod = 'b' AND 861 k.castsource = c.casttarget AND 862 k.casttarget = c.castsource); 863 castsource | casttarget | castfunc | castcontext 864-------------------+-------------------+----------+------------- 865 text | character | 0 | i 866 character varying | character | 0 | i 867 pg_node_tree | text | 0 | i 868 pg_ndistinct | bytea | 0 | i 869 pg_dependencies | bytea | 0 | i 870 cidr | inet | 0 | i 871 xml | text | 0 | a 872 xml | character varying | 0 | a 873 xml | character | 0 | a 874(9 rows) 875 876-- **************** pg_conversion **************** 877-- Look for illegal values in pg_conversion fields. 878SELECT p1.oid, p1.conname 879FROM pg_conversion as p1 880WHERE p1.conproc = 0 OR 881 pg_encoding_to_char(conforencoding) = '' OR 882 pg_encoding_to_char(contoencoding) = ''; 883 oid | conname 884-----+--------- 885(0 rows) 886 887-- Look for conprocs that don't have the expected signature. 888SELECT p.oid, p.proname, c.oid, c.conname 889FROM pg_proc p, pg_conversion c 890WHERE p.oid = c.conproc AND 891 (p.prorettype != 'void'::regtype OR p.proretset OR 892 p.pronargs != 5 OR 893 p.proargtypes[0] != 'int4'::regtype OR 894 p.proargtypes[1] != 'int4'::regtype OR 895 p.proargtypes[2] != 'cstring'::regtype OR 896 p.proargtypes[3] != 'internal'::regtype OR 897 p.proargtypes[4] != 'int4'::regtype); 898 oid | proname | oid | conname 899-----+---------+-----+--------- 900(0 rows) 901 902-- Check for conprocs that don't perform the specific conversion that 903-- pg_conversion alleges they do, by trying to invoke each conversion 904-- on some simple ASCII data. (The conproc should throw an error if 905-- it doesn't accept the encodings that are passed to it.) 906-- Unfortunately, we can't test non-default conprocs this way, because 907-- there is no way to ask convert() to invoke them, and we cannot call 908-- them directly from SQL. But there are no non-default built-in 909-- conversions anyway. 910-- (Similarly, this doesn't cope with any search path issues.) 911SELECT p1.oid, p1.conname 912FROM pg_conversion as p1 913WHERE condefault AND 914 convert('ABC'::bytea, pg_encoding_to_char(conforencoding), 915 pg_encoding_to_char(contoencoding)) != 'ABC'; 916 oid | conname 917-----+--------- 918(0 rows) 919 920-- **************** pg_operator **************** 921-- Look for illegal values in pg_operator fields. 922SELECT p1.oid, p1.oprname 923FROM pg_operator as p1 924WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR 925 p1.oprresult = 0 OR p1.oprcode = 0; 926 oid | oprname 927-----+--------- 928(0 rows) 929 930-- Look for missing or unwanted operand types 931SELECT p1.oid, p1.oprname 932FROM pg_operator as p1 933WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR 934 (p1.oprleft != 0 and p1.oprkind = 'l') OR 935 (p1.oprright = 0 and p1.oprkind != 'r') OR 936 (p1.oprright != 0 and p1.oprkind = 'r'); 937 oid | oprname 938-----+--------- 939(0 rows) 940 941-- Look for conflicting operator definitions (same names and input datatypes). 942SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode 943FROM pg_operator AS p1, pg_operator AS p2 944WHERE p1.oid != p2.oid AND 945 p1.oprname = p2.oprname AND 946 p1.oprkind = p2.oprkind AND 947 p1.oprleft = p2.oprleft AND 948 p1.oprright = p2.oprright; 949 oid | oprcode | oid | oprcode 950-----+---------+-----+--------- 951(0 rows) 952 953-- Look for commutative operators that don't commute. 954-- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x. 955-- We expect that B will always say that B.oprcom = A as well; that's not 956-- inherently essential, but it would be inefficient not to mark it so. 957SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode 958FROM pg_operator AS p1, pg_operator AS p2 959WHERE p1.oprcom = p2.oid AND 960 (p1.oprkind != 'b' OR 961 p1.oprleft != p2.oprright OR 962 p1.oprright != p2.oprleft OR 963 p1.oprresult != p2.oprresult OR 964 p1.oid != p2.oprcom); 965 oid | oprcode | oid | oprcode 966-----+---------+-----+--------- 967(0 rows) 968 969-- Look for negatory operators that don't agree. 970-- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield 971-- boolean results, and (x A y) == ! (x B y), or the equivalent for 972-- single-operand operators. 973-- We expect that B will always say that B.oprnegate = A as well; that's not 974-- inherently essential, but it would be inefficient not to mark it so. 975-- Also, A and B had better not be the same operator. 976SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode 977FROM pg_operator AS p1, pg_operator AS p2 978WHERE p1.oprnegate = p2.oid AND 979 (p1.oprkind != p2.oprkind OR 980 p1.oprleft != p2.oprleft OR 981 p1.oprright != p2.oprright OR 982 p1.oprresult != 'bool'::regtype OR 983 p2.oprresult != 'bool'::regtype OR 984 p1.oid != p2.oprnegate OR 985 p1.oid = p2.oid); 986 oid | oprcode | oid | oprcode 987-----+---------+-----+--------- 988(0 rows) 989 990-- Make a list of the names of operators that are claimed to be commutator 991-- pairs. This list will grow over time, but before accepting a new entry 992-- make sure you didn't link the wrong operators. 993SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2 994FROM pg_operator o1, pg_operator o2 995WHERE o1.oprcom = o2.oid AND o1.oprname <= o2.oprname 996ORDER BY 1, 2; 997 op1 | op2 998------+------ 999 # | # 1000 & | & 1001 && | && 1002 * | * 1003 *< | *> 1004 *<= | *>= 1005 *<> | *<> 1006 *= | *= 1007 + | + 1008 -|- | -|- 1009 < | > 1010 <-> | <-> 1011 << | >> 1012 <<= | >>= 1013 <= | >= 1014 <> | <> 1015 <@ | @> 1016 = | = 1017 ?# | ?# 1018 ?- | ?- 1019 ?-| | ?-| 1020 ?| | ?| 1021 ?|| | ?|| 1022 @ | ~ 1023 @@ | @@ 1024 @@@ | @@@ 1025 | | | 1026 ~<=~ | ~>=~ 1027 ~<~ | ~>~ 1028 ~= | ~= 1029(30 rows) 1030 1031-- Likewise for negator pairs. 1032SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2 1033FROM pg_operator o1, pg_operator o2 1034WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname 1035ORDER BY 1, 2; 1036 op1 | op2 1037------+------ 1038 !~ | ~ 1039 !~* | ~* 1040 !~~ | ~~ 1041 !~~* | ~~* 1042 #< | #>= 1043 #<= | #> 1044 #<> | #= 1045 *< | *>= 1046 *<= | *> 1047 *<> | *= 1048 < | >= 1049 <= | > 1050 <> | = 1051 <> | ~= 1052 ~<=~ | ~>~ 1053 ~<~ | ~>=~ 1054(16 rows) 1055 1056-- A mergejoinable or hashjoinable operator must be binary, must return 1057-- boolean, and must have a commutator (itself, unless it's a cross-type 1058-- operator). 1059SELECT p1.oid, p1.oprname FROM pg_operator AS p1 1060WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT 1061 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0); 1062 oid | oprname 1063-----+--------- 1064(0 rows) 1065 1066-- What's more, the commutator had better be mergejoinable/hashjoinable too. 1067SELECT p1.oid, p1.oprname, p2.oid, p2.oprname 1068FROM pg_operator AS p1, pg_operator AS p2 1069WHERE p1.oprcom = p2.oid AND 1070 (p1.oprcanmerge != p2.oprcanmerge OR 1071 p1.oprcanhash != p2.oprcanhash); 1072 oid | oprname | oid | oprname 1073-----+---------+-----+--------- 1074(0 rows) 1075 1076-- Mergejoinable operators should appear as equality members of btree index 1077-- opfamilies. 1078SELECT p1.oid, p1.oprname 1079FROM pg_operator AS p1 1080WHERE p1.oprcanmerge AND NOT EXISTS 1081 (SELECT 1 FROM pg_amop 1082 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND 1083 amopopr = p1.oid AND amopstrategy = 3); 1084 oid | oprname 1085-----+--------- 1086(0 rows) 1087 1088-- And the converse. 1089SELECT p1.oid, p1.oprname, p.amopfamily 1090FROM pg_operator AS p1, pg_amop p 1091WHERE amopopr = p1.oid 1092 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') 1093 AND amopstrategy = 3 1094 AND NOT p1.oprcanmerge; 1095 oid | oprname | amopfamily 1096-----+---------+------------ 1097(0 rows) 1098 1099-- Hashable operators should appear as members of hash index opfamilies. 1100SELECT p1.oid, p1.oprname 1101FROM pg_operator AS p1 1102WHERE p1.oprcanhash AND NOT EXISTS 1103 (SELECT 1 FROM pg_amop 1104 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND 1105 amopopr = p1.oid AND amopstrategy = 1); 1106 oid | oprname 1107-----+--------- 1108(0 rows) 1109 1110-- And the converse. 1111SELECT p1.oid, p1.oprname, p.amopfamily 1112FROM pg_operator AS p1, pg_amop p 1113WHERE amopopr = p1.oid 1114 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') 1115 AND NOT p1.oprcanhash; 1116 oid | oprname | amopfamily 1117-----+---------+------------ 1118(0 rows) 1119 1120-- Check that each operator defined in pg_operator matches its oprcode entry 1121-- in pg_proc. Easiest to do this separately for each oprkind. 1122SELECT p1.oid, p1.oprname, p2.oid, p2.proname 1123FROM pg_operator AS p1, pg_proc AS p2 1124WHERE p1.oprcode = p2.oid AND 1125 p1.oprkind = 'b' AND 1126 (p2.pronargs != 2 1127 OR NOT binary_coercible(p2.prorettype, p1.oprresult) 1128 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) 1129 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1])); 1130 oid | oprname | oid | proname 1131-----+---------+-----+--------- 1132(0 rows) 1133 1134SELECT p1.oid, p1.oprname, p2.oid, p2.proname 1135FROM pg_operator AS p1, pg_proc AS p2 1136WHERE p1.oprcode = p2.oid AND 1137 p1.oprkind = 'l' AND 1138 (p2.pronargs != 1 1139 OR NOT binary_coercible(p2.prorettype, p1.oprresult) 1140 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0]) 1141 OR p1.oprleft != 0); 1142 oid | oprname | oid | proname 1143-----+---------+-----+--------- 1144(0 rows) 1145 1146SELECT p1.oid, p1.oprname, p2.oid, p2.proname 1147FROM pg_operator AS p1, pg_proc AS p2 1148WHERE p1.oprcode = p2.oid AND 1149 p1.oprkind = 'r' AND 1150 (p2.pronargs != 1 1151 OR NOT binary_coercible(p2.prorettype, p1.oprresult) 1152 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) 1153 OR p1.oprright != 0); 1154 oid | oprname | oid | proname 1155-----+---------+-----+--------- 1156(0 rows) 1157 1158-- If the operator is mergejoinable or hashjoinable, its underlying function 1159-- should not be volatile. 1160SELECT p1.oid, p1.oprname, p2.oid, p2.proname 1161FROM pg_operator AS p1, pg_proc AS p2 1162WHERE p1.oprcode = p2.oid AND 1163 (p1.oprcanmerge OR p1.oprcanhash) AND 1164 p2.provolatile = 'v'; 1165 oid | oprname | oid | proname 1166-----+---------+-----+--------- 1167(0 rows) 1168 1169-- If oprrest is set, the operator must return boolean, 1170-- and it must link to a proc with the right signature 1171-- to be a restriction selectivity estimator. 1172-- The proc signature we want is: float8 proc(internal, oid, internal, int4) 1173SELECT p1.oid, p1.oprname, p2.oid, p2.proname 1174FROM pg_operator AS p1, pg_proc AS p2 1175WHERE p1.oprrest = p2.oid AND 1176 (p1.oprresult != 'bool'::regtype OR 1177 p2.prorettype != 'float8'::regtype OR p2.proretset OR 1178 p2.pronargs != 4 OR 1179 p2.proargtypes[0] != 'internal'::regtype OR 1180 p2.proargtypes[1] != 'oid'::regtype OR 1181 p2.proargtypes[2] != 'internal'::regtype OR 1182 p2.proargtypes[3] != 'int4'::regtype); 1183 oid | oprname | oid | proname 1184-----+---------+-----+--------- 1185(0 rows) 1186 1187-- If oprjoin is set, the operator must be a binary boolean op, 1188-- and it must link to a proc with the right signature 1189-- to be a join selectivity estimator. 1190-- The proc signature we want is: float8 proc(internal, oid, internal, int2, internal) 1191-- (Note: the old signature with only 4 args is still allowed, but no core 1192-- estimator should be using it.) 1193SELECT p1.oid, p1.oprname, p2.oid, p2.proname 1194FROM pg_operator AS p1, pg_proc AS p2 1195WHERE p1.oprjoin = p2.oid AND 1196 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR 1197 p2.prorettype != 'float8'::regtype OR p2.proretset OR 1198 p2.pronargs != 5 OR 1199 p2.proargtypes[0] != 'internal'::regtype OR 1200 p2.proargtypes[1] != 'oid'::regtype OR 1201 p2.proargtypes[2] != 'internal'::regtype OR 1202 p2.proargtypes[3] != 'int2'::regtype OR 1203 p2.proargtypes[4] != 'internal'::regtype); 1204 oid | oprname | oid | proname 1205-----+---------+-----+--------- 1206(0 rows) 1207 1208-- Insist that all built-in pg_operator entries have descriptions 1209SELECT p1.oid, p1.oprname 1210FROM pg_operator as p1 LEFT JOIN pg_description as d 1211 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0 1212WHERE d.classoid IS NULL AND p1.oid <= 9999; 1213 oid | oprname 1214-----+--------- 1215(0 rows) 1216 1217-- Check that operators' underlying functions have suitable comments, 1218-- namely 'implementation of XXX operator'. (Note: it's not necessary to 1219-- put such comments into pg_proc.h; initdb will generate them as needed.) 1220-- In some cases involving legacy names for operators, there are multiple 1221-- operators referencing the same pg_proc entry, so ignore operators whose 1222-- comments say they are deprecated. 1223-- We also have a few functions that are both operator support and meant to 1224-- be called directly; those should have comments matching their operator. 1225WITH funcdescs AS ( 1226 SELECT p.oid as p_oid, proname, o.oid as o_oid, 1227 pd.description as prodesc, 1228 'implementation of ' || oprname || ' operator' as expecteddesc, 1229 od.description as oprdesc 1230 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid 1231 LEFT JOIN pg_description pd ON 1232 (pd.objoid = p.oid and pd.classoid = p.tableoid and pd.objsubid = 0) 1233 LEFT JOIN pg_description od ON 1234 (od.objoid = o.oid and od.classoid = o.tableoid and od.objsubid = 0) 1235 WHERE o.oid <= 9999 1236) 1237SELECT * FROM funcdescs 1238 WHERE prodesc IS DISTINCT FROM expecteddesc 1239 AND oprdesc NOT LIKE 'deprecated%' 1240 AND prodesc IS DISTINCT FROM oprdesc; 1241 p_oid | proname | o_oid | prodesc | expecteddesc | oprdesc 1242-------+---------+-------+---------+--------------+--------- 1243(0 rows) 1244 1245-- Show all the operator-implementation functions that have their own 1246-- comments. This should happen only in cases where the function and 1247-- operator syntaxes are both documented at the user level. 1248-- This should be a pretty short list; it's mostly legacy cases. 1249WITH funcdescs AS ( 1250 SELECT p.oid as p_oid, proname, o.oid as o_oid, 1251 pd.description as prodesc, 1252 'implementation of ' || oprname || ' operator' as expecteddesc, 1253 od.description as oprdesc 1254 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid 1255 LEFT JOIN pg_description pd ON 1256 (pd.objoid = p.oid and pd.classoid = p.tableoid and pd.objsubid = 0) 1257 LEFT JOIN pg_description od ON 1258 (od.objoid = o.oid and od.classoid = o.tableoid and od.objsubid = 0) 1259 WHERE o.oid <= 9999 1260) 1261SELECT p_oid, proname, prodesc FROM funcdescs 1262 WHERE prodesc IS DISTINCT FROM expecteddesc 1263 AND oprdesc NOT LIKE 'deprecated%' 1264ORDER BY 1; 1265 p_oid | proname | prodesc 1266-------+-------------------------+------------------------------------------------- 1267 378 | array_append | append element onto end of array 1268 379 | array_prepend | prepend element onto front of array 1269 1035 | aclinsert | add/update ACL item 1270 1036 | aclremove | remove ACL item 1271 1037 | aclcontains | contains 1272 3217 | jsonb_extract_path | get value from jsonb with path elements 1273 3940 | jsonb_extract_path_text | get value from jsonb as text with path elements 1274 3951 | json_extract_path | get value from json with path elements 1275 3953 | json_extract_path_text | get value from json as text with path elements 1276(9 rows) 1277 1278-- **************** pg_aggregate **************** 1279-- Look for illegal values in pg_aggregate fields. 1280SELECT ctid, aggfnoid::oid 1281FROM pg_aggregate as p1 1282WHERE aggfnoid = 0 OR aggtransfn = 0 OR 1283 aggkind NOT IN ('n', 'o', 'h') OR 1284 aggnumdirectargs < 0 OR 1285 (aggkind = 'n' AND aggnumdirectargs > 0) OR 1286 aggfinalmodify NOT IN ('r', 's', 'w') OR 1287 aggmfinalmodify NOT IN ('r', 's', 'w') OR 1288 aggtranstype = 0 OR aggtransspace < 0 OR aggmtransspace < 0; 1289 ctid | aggfnoid 1290------+---------- 1291(0 rows) 1292 1293-- Make sure the matching pg_proc entry is sensible, too. 1294SELECT a.aggfnoid::oid, p.proname 1295FROM pg_aggregate as a, pg_proc as p 1296WHERE a.aggfnoid = p.oid AND 1297 (p.prokind != 'a' OR p.proretset OR p.pronargs < a.aggnumdirectargs); 1298 aggfnoid | proname 1299----------+--------- 1300(0 rows) 1301 1302-- Make sure there are no prokind = PROKIND_AGGREGATE pg_proc entries without matches. 1303SELECT oid, proname 1304FROM pg_proc as p 1305WHERE p.prokind = 'a' AND 1306 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid); 1307 oid | proname 1308-----+--------- 1309(0 rows) 1310 1311-- If there is no finalfn then the output type must be the transtype. 1312SELECT a.aggfnoid::oid, p.proname 1313FROM pg_aggregate as a, pg_proc as p 1314WHERE a.aggfnoid = p.oid AND 1315 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype; 1316 aggfnoid | proname 1317----------+--------- 1318(0 rows) 1319 1320-- Cross-check transfn against its entry in pg_proc. 1321-- NOTE: use physically_coercible here, not binary_coercible, because 1322-- max and min on abstime are implemented using int4larger/int4smaller. 1323SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 1324FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 1325WHERE a.aggfnoid = p.oid AND 1326 a.aggtransfn = ptr.oid AND 1327 (ptr.proretset 1328 OR NOT (ptr.pronargs = 1329 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1 1330 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END) 1331 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype) 1332 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0]) 1333 OR (p.pronargs > 0 AND 1334 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) 1335 OR (p.pronargs > 1 AND 1336 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2])) 1337 OR (p.pronargs > 2 AND 1338 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3])) 1339 -- we could carry the check further, but 3 args is enough for now 1340 ); 1341 aggfnoid | proname | oid | proname 1342----------+---------+-----+--------- 1343(0 rows) 1344 1345-- Cross-check finalfn (if present) against its entry in pg_proc. 1346SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname 1347FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn 1348WHERE a.aggfnoid = p.oid AND 1349 a.aggfinalfn = pfn.oid AND 1350 (pfn.proretset OR 1351 NOT binary_coercible(pfn.prorettype, p.prorettype) OR 1352 NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]) OR 1353 CASE WHEN a.aggfinalextra THEN pfn.pronargs != p.pronargs + 1 1354 ELSE pfn.pronargs != a.aggnumdirectargs + 1 END 1355 OR (pfn.pronargs > 1 AND 1356 NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1])) 1357 OR (pfn.pronargs > 2 AND 1358 NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2])) 1359 OR (pfn.pronargs > 3 AND 1360 NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3])) 1361 -- we could carry the check further, but 3 args is enough for now 1362 ); 1363 aggfnoid | proname | oid | proname 1364----------+---------+-----+--------- 1365(0 rows) 1366 1367-- If transfn is strict then either initval should be non-NULL, or 1368-- input type should match transtype so that the first non-null input 1369-- can be assigned as the state value. 1370SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 1371FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 1372WHERE a.aggfnoid = p.oid AND 1373 a.aggtransfn = ptr.oid AND ptr.proisstrict AND 1374 a.agginitval IS NULL AND 1375 NOT binary_coercible(p.proargtypes[0], a.aggtranstype); 1376 aggfnoid | proname | oid | proname 1377----------+---------+-----+--------- 1378(0 rows) 1379 1380-- Check for inconsistent specifications of moving-aggregate columns. 1381SELECT ctid, aggfnoid::oid 1382FROM pg_aggregate as p1 1383WHERE aggmtranstype != 0 AND 1384 (aggmtransfn = 0 OR aggminvtransfn = 0); 1385 ctid | aggfnoid 1386------+---------- 1387(0 rows) 1388 1389SELECT ctid, aggfnoid::oid 1390FROM pg_aggregate as p1 1391WHERE aggmtranstype = 0 AND 1392 (aggmtransfn != 0 OR aggminvtransfn != 0 OR aggmfinalfn != 0 OR 1393 aggmtransspace != 0 OR aggminitval IS NOT NULL); 1394 ctid | aggfnoid 1395------+---------- 1396(0 rows) 1397 1398-- If there is no mfinalfn then the output type must be the mtranstype. 1399SELECT a.aggfnoid::oid, p.proname 1400FROM pg_aggregate as a, pg_proc as p 1401WHERE a.aggfnoid = p.oid AND 1402 a.aggmtransfn != 0 AND 1403 a.aggmfinalfn = 0 AND p.prorettype != a.aggmtranstype; 1404 aggfnoid | proname 1405----------+--------- 1406(0 rows) 1407 1408-- Cross-check mtransfn (if present) against its entry in pg_proc. 1409SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 1410FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 1411WHERE a.aggfnoid = p.oid AND 1412 a.aggmtransfn = ptr.oid AND 1413 (ptr.proretset 1414 OR NOT (ptr.pronargs = 1415 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1 1416 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END) 1417 OR NOT physically_coercible(ptr.prorettype, a.aggmtranstype) 1418 OR NOT physically_coercible(a.aggmtranstype, ptr.proargtypes[0]) 1419 OR (p.pronargs > 0 AND 1420 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) 1421 OR (p.pronargs > 1 AND 1422 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2])) 1423 OR (p.pronargs > 2 AND 1424 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3])) 1425 -- we could carry the check further, but 3 args is enough for now 1426 ); 1427 aggfnoid | proname | oid | proname 1428----------+---------+-----+--------- 1429(0 rows) 1430 1431-- Cross-check minvtransfn (if present) against its entry in pg_proc. 1432SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 1433FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 1434WHERE a.aggfnoid = p.oid AND 1435 a.aggminvtransfn = ptr.oid AND 1436 (ptr.proretset 1437 OR NOT (ptr.pronargs = 1438 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1 1439 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END) 1440 OR NOT physically_coercible(ptr.prorettype, a.aggmtranstype) 1441 OR NOT physically_coercible(a.aggmtranstype, ptr.proargtypes[0]) 1442 OR (p.pronargs > 0 AND 1443 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) 1444 OR (p.pronargs > 1 AND 1445 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2])) 1446 OR (p.pronargs > 2 AND 1447 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3])) 1448 -- we could carry the check further, but 3 args is enough for now 1449 ); 1450 aggfnoid | proname | oid | proname 1451----------+---------+-----+--------- 1452(0 rows) 1453 1454-- Cross-check mfinalfn (if present) against its entry in pg_proc. 1455SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname 1456FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn 1457WHERE a.aggfnoid = p.oid AND 1458 a.aggmfinalfn = pfn.oid AND 1459 (pfn.proretset OR 1460 NOT binary_coercible(pfn.prorettype, p.prorettype) OR 1461 NOT binary_coercible(a.aggmtranstype, pfn.proargtypes[0]) OR 1462 CASE WHEN a.aggmfinalextra THEN pfn.pronargs != p.pronargs + 1 1463 ELSE pfn.pronargs != a.aggnumdirectargs + 1 END 1464 OR (pfn.pronargs > 1 AND 1465 NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1])) 1466 OR (pfn.pronargs > 2 AND 1467 NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2])) 1468 OR (pfn.pronargs > 3 AND 1469 NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3])) 1470 -- we could carry the check further, but 3 args is enough for now 1471 ); 1472 aggfnoid | proname | oid | proname 1473----------+---------+-----+--------- 1474(0 rows) 1475 1476-- If mtransfn is strict then either minitval should be non-NULL, or 1477-- input type should match mtranstype so that the first non-null input 1478-- can be assigned as the state value. 1479SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname 1480FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr 1481WHERE a.aggfnoid = p.oid AND 1482 a.aggmtransfn = ptr.oid AND ptr.proisstrict AND 1483 a.aggminitval IS NULL AND 1484 NOT binary_coercible(p.proargtypes[0], a.aggmtranstype); 1485 aggfnoid | proname | oid | proname 1486----------+---------+-----+--------- 1487(0 rows) 1488 1489-- mtransfn and minvtransfn should have same strictness setting. 1490SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname, iptr.oid, iptr.proname 1491FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr, pg_proc AS iptr 1492WHERE a.aggfnoid = p.oid AND 1493 a.aggmtransfn = ptr.oid AND 1494 a.aggminvtransfn = iptr.oid AND 1495 ptr.proisstrict != iptr.proisstrict; 1496 aggfnoid | proname | oid | proname | oid | proname 1497----------+---------+-----+---------+-----+--------- 1498(0 rows) 1499 1500-- Check that all combine functions have signature 1501-- combine(transtype, transtype) returns transtype 1502-- NOTE: use physically_coercible here, not binary_coercible, because 1503-- max and min on abstime are implemented using int4larger/int4smaller. 1504SELECT a.aggfnoid, p.proname 1505FROM pg_aggregate as a, pg_proc as p 1506WHERE a.aggcombinefn = p.oid AND 1507 (p.pronargs != 2 OR 1508 p.prorettype != p.proargtypes[0] OR 1509 p.prorettype != p.proargtypes[1] OR 1510 NOT physically_coercible(a.aggtranstype, p.proargtypes[0])); 1511 aggfnoid | proname 1512----------+--------- 1513(0 rows) 1514 1515-- Check that no combine function for an INTERNAL transtype is strict. 1516SELECT a.aggfnoid, p.proname 1517FROM pg_aggregate as a, pg_proc as p 1518WHERE a.aggcombinefn = p.oid AND 1519 a.aggtranstype = 'internal'::regtype AND p.proisstrict; 1520 aggfnoid | proname 1521----------+--------- 1522(0 rows) 1523 1524-- serialize/deserialize functions should be specified only for aggregates 1525-- with transtype internal and a combine function, and we should have both 1526-- or neither of them. 1527SELECT aggfnoid, aggtranstype, aggserialfn, aggdeserialfn 1528FROM pg_aggregate 1529WHERE (aggserialfn != 0 OR aggdeserialfn != 0) 1530 AND (aggtranstype != 'internal'::regtype OR aggcombinefn = 0 OR 1531 aggserialfn = 0 OR aggdeserialfn = 0); 1532 aggfnoid | aggtranstype | aggserialfn | aggdeserialfn 1533----------+--------------+-------------+--------------- 1534(0 rows) 1535 1536-- Check that all serialization functions have signature 1537-- serialize(internal) returns bytea 1538-- Also insist that they be strict; it's wasteful to run them on NULLs. 1539SELECT a.aggfnoid, p.proname 1540FROM pg_aggregate as a, pg_proc as p 1541WHERE a.aggserialfn = p.oid AND 1542 (p.prorettype != 'bytea'::regtype OR p.pronargs != 1 OR 1543 p.proargtypes[0] != 'internal'::regtype OR 1544 NOT p.proisstrict); 1545 aggfnoid | proname 1546----------+--------- 1547(0 rows) 1548 1549-- Check that all deserialization functions have signature 1550-- deserialize(bytea, internal) returns internal 1551-- Also insist that they be strict; it's wasteful to run them on NULLs. 1552SELECT a.aggfnoid, p.proname 1553FROM pg_aggregate as a, pg_proc as p 1554WHERE a.aggdeserialfn = p.oid AND 1555 (p.prorettype != 'internal'::regtype OR p.pronargs != 2 OR 1556 p.proargtypes[0] != 'bytea'::regtype OR 1557 p.proargtypes[1] != 'internal'::regtype OR 1558 NOT p.proisstrict); 1559 aggfnoid | proname 1560----------+--------- 1561(0 rows) 1562 1563-- Check that aggregates which have the same transition function also have 1564-- the same combine, serialization, and deserialization functions. 1565-- While that isn't strictly necessary, it's fishy if they don't. 1566SELECT a.aggfnoid, a.aggcombinefn, a.aggserialfn, a.aggdeserialfn, 1567 b.aggfnoid, b.aggcombinefn, b.aggserialfn, b.aggdeserialfn 1568FROM 1569 pg_aggregate a, pg_aggregate b 1570WHERE 1571 a.aggfnoid < b.aggfnoid AND a.aggtransfn = b.aggtransfn AND 1572 (a.aggcombinefn != b.aggcombinefn OR a.aggserialfn != b.aggserialfn 1573 OR a.aggdeserialfn != b.aggdeserialfn); 1574 aggfnoid | aggcombinefn | aggserialfn | aggdeserialfn | aggfnoid | aggcombinefn | aggserialfn | aggdeserialfn 1575----------+--------------+-------------+---------------+----------+--------------+-------------+--------------- 1576(0 rows) 1577 1578-- Cross-check aggsortop (if present) against pg_operator. 1579-- We expect to find entries for bool_and, bool_or, every, max, and min. 1580SELECT DISTINCT proname, oprname 1581FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p 1582WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid 1583ORDER BY 1, 2; 1584 proname | oprname 1585----------+--------- 1586 bool_and | < 1587 bool_or | > 1588 every | < 1589 max | > 1590 min | < 1591(5 rows) 1592 1593-- Check datatypes match 1594SELECT a.aggfnoid::oid, o.oid 1595FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p 1596WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND 1597 (oprkind != 'b' OR oprresult != 'boolean'::regtype 1598 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]); 1599 aggfnoid | oid 1600----------+----- 1601(0 rows) 1602 1603-- Check operator is a suitable btree opfamily member 1604SELECT a.aggfnoid::oid, o.oid 1605FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p 1606WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND 1607 NOT EXISTS(SELECT 1 FROM pg_amop 1608 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') 1609 AND amopopr = o.oid 1610 AND amoplefttype = o.oprleft 1611 AND amoprighttype = o.oprright); 1612 aggfnoid | oid 1613----------+----- 1614(0 rows) 1615 1616-- Check correspondence of btree strategies and names 1617SELECT DISTINCT proname, oprname, amopstrategy 1618FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p, 1619 pg_amop as ao 1620WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND 1621 amopopr = o.oid AND 1622 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') 1623ORDER BY 1, 2; 1624 proname | oprname | amopstrategy 1625----------+---------+-------------- 1626 bool_and | < | 1 1627 bool_or | > | 5 1628 every | < | 1 1629 max | > | 5 1630 min | < | 1 1631(5 rows) 1632 1633-- Check that there are not aggregates with the same name and different 1634-- numbers of arguments. While not technically wrong, we have a project policy 1635-- to avoid this because it opens the door for confusion in connection with 1636-- ORDER BY: novices frequently put the ORDER BY in the wrong place. 1637-- See the fate of the single-argument form of string_agg() for history. 1638-- (Note: we don't forbid users from creating such aggregates; the policy is 1639-- just to think twice before creating built-in aggregates like this.) 1640-- The only aggregates that should show up here are count(x) and count(*). 1641SELECT p1.oid::regprocedure, p2.oid::regprocedure 1642FROM pg_proc AS p1, pg_proc AS p2 1643WHERE p1.oid < p2.oid AND p1.proname = p2.proname AND 1644 p1.prokind = 'a' AND p2.prokind = 'a' AND 1645 array_dims(p1.proargtypes) != array_dims(p2.proargtypes) 1646ORDER BY 1; 1647 oid | oid 1648--------------+--------- 1649 count("any") | count() 1650(1 row) 1651 1652-- For the same reason, built-in aggregates with default arguments are no good. 1653SELECT oid, proname 1654FROM pg_proc AS p 1655WHERE prokind = 'a' AND proargdefaults IS NOT NULL; 1656 oid | proname 1657-----+--------- 1658(0 rows) 1659 1660-- For the same reason, we avoid creating built-in variadic aggregates, except 1661-- that variadic ordered-set aggregates are OK (since they have special syntax 1662-- that is not subject to the misplaced ORDER BY issue). 1663SELECT p.oid, proname 1664FROM pg_proc AS p JOIN pg_aggregate AS a ON a.aggfnoid = p.oid 1665WHERE prokind = 'a' AND provariadic != 0 AND a.aggkind = 'n'; 1666 oid | proname 1667-----+--------- 1668(0 rows) 1669 1670-- **************** pg_opfamily **************** 1671-- Look for illegal values in pg_opfamily fields 1672SELECT p1.oid 1673FROM pg_opfamily as p1 1674WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0; 1675 oid 1676----- 1677(0 rows) 1678 1679-- **************** pg_opclass **************** 1680-- Look for illegal values in pg_opclass fields 1681SELECT p1.oid 1682FROM pg_opclass AS p1 1683WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0 1684 OR p1.opcintype = 0; 1685 oid 1686----- 1687(0 rows) 1688 1689-- opcmethod must match owning opfamily's opfmethod 1690SELECT p1.oid, p2.oid 1691FROM pg_opclass AS p1, pg_opfamily AS p2 1692WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod; 1693 oid | oid 1694-----+----- 1695(0 rows) 1696 1697-- There should not be multiple entries in pg_opclass with opcdefault true 1698-- and the same opcmethod/opcintype combination. 1699SELECT p1.oid, p2.oid 1700FROM pg_opclass AS p1, pg_opclass AS p2 1701WHERE p1.oid != p2.oid AND 1702 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND 1703 p1.opcdefault AND p2.opcdefault; 1704 oid | oid 1705-----+----- 1706(0 rows) 1707 1708-- Ask access methods to validate opclasses 1709-- (this replaces a lot of SQL-level checks that used to be done in this file) 1710SELECT oid, opcname FROM pg_opclass WHERE NOT amvalidate(oid); 1711 oid | opcname 1712-----+--------- 1713(0 rows) 1714 1715-- **************** pg_am **************** 1716-- Look for illegal values in pg_am fields 1717SELECT p1.oid, p1.amname 1718FROM pg_am AS p1 1719WHERE p1.amhandler = 0; 1720 oid | amname 1721-----+-------- 1722(0 rows) 1723 1724-- Check for amhandler functions with the wrong signature 1725SELECT p1.oid, p1.amname, p2.oid, p2.proname 1726FROM pg_am AS p1, pg_proc AS p2 1727WHERE p2.oid = p1.amhandler AND 1728 (p2.prorettype != 'index_am_handler'::regtype OR p2.proretset 1729 OR p2.pronargs != 1 1730 OR p2.proargtypes[0] != 'internal'::regtype); 1731 oid | amname | oid | proname 1732-----+--------+-----+--------- 1733(0 rows) 1734 1735-- **************** pg_amop **************** 1736-- Look for illegal values in pg_amop fields 1737SELECT p1.amopfamily, p1.amopstrategy 1738FROM pg_amop as p1 1739WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0 1740 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1; 1741 amopfamily | amopstrategy 1742------------+-------------- 1743(0 rows) 1744 1745SELECT p1.amopfamily, p1.amopstrategy 1746FROM pg_amop as p1 1747WHERE NOT ((p1.amoppurpose = 's' AND p1.amopsortfamily = 0) OR 1748 (p1.amoppurpose = 'o' AND p1.amopsortfamily <> 0)); 1749 amopfamily | amopstrategy 1750------------+-------------- 1751(0 rows) 1752 1753-- amopmethod must match owning opfamily's opfmethod 1754SELECT p1.oid, p2.oid 1755FROM pg_amop AS p1, pg_opfamily AS p2 1756WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod; 1757 oid | oid 1758-----+----- 1759(0 rows) 1760 1761-- Make a list of all the distinct operator names being used in particular 1762-- strategy slots. This is a bit hokey, since the list might need to change 1763-- in future releases, but it's an effective way of spotting mistakes such as 1764-- swapping two operators within a family. 1765SELECT DISTINCT amopmethod, amopstrategy, oprname 1766FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid 1767ORDER BY 1, 2, 3; 1768 amopmethod | amopstrategy | oprname 1769------------+--------------+--------- 1770 403 | 1 | *< 1771 403 | 1 | < 1772 403 | 1 | ~<~ 1773 403 | 2 | *<= 1774 403 | 2 | <= 1775 403 | 2 | ~<=~ 1776 403 | 3 | *= 1777 403 | 3 | = 1778 403 | 4 | *>= 1779 403 | 4 | >= 1780 403 | 4 | ~>=~ 1781 403 | 5 | *> 1782 403 | 5 | > 1783 403 | 5 | ~>~ 1784 405 | 1 | = 1785 783 | 1 | << 1786 783 | 1 | @@ 1787 783 | 2 | &< 1788 783 | 3 | && 1789 783 | 4 | &> 1790 783 | 5 | >> 1791 783 | 6 | -|- 1792 783 | 6 | ~= 1793 783 | 7 | @> 1794 783 | 8 | <@ 1795 783 | 9 | &<| 1796 783 | 10 | <<| 1797 783 | 10 | <^ 1798 783 | 11 | >^ 1799 783 | 11 | |>> 1800 783 | 12 | |&> 1801 783 | 13 | ~ 1802 783 | 14 | @ 1803 783 | 15 | <-> 1804 783 | 16 | @> 1805 783 | 18 | = 1806 783 | 19 | <> 1807 783 | 20 | < 1808 783 | 21 | <= 1809 783 | 22 | > 1810 783 | 23 | >= 1811 783 | 24 | << 1812 783 | 25 | <<= 1813 783 | 26 | >> 1814 783 | 27 | >>= 1815 783 | 28 | <@ 1816 783 | 48 | <@ 1817 783 | 68 | <@ 1818 2742 | 1 | && 1819 2742 | 1 | @@ 1820 2742 | 2 | @> 1821 2742 | 2 | @@@ 1822 2742 | 3 | <@ 1823 2742 | 4 | = 1824 2742 | 7 | @> 1825 2742 | 9 | ? 1826 2742 | 10 | ?| 1827 2742 | 11 | ?& 1828 3580 | 1 | < 1829 3580 | 1 | << 1830 3580 | 2 | &< 1831 3580 | 2 | <= 1832 3580 | 3 | && 1833 3580 | 3 | = 1834 3580 | 4 | &> 1835 3580 | 4 | >= 1836 3580 | 5 | > 1837 3580 | 5 | >> 1838 3580 | 6 | ~= 1839 3580 | 7 | >>= 1840 3580 | 7 | @> 1841 3580 | 8 | <<= 1842 3580 | 8 | <@ 1843 3580 | 9 | &<| 1844 3580 | 10 | <<| 1845 3580 | 11 | |>> 1846 3580 | 12 | |&> 1847 3580 | 16 | @> 1848 3580 | 17 | -|- 1849 3580 | 18 | = 1850 3580 | 20 | < 1851 3580 | 21 | <= 1852 3580 | 22 | > 1853 3580 | 23 | >= 1854 3580 | 24 | >> 1855 3580 | 26 | << 1856 4000 | 1 | << 1857 4000 | 1 | ~<~ 1858 4000 | 2 | &< 1859 4000 | 2 | ~<=~ 1860 4000 | 3 | && 1861 4000 | 3 | = 1862 4000 | 4 | &> 1863 4000 | 4 | ~>=~ 1864 4000 | 5 | >> 1865 4000 | 5 | ~>~ 1866 4000 | 6 | -|- 1867 4000 | 6 | ~= 1868 4000 | 7 | @> 1869 4000 | 8 | <@ 1870 4000 | 9 | &<| 1871 4000 | 10 | <<| 1872 4000 | 10 | <^ 1873 4000 | 11 | < 1874 4000 | 11 | >^ 1875 4000 | 11 | |>> 1876 4000 | 12 | <= 1877 4000 | 12 | |&> 1878 4000 | 14 | >= 1879 4000 | 15 | > 1880 4000 | 16 | @> 1881 4000 | 18 | = 1882 4000 | 19 | <> 1883 4000 | 20 | < 1884 4000 | 21 | <= 1885 4000 | 22 | > 1886 4000 | 23 | >= 1887 4000 | 24 | << 1888 4000 | 25 | <<= 1889 4000 | 26 | >> 1890 4000 | 27 | >>= 1891 4000 | 28 | ^@ 1892(122 rows) 1893 1894-- Check that all opclass search operators have selectivity estimators. 1895-- This is not absolutely required, but it seems a reasonable thing 1896-- to insist on for all standard datatypes. 1897SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname 1898FROM pg_amop AS p1, pg_operator AS p2 1899WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND 1900 (p2.oprrest = 0 OR p2.oprjoin = 0); 1901 amopfamily | amopopr | oid | oprname 1902------------+---------+-----+--------- 1903(0 rows) 1904 1905-- Check that each opclass in an opfamily has associated operators, that is 1906-- ones whose oprleft matches opcintype (possibly by coercion). 1907SELECT p1.opcname, p1.opcfamily 1908FROM pg_opclass AS p1 1909WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2 1910 WHERE p2.amopfamily = p1.opcfamily 1911 AND binary_coercible(p1.opcintype, p2.amoplefttype)); 1912 opcname | opcfamily 1913---------+----------- 1914(0 rows) 1915 1916-- Check that each operator listed in pg_amop has an associated opclass, 1917-- that is one whose opcintype matches oprleft (possibly by coercion). 1918-- Otherwise the operator is useless because it cannot be matched to an index. 1919-- (In principle it could be useful to list such operators in multiple-datatype 1920-- btree opfamilies, but in practice you'd expect there to be an opclass for 1921-- every datatype the family knows about.) 1922SELECT p1.amopfamily, p1.amopstrategy, p1.amopopr 1923FROM pg_amop AS p1 1924WHERE NOT EXISTS(SELECT 1 FROM pg_opclass AS p2 1925 WHERE p2.opcfamily = p1.amopfamily 1926 AND binary_coercible(p2.opcintype, p1.amoplefttype)); 1927 amopfamily | amopstrategy | amopopr 1928------------+--------------+--------- 1929(0 rows) 1930 1931-- Operators that are primary members of opclasses must be immutable (else 1932-- it suggests that the index ordering isn't fixed). Operators that are 1933-- cross-type members need only be stable, since they are just shorthands 1934-- for index probe queries. 1935SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc 1936FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3 1937WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND 1938 p1.amoplefttype = p1.amoprighttype AND 1939 p3.provolatile != 'i'; 1940 amopfamily | amopopr | oprname | prosrc 1941------------+---------+---------+-------- 1942(0 rows) 1943 1944SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc 1945FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3 1946WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND 1947 p1.amoplefttype != p1.amoprighttype AND 1948 p3.provolatile = 'v'; 1949 amopfamily | amopopr | oprname | prosrc 1950------------+---------+---------+-------- 1951(0 rows) 1952 1953-- **************** pg_amproc **************** 1954-- Look for illegal values in pg_amproc fields 1955SELECT p1.amprocfamily, p1.amprocnum 1956FROM pg_amproc as p1 1957WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0 1958 OR p1.amprocnum < 1 OR p1.amproc = 0; 1959 amprocfamily | amprocnum 1960--------------+----------- 1961(0 rows) 1962 1963-- Support routines that are primary members of opfamilies must be immutable 1964-- (else it suggests that the index ordering isn't fixed). But cross-type 1965-- members need only be stable, since they are just shorthands 1966-- for index probe queries. 1967SELECT p1.amprocfamily, p1.amproc, p2.prosrc 1968FROM pg_amproc AS p1, pg_proc AS p2 1969WHERE p1.amproc = p2.oid AND 1970 p1.amproclefttype = p1.amprocrighttype AND 1971 p2.provolatile != 'i'; 1972 amprocfamily | amproc | prosrc 1973--------------+--------+-------- 1974(0 rows) 1975 1976SELECT p1.amprocfamily, p1.amproc, p2.prosrc 1977FROM pg_amproc AS p1, pg_proc AS p2 1978WHERE p1.amproc = p2.oid AND 1979 p1.amproclefttype != p1.amprocrighttype AND 1980 p2.provolatile = 'v'; 1981 amprocfamily | amproc | prosrc 1982--------------+--------+-------- 1983(0 rows) 1984 1985-- **************** pg_index **************** 1986-- Look for illegal values in pg_index fields. 1987SELECT p1.indexrelid, p1.indrelid 1988FROM pg_index as p1 1989WHERE p1.indexrelid = 0 OR p1.indrelid = 0 OR 1990 p1.indnatts <= 0 OR p1.indnatts > 32; 1991 indexrelid | indrelid 1992------------+---------- 1993(0 rows) 1994 1995-- oidvector and int2vector fields should be of length indnatts. 1996SELECT p1.indexrelid, p1.indrelid 1997FROM pg_index as p1 1998WHERE array_lower(indkey, 1) != 0 OR array_upper(indkey, 1) != indnatts-1 OR 1999 array_lower(indclass, 1) != 0 OR array_upper(indclass, 1) != indnatts-1 OR 2000 array_lower(indcollation, 1) != 0 OR array_upper(indcollation, 1) != indnatts-1 OR 2001 array_lower(indoption, 1) != 0 OR array_upper(indoption, 1) != indnatts-1; 2002 indexrelid | indrelid 2003------------+---------- 2004(0 rows) 2005 2006-- Check that opclasses and collations match the underlying columns. 2007-- (As written, this test ignores expression indexes.) 2008SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname 2009FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey, 2010 unnest(indclass) as iclass, unnest(indcollation) as icoll 2011 FROM pg_index) ss, 2012 pg_attribute a, 2013 pg_opclass opc 2014WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND 2015 (NOT binary_coercible(atttypid, opcintype) OR icoll != attcollation); 2016 indexrelid | indrelid | attname | atttypid | opcname 2017------------+----------+---------+----------+--------- 2018(0 rows) 2019 2020-- For system catalogs, be even tighter: nearly all indexes should be 2021-- exact type matches not binary-coercible matches. At this writing 2022-- the only exception is an OID index on a regproc column. 2023SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname 2024FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey, 2025 unnest(indclass) as iclass, unnest(indcollation) as icoll 2026 FROM pg_index 2027 WHERE indrelid < 16384) ss, 2028 pg_attribute a, 2029 pg_opclass opc 2030WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND 2031 (opcintype != atttypid OR icoll != attcollation) 2032ORDER BY 1; 2033 indexrelid | indrelid | attname | atttypid | opcname 2034--------------------------+--------------+----------+----------+--------- 2035 pg_aggregate_fnoid_index | pg_aggregate | aggfnoid | regproc | oid_ops 2036(1 row) 2037 2038-- Check for system catalogs with collation-sensitive ordering. This is not 2039-- a representational error in pg_index, but simply wrong catalog design. 2040-- It's bad because we expect to be able to clone template0 and assign the 2041-- copy a different database collation. It would especially not work for 2042-- shared catalogs. Note that although text columns will show a collation 2043-- in indcollation, they're still okay to index with text_pattern_ops, 2044-- so allow that case. 2045SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll 2046FROM (SELECT indexrelid, indrelid, 2047 unnest(indclass) as iclass, unnest(indcollation) as icoll 2048 FROM pg_index 2049 WHERE indrelid < 16384) ss 2050WHERE icoll != 0 AND iclass != 2051 (SELECT oid FROM pg_opclass 2052 WHERE opcname = 'text_pattern_ops' AND opcmethod = 2053 (SELECT oid FROM pg_am WHERE amname = 'btree')); 2054 indexrelid | indrelid | iclass | icoll 2055------------+----------+--------+------- 2056(0 rows) 2057 2058