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