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