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