opr_sanity.sql 29.5 KB
Newer Older
1
--
2
-- OPR_SANITY
3
-- Sanity checks for common errors in making operator/procedure system tables:
4 5
-- pg_operator, pg_proc, pg_cast, pg_aggregate, pg_am,
-- pg_amop, pg_amproc, pg_opclass, pg_opfamily.
6
--
7 8
-- None of the SELECTs here should ever find any matching entries,
-- so the expected output is easy to maintain ;-).
9 10 11 12 13 14
-- A test failure indicates someone messed up an entry in the system tables.
--
-- NB: we assume the oidjoins test will have caught any dangling links,
-- that is OID or REGPROC fields that are not zero and do not match some
-- row in the linked-to table.  However, if we want to enforce that a link
-- field can't be 0, we have to check it here.
15 16 17 18
--
-- NB: run this test earlier than the create_operator test, because
-- that test creates some bogus operators...

19 20 21 22 23

-- Helper functions to deal with cases where binary-coercible matches are
-- allowed.

-- This should match IsBinaryCoercible() in parse_coerce.c.
24 25 26
create function binary_coercible(oid, oid) returns bool as $$
SELECT ($1 = $2) OR
 EXISTS(select 1 from pg_catalog.pg_cast where
27
        castsource = $1 and casttarget = $2 and
28 29 30 31 32
        castfunc = 0 and castcontext = 'i') OR
 ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
  EXISTS(select 1 from pg_catalog.pg_type where
         oid = $1 and typelem != 0 and typlen = -1))
$$ language sql strict stable;
33 34 35

-- This one ignores castcontext, so it considers only physical equivalence
-- and not whether the coercion can be invoked implicitly.
36 37 38
create function physically_coercible(oid, oid) returns bool as $$
SELECT ($1 = $2) OR
 EXISTS(select 1 from pg_catalog.pg_cast where
39
        castsource = $1 and casttarget = $2 and
40 41 42 43 44
        castfunc = 0) OR
 ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
  EXISTS(select 1 from pg_catalog.pg_type where
         oid = $1 and typelem != 0 and typlen = -1))
$$ language sql strict stable;
45

46
-- **************** pg_proc ****************
47

48
-- Look for illegal values in pg_proc fields.
49

50 51
SELECT p1.oid, p1.proname
FROM pg_proc as p1
52
WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
53 54 55
       p1.pronargs < 0 OR
       array_lower(p1.proargtypes, 1) != 0 OR
       array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR
56 57 58
       0::oid = ANY (p1.proargtypes) OR
       procost <= 0 OR
       CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END;
59

60
-- Look for conflicting proc definitions (same names and input datatypes).
61
-- (This test should be dead code now that we have the unique index
62
-- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.)
63 64 65 66 67 68 69 70

SELECT p1.oid, p1.proname, p2.oid, p2.proname
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
    p1.proname = p2.proname AND
    p1.pronargs = p2.pronargs AND
    p1.proargtypes = p2.proargtypes;

71
-- Considering only built-in procs (prolang = 12), look for multiple uses
72 73 74
-- of the same internal function (ie, matching prosrc fields).  It's OK to
-- have several entries with different pronames for the same internal function,
-- but conflicts in the number of arguments and other critical items should
75 76 77
-- be complained of.  (We don't check data types here; see next query.)
-- Note: ignore aggregate functions here, since they all point to the same
-- dummy built-in function.
78 79 80

SELECT p1.oid, p1.proname, p2.oid, p2.proname
FROM pg_proc AS p1, pg_proc AS p2
81
WHERE p1.oid < p2.oid AND
82
    p1.prosrc = p2.prosrc AND
83
    p1.prolang = 12 AND p2.prolang = 12 AND
84
    (p1.proisagg = false OR p2.proisagg = false) AND
85
    (p1.prolang != p2.prolang OR
86
     p1.proisagg != p2.proisagg OR
87
     p1.prosecdef != p2.prosecdef OR
88 89
     p1.proisstrict != p2.proisstrict OR
     p1.proretset != p2.proretset OR
90
     p1.provolatile != p2.provolatile OR
91
     p1.pronargs != p2.pronargs);
92 93 94

-- Look for uses of different type OIDs in the argument/result type fields
-- for different aliases of the same built-in function.
95 96
-- This indicates that the types are being presumed to be binary-equivalent,
-- or that the built-in function is prepared to deal with different types.
97 98
-- That's not wrong, necessarily, but we make lists of all the types being
-- so treated.  Note that the expected output of this part of the test will
99 100
-- need to be modified whenever new pairs of types are made binary-equivalent,
-- or when new polymorphic built-in functions are added!
101 102
-- Note: ignore aggregate functions here, since they all point to the same
-- dummy built-in function.
103 104 105 106

SELECT DISTINCT p1.prorettype, p2.prorettype
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
107
    p1.prosrc = p2.prosrc AND
108 109
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
110 111 112 113 114
    (p1.prorettype < p2.prorettype);

SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
115
    p1.prosrc = p2.prosrc AND
116 117
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
118 119 120 121 122
    (p1.proargtypes[0] < p2.proargtypes[0]);

SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
123
    p1.prosrc = p2.prosrc AND
124 125
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
126 127 128 129 130
    (p1.proargtypes[1] < p2.proargtypes[1]);

SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
131
    p1.prosrc = p2.prosrc AND
132 133
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
134 135 136 137 138
    (p1.proargtypes[2] < p2.proargtypes[2]);

SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
139
    p1.prosrc = p2.prosrc AND
140 141
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
142 143 144 145 146
    (p1.proargtypes[3] < p2.proargtypes[3]);

SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
147
    p1.prosrc = p2.prosrc AND
148 149
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
150 151 152 153 154
    (p1.proargtypes[4] < p2.proargtypes[4]);

SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
155
    p1.prosrc = p2.prosrc AND
156 157
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
158 159 160 161 162
    (p1.proargtypes[5] < p2.proargtypes[5]);

SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
163
    p1.prosrc = p2.prosrc AND
164 165
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
166 167 168 169 170
    (p1.proargtypes[6] < p2.proargtypes[6]);

SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
FROM pg_proc AS p1, pg_proc AS p2
WHERE p1.oid != p2.oid AND
171
    p1.prosrc = p2.prosrc AND
172 173
    p1.prolang = 12 AND p2.prolang = 12 AND
    NOT p1.proisagg AND NOT p2.proisagg AND
174 175
    (p1.proargtypes[7] < p2.proargtypes[7]);

176 177 178 179 180 181 182 183
-- Look for functions that return type "internal" and do not have any
-- "internal" argument.  Such a function would be a security hole since
-- it might be used to call an internal function from an SQL command.
-- As of 7.3 this query should find only internal_in.

SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE p1.prorettype = 'internal'::regtype AND NOT
184
    'internal'::regtype = ANY (p1.proargtypes);
185 186


187
-- **************** pg_cast ****************
188

189 190
-- Catch bogus values in pg_cast columns (other than cases detected by
-- oidjoins test).
191 192 193

SELECT *
FROM pg_cast c
194 195 196 197 198 199 200 201 202 203 204 205 206
WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i');

-- Look for casts to/from the same type that aren't length coercion functions.
-- (We assume they are length coercions if they take multiple arguments.)
-- Such entries are not necessarily harmful, but they are useless.

SELECT *
FROM pg_cast c
WHERE castsource = casttarget AND castfunc = 0;

SELECT c.*
FROM pg_cast c, pg_proc p
WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
207

208 209 210
-- Look for cast functions that don't have the right signature.  The
-- argument and result types in pg_proc must be the same as, or binary
-- compatible with, what it says in pg_cast.
211 212 213 214
-- As a special case, we allow casts from CHAR(n) that use functions
-- declared to take TEXT.  This does not pass the binary-coercibility test
-- because CHAR(n)-to-TEXT normally invokes rtrim().  However, the results
-- are the same, so long as the function is one that ignores trailing blanks.
215 216 217 218

SELECT c.*
FROM pg_cast c, pg_proc p
WHERE c.castfunc = p.oid AND
219
    (p.pronargs < 1 OR p.pronargs > 3
220 221 222
     OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
             OR (c.castsource = 'character'::regtype AND
                 p.proargtypes[0] = 'text'::regtype))
223
     OR NOT binary_coercible(p.prorettype, c.casttarget));
224

225 226 227 228 229 230
SELECT c.*
FROM pg_cast c, pg_proc p
WHERE c.castfunc = p.oid AND
    ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
     (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));

231 232
-- Look for binary compatible casts that do not have the reverse
-- direction registered as well, or where the reverse direction is not
233 234 235 236
-- also binary compatible.  This is legal, but usually not intended.

-- As of 7.4, this finds the casts from text and varchar to bpchar, because
-- those are binary-compatible while the reverse way goes through rtrim().
237

238 239 240
-- As of 8.2, this finds the cast from cidr to inet, because that is a
-- trivial binary coercion while the other way goes through inet_to_cidr().

241 242 243
SELECT *
FROM pg_cast c
WHERE c.castfunc = 0 AND
244
    NOT EXISTS (SELECT 1 FROM pg_cast k
245 246 247
                WHERE k.castfunc = 0 AND
                    k.castsource = c.casttarget AND
                    k.casttarget = c.castsource);
248

249
-- **************** pg_operator ****************
250

251
-- Look for illegal values in pg_operator fields.
252

253 254 255 256
SELECT p1.oid, p1.oprname
FROM pg_operator as p1
WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
    p1.oprresult = 0 OR p1.oprcode = 0;
257

258
-- Look for missing or unwanted operand types
259

260 261 262 263 264 265
SELECT p1.oid, p1.oprname
FROM pg_operator as p1
WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
    (p1.oprleft != 0 and p1.oprkind = 'l') OR
    (p1.oprright = 0 and p1.oprkind != 'r') OR
    (p1.oprright != 0 and p1.oprkind = 'r');
266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296

-- Look for conflicting operator definitions (same names and input datatypes).

SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
FROM pg_operator AS p1, pg_operator AS p2
WHERE p1.oid != p2.oid AND
    p1.oprname = p2.oprname AND
    p1.oprkind = p2.oprkind AND
    p1.oprleft = p2.oprleft AND
    p1.oprright = p2.oprright;

-- Look for commutative operators that don't commute.
-- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
-- We expect that B will always say that B.oprcom = A as well; that's not
-- inherently essential, but it would be inefficient not to mark it so.

SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
FROM pg_operator AS p1, pg_operator AS p2
WHERE p1.oprcom = p2.oid AND
    (p1.oprkind != 'b' OR
     p1.oprleft != p2.oprright OR
     p1.oprright != p2.oprleft OR
     p1.oprresult != p2.oprresult OR
     p1.oid != p2.oprcom);

-- Look for negatory operators that don't agree.
-- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
-- boolean results, and (x A y) == ! (x B y), or the equivalent for
-- single-operand operators.
-- We expect that B will always say that B.oprnegate = A as well; that's not
-- inherently essential, but it would be inefficient not to mark it so.
297
-- Also, A and B had better not be the same operator.
298 299 300 301 302 303 304

SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
FROM pg_operator AS p1, pg_operator AS p2
WHERE p1.oprnegate = p2.oid AND
    (p1.oprkind != p2.oprkind OR
     p1.oprleft != p2.oprleft OR
     p1.oprright != p2.oprright OR
305 306
     p1.oprresult != 'bool'::regtype OR
     p2.oprresult != 'bool'::regtype OR
307 308
     p1.oid != p2.oprnegate OR
     p1.oid = p2.oid);
309

310 311 312
-- A mergejoinable or hashjoinable operator must be binary, must return
-- boolean, and must have a commutator (itself, unless it's a cross-type
-- operator).
313

314
SELECT p1.oid, p1.oprname FROM pg_operator AS p1
315 316
WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT
    (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0);
317

318 319
-- Mergejoinable operators should appear as equality members of btree index
-- opfamilies.
320

321 322
SELECT p1.oid, p1.oprname
FROM pg_operator AS p1
323 324 325 326
WHERE p1.oprcanmerge AND NOT EXISTS
  (SELECT 1 FROM pg_amop
   WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
         amopopr = p1.oid AND amopstrategy = 3);
327

328 329 330 331 332 333 334 335 336 337
-- And the converse.

SELECT p1.oid, p1.oprname, p.amopfamily
FROM pg_operator AS p1, pg_amop p
WHERE amopopr = p1.oid
  AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
  AND amopstrategy = 3
  AND NOT p1.oprcanmerge;

-- Hashable operators should appear as members of hash index opfamilies.
338 339 340 341

SELECT p1.oid, p1.oprname
FROM pg_operator AS p1
WHERE p1.oprcanhash AND NOT EXISTS
342 343 344
  (SELECT 1 FROM pg_amop
   WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
         amopopr = p1.oid AND amopstrategy = 1);
345

346 347
-- And the converse.

348 349 350 351
SELECT p1.oid, p1.oprname, p.amopfamily
FROM pg_operator AS p1, pg_amop p
WHERE amopopr = p1.oid
  AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
352
  AND NOT p1.oprcanhash;
353

354 355 356 357 358 359 360 361
-- Check that each operator defined in pg_operator matches its oprcode entry
-- in pg_proc.  Easiest to do this separately for each oprkind.

SELECT p1.oid, p1.oprname, p2.oid, p2.proname
FROM pg_operator AS p1, pg_proc AS p2
WHERE p1.oprcode = p2.oid AND
    p1.oprkind = 'b' AND
    (p2.pronargs != 2
362 363 364
     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
     OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
     OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
365 366 367 368 369

SELECT p1.oid, p1.oprname, p2.oid, p2.proname
FROM pg_operator AS p1, pg_proc AS p2
WHERE p1.oprcode = p2.oid AND
    p1.oprkind = 'l' AND
370 371 372 373
    (p2.pronargs != 1
     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
     OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
     OR p1.oprleft != 0);
374 375 376 377 378

SELECT p1.oid, p1.oprname, p2.oid, p2.proname
FROM pg_operator AS p1, pg_proc AS p2
WHERE p1.oprcode = p2.oid AND
    p1.oprkind = 'r' AND
379 380 381 382
    (p2.pronargs != 1
     OR NOT binary_coercible(p2.prorettype, p1.oprresult)
     OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
     OR p1.oprright != 0);
383

384 385 386 387 388 389
-- If the operator is mergejoinable or hashjoinable, its underlying function
-- should not be volatile.

SELECT p1.oid, p1.oprname, p2.oid, p2.proname
FROM pg_operator AS p1, pg_proc AS p2
WHERE p1.oprcode = p2.oid AND
390
    (p1.oprcanmerge OR p1.oprcanhash) AND
391 392
    p2.provolatile = 'v';

393 394 395
-- If oprrest is set, the operator must return boolean,
-- and it must link to a proc with the right signature
-- to be a restriction selectivity estimator.
396
-- The proc signature we want is: float8 proc(internal, oid, internal, int4)
397 398 399 400

SELECT p1.oid, p1.oprname, p2.oid, p2.proname
FROM pg_operator AS p1, pg_proc AS p2
WHERE p1.oprrest = p2.oid AND
401 402
    (p1.oprresult != 'bool'::regtype OR
     p2.prorettype != 'float8'::regtype OR p2.proretset OR
403
     p2.pronargs != 4 OR
404 405 406 407
     p2.proargtypes[0] != 'internal'::regtype OR
     p2.proargtypes[1] != 'oid'::regtype OR
     p2.proargtypes[2] != 'internal'::regtype OR
     p2.proargtypes[3] != 'int4'::regtype);
408 409 410 411

-- If oprjoin is set, the operator must be a binary boolean op,
-- and it must link to a proc with the right signature
-- to be a join selectivity estimator.
412
-- The proc signature we want is: float8 proc(internal, oid, internal, int2)
413 414 415 416

SELECT p1.oid, p1.oprname, p2.oid, p2.proname
FROM pg_operator AS p1, pg_proc AS p2
WHERE p1.oprjoin = p2.oid AND
417 418
    (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
     p2.prorettype != 'float8'::regtype OR p2.proretset OR
419
     p2.pronargs != 4 OR
420 421
     p2.proargtypes[0] != 'internal'::regtype OR
     p2.proargtypes[1] != 'oid'::regtype OR
422 423
     p2.proargtypes[2] != 'internal'::regtype OR
     p2.proargtypes[3] != 'int2'::regtype);
424 425 426 427 428

-- **************** pg_aggregate ****************

-- Look for illegal values in pg_aggregate fields.

429
SELECT ctid, aggfnoid::oid
430
FROM pg_aggregate as p1
431 432 433 434 435 436 437
WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;

-- Make sure the matching pg_proc entry is sensible, too.

SELECT a.aggfnoid::oid, p.proname
FROM pg_aggregate as a, pg_proc as p
WHERE a.aggfnoid = p.oid AND
438
    (NOT p.proisagg OR p.proretset);
439 440 441 442 443 444 445

-- Make sure there are no proisagg pg_proc entries without matches.

SELECT oid, proname
FROM pg_proc as p
WHERE p.proisagg AND
    NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
446

447
-- If there is no finalfn then the output type must be the transtype.
448

449 450 451 452
SELECT a.aggfnoid::oid, p.proname
FROM pg_aggregate as a, pg_proc as p
WHERE a.aggfnoid = p.oid AND
    a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
453

454
-- Cross-check transfn against its entry in pg_proc.
455 456 457 458
-- NOTE: use physically_coercible here, not binary_coercible, because
-- max and min on abstime are implemented using int4larger/int4smaller.
SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
459
WHERE a.aggfnoid = p.oid AND
460 461
    a.aggtransfn = ptr.oid AND
    (ptr.proretset
462
     OR NOT (ptr.pronargs = p.pronargs + 1)
463 464
     OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
     OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
465 466 467 468 469 470 471 472
     OR (p.pronargs > 0 AND
         NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
     OR (p.pronargs > 1 AND
         NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
     OR (p.pronargs > 2 AND
         NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
     -- we could carry the check further, but that's enough for now
    );
473 474 475

-- Cross-check finalfn (if present) against its entry in pg_proc.

476 477
SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
478
WHERE a.aggfnoid = p.oid AND
479 480 481 482 483
    a.aggfinalfn = pfn.oid AND
    (pfn.proretset
     OR NOT binary_coercible(pfn.prorettype, p.prorettype)
     OR pfn.pronargs != 1
     OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
484

485
-- If transfn is strict then either initval should be non-NULL, or
486
-- input type should match transtype so that the first non-null input
487 488
-- can be assigned as the state value.

489 490
SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
491
WHERE a.aggfnoid = p.oid AND
492 493 494
    a.aggtransfn = ptr.oid AND ptr.proisstrict AND
    a.agginitval IS NULL AND
    NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
495

496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511
-- Cross-check aggsortop (if present) against pg_operator.
-- We expect to find only "<" for "min" and ">" for "max".

SELECT DISTINCT proname, oprname
FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
ORDER BY 1;

-- Check datatypes match

SELECT a.aggfnoid::oid, o.oid
FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
    (oprkind != 'b' OR oprresult != 'boolean'::regtype
     OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);

512
-- Check operator is a suitable btree opfamily member
513 514 515 516

SELECT a.aggfnoid::oid, o.oid
FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
517 518 519 520 521
    NOT EXISTS(SELECT 1 FROM pg_amop
               WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
                     AND amopopr = o.oid
                     AND amoplefttype = o.oprleft
                     AND amoprighttype = o.oprright);
522 523 524 525 526

-- Check correspondence of btree strategies and names

SELECT DISTINCT proname, oprname, amopstrategy
FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
527
     pg_amop as ao
528
WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
529 530 531 532 533 534 535 536 537 538 539
    amopopr = o.oid AND
    amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
ORDER BY 1, 2;

-- **************** pg_opfamily ****************

-- Look for illegal values in pg_opfamily fields

SELECT p1.oid
FROM pg_opfamily as p1
WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
540

541 542
-- **************** pg_opclass ****************

543 544 545
-- Look for illegal values in pg_opclass fields

SELECT p1.oid
546 547 548 549 550 551 552 553 554
FROM pg_opclass AS p1
WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
    OR p1.opcintype = 0;

-- opcmethod must match owning opfamily's opfmethod

SELECT p1.oid, p2.oid
FROM pg_opclass AS p1, pg_opfamily AS p2
WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
555 556

-- There should not be multiple entries in pg_opclass with opcdefault true
557
-- and the same opcmethod/opcintype combination.
558 559 560 561

SELECT p1.oid, p2.oid
FROM pg_opclass AS p1, pg_opclass AS p2
WHERE p1.oid != p2.oid AND
562
    p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
563
    p1.opcdefault AND p2.opcdefault;
564

565 566 567 568
-- **************** pg_amop ****************

-- Look for illegal values in pg_amop fields

569
SELECT p1.amopfamily, p1.amopstrategy
570
FROM pg_amop as p1
571 572 573 574 575 576 577 578 579 580 581 582 583 584 585
WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
    OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;

-- amoplefttype/amoprighttype must match the operator

SELECT p1.oid, p2.oid
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND NOT
    (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);

-- amopmethod must match owning opfamily's opfmethod

SELECT p1.oid, p2.oid
FROM pg_amop AS p1, pg_opfamily AS p2
WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
586 587 588

-- Cross-check amopstrategy index against parent AM

589 590 591
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
FROM pg_amop AS p1, pg_am AS p2
WHERE p1.amopmethod = p2.oid AND
592
    p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
593

594
-- Detect missing pg_amop entries: should have as many strategy operators
595
-- as AM expects for each datatype combination supported by the opfamily.
596
-- We can't check this for AMs with variable strategy sets.
597

598 599 600
SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
FROM pg_am AS p1, pg_amop AS p2
WHERE p2.amopmethod = p1.oid AND
601
    p1.amstrategies <> 0 AND
602 603 604 605
    p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
                        WHERE p3.amopfamily = p2.amopfamily AND
                              p3.amoplefttype = p2.amoplefttype AND
                              p3.amoprighttype = p2.amoprighttype);
606

607 608
-- Check that amopopr points at a reasonable-looking operator, ie a binary
-- operator yielding boolean.
609

610
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
611 612
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND
613 614 615 616 617
    (p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);

-- Make a list of all the distinct operator names being used in particular
-- strategy slots.  This is a bit hokey, since the list might need to change
-- in future releases, but it's an effective way of spotting mistakes such as
618
-- swapping two operators within a family.
619

620 621
SELECT DISTINCT amopmethod, amopstrategy, oprname
FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
622
ORDER BY 1, 2, 3;
623

624 625 626 627
-- Check that all operators linked to by opclass entries have selectivity
-- estimators.  This is not absolutely required, but it seems a reasonable
-- thing to insist on for all standard datatypes.

628
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
629 630 631 632
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND
    (p2.oprrest = 0 OR p2.oprjoin = 0);

633 634
-- Check that each opclass in an opfamily has associated operators, that is
-- ones whose oprleft matches opcintype (possibly by coercion).
635

636 637 638 639 640
SELECT p1.opcname, p1.opcfamily
FROM pg_opclass AS p1
WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
                 WHERE p2.amopfamily = p1.opcfamily
                   AND binary_coercible(p1.opcintype, p2.amoplefttype));
641

642 643 644 645 646
-- Operators that are primary members of opclasses must be immutable (else
-- it suggests that the index ordering isn't fixed).  Operators that are
-- cross-type members need only be stable, since they are just shorthands
-- for index probe queries.

647
SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
648 649
FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
650
    p1.amoplefttype = p1.amoprighttype AND
651 652
    p3.provolatile != 'i';

653
SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
654 655
FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
656
    p1.amoplefttype != p1.amoprighttype AND
657 658
    p3.provolatile = 'v';

659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694
-- Multiple-datatype btree opclasses should provide closed sets of equality
-- operators; that is if you provide int2 = int4 and int4 = int8 then you
-- must also provide int2 = int8 (and commutators of all these).  This is
-- necessary because the planner tries to deduce additional qual clauses from
-- transitivity of mergejoinable operators.  If there are clauses
-- int2var = int4var and int4var = int8var, the planner will deduce
-- int2var = int8var ... and it had better have a way to represent it.

-- check commutative closure
SELECT p1.amoplefttype, p1.amoprighttype
FROM pg_amop AS p1
WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
    p1.amopstrategy = 3 AND
    p1.amoplefttype != p1.amoprighttype AND
    NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
                 p2.amopfamily = p1.amopfamily AND
                 p2.amoplefttype = p1.amoprighttype AND
                 p2.amoprighttype = p1.amoplefttype AND
                 p2.amopstrategy = 3);

-- check transitive closure
SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
FROM pg_amop AS p1, pg_amop AS p2
WHERE p1.amopfamily = p2.amopfamily AND
    p1.amoprighttype = p2.amoplefttype AND
    p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
    p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
    p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
    p1.amoplefttype != p1.amoprighttype AND
    p2.amoplefttype != p2.amoprighttype AND
    NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
                 p3.amopfamily = p1.amopfamily AND
                 p3.amoplefttype = p1.amoplefttype AND
                 p3.amoprighttype = p2.amoprighttype AND
                 p3.amopstrategy = 3);

695 696 697 698
-- **************** pg_amproc ****************

-- Look for illegal values in pg_amproc fields

699
SELECT p1.amprocfamily, p1.amprocnum
700
FROM pg_amproc as p1
701 702
WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
    OR p1.amprocnum < 1 OR p1.amproc = 0;
703 704 705

-- Cross-check amprocnum index against parent AM

706 707 708
SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
709
    p1.amprocnum > p2.amsupport;
710 711

-- Detect missing pg_amproc entries: should have as many support functions
712
-- as AM expects for each datatype combination supported by the opfamily.
713

714 715 716
SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
717
    p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
718 719 720
                     WHERE p4.amprocfamily = p2.oid AND
                           p4.amproclefttype = p3.amproclefttype AND
                           p4.amprocrighttype = p3.amprocrighttype);
721 722 723 724 725

-- Unfortunately, we can't check the amproc link very well because the
-- signature of the function may be different for different support routines
-- or different base data types.
-- We can check that all the referenced instances of the same support
726 727
-- routine number take the same number of parameters, but that's about it
-- for a general check...
728

729
SELECT p1.amprocfamily, p1.amprocnum,
730
	p2.oid, p2.proname,
731 732
	p3.opfname,
	p4.amprocfamily, p4.amprocnum,
733
	p5.oid, p5.proname,
734 735 736 737 738
	p6.opfname
FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
     pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
    p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
739 740
    p1.amproc = p2.oid AND p4.amproc = p5.oid AND
    (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
741 742

-- For btree, though, we can do better since we know the support routines
743
-- must be of the form cmp(lefttype, righttype) returns int4.
744

745
SELECT p1.amprocfamily, p1.amprocnum,
746
	p2.oid, p2.proname,
747 748 749 750 751
	p3.opfname
FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
    AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
    (amprocnum != 1
752
     OR proretset
753
     OR prorettype != 'int4'::regtype
754
     OR pronargs != 2
755 756
     OR proargtypes[0] != amproclefttype
     OR proargtypes[1] != amprocrighttype);
757

758
-- For hash we can also do a little better: the support routines must be
759 760 761
-- of the form hash(lefttype) returns int4.  There are several cases where
-- we cheat and use a hash function that is physically compatible with the
-- datatype even though there's no cast, so for now we can't check that.
762

763
SELECT p1.amprocfamily, p1.amprocnum,
764
	p2.oid, p2.proname,
765 766 767 768 769
	p3.opfname
FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
    AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
    (amprocnum != 1
770
     OR proretset
771
     OR prorettype != 'int4'::regtype
772
     OR pronargs != 1
773 774
--   OR NOT physically_coercible(amproclefttype, proargtypes[0])
     OR amproclefttype != amprocrighttype);
775

776
-- Support routines that are primary members of opfamilies must be immutable
777 778 779 780
-- (else it suggests that the index ordering isn't fixed).  But cross-type
-- members need only be stable, since they are just shorthands
-- for index probe queries.

781
SELECT p1.amprocfamily, p1.amproc, p2.prosrc
782 783
FROM pg_amproc AS p1, pg_proc AS p2
WHERE p1.amproc = p2.oid AND
784
    p1.amproclefttype = p1.amprocrighttype AND
785 786
    p2.provolatile != 'i';

787
SELECT p1.amprocfamily, p1.amproc, p2.prosrc
788 789
FROM pg_amproc AS p1, pg_proc AS p2
WHERE p1.amproc = p2.oid AND
790
    p1.amproclefttype != p1.amprocrighttype AND
791
    p2.provolatile = 'v';