3 -- Sanity checks for common errors in making operator/procedure system tables:
4 -- pg_operator, pg_proc, pg_cast, pg_aggregate, pg_am,
5 -- pg_amop, pg_amproc, pg_opclass, pg_opfamily.
7 -- None of the SELECTs here should ever find any matching entries,
8 -- so the expected output is easy to maintain ;-).
9 -- A test failure indicates someone messed up an entry in the system tables.
11 -- NB: we assume the oidjoins test will have caught any dangling links,
12 -- that is OID or REGPROC fields that are not zero and do not match some
13 -- row in the linked-to table. However, if we want to enforce that a link
14 -- field can't be 0, we have to check it here.
16 -- NB: run this test earlier than the create_operator test, because
17 -- that test creates some bogus operators...
20 -- Helper functions to deal with cases where binary-coercible matches are
23 -- This should match IsBinaryCoercible() in parse_coerce.c.
24 create function binary_coercible(oid, oid) returns bool as $$
26 EXISTS(select 1 from pg_catalog.pg_cast where
27 castsource = $1 and casttarget = $2 and
28 castmethod = 'b' and castcontext = 'i') OR
29 ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
30 EXISTS(select 1 from pg_catalog.pg_type where
31 oid = $1 and typelem != 0 and typlen = -1))
32 $$ language sql strict stable;
34 -- This one ignores castcontext, so it considers only physical equivalence
35 -- and not whether the coercion can be invoked implicitly.
36 create function physically_coercible(oid, oid) returns bool as $$
38 EXISTS(select 1 from pg_catalog.pg_cast where
39 castsource = $1 and casttarget = $2 and
41 ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
42 EXISTS(select 1 from pg_catalog.pg_type where
43 oid = $1 and typelem != 0 and typlen = -1))
44 $$ language sql strict stable;
46 -- **************** pg_proc ****************
48 -- Look for illegal values in pg_proc fields.
50 SELECT p1.oid, p1.proname
52 WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
54 p1.pronargdefaults < 0 OR
55 p1.pronargdefaults > p1.pronargs OR
56 array_lower(p1.proargtypes, 1) != 0 OR
57 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR
58 0::oid = ANY (p1.proargtypes) OR
60 CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END;
62 -- prosrc should never be null or empty
63 SELECT p1.oid, p1.proname
65 WHERE prosrc IS NULL OR prosrc = '' OR prosrc = '-';
67 -- proiswindow shouldn't be set together with proisagg or proretset
68 SELECT p1.oid, p1.proname
70 WHERE proiswindow AND (proisagg OR proretset);
72 -- pronargdefaults should be 0 iff proargdefaults is null
73 SELECT p1.oid, p1.proname
75 WHERE (pronargdefaults <> 0) != (proargdefaults IS NOT NULL);
77 -- probin should be non-empty for C functions, null everywhere else
78 SELECT p1.oid, p1.proname
80 WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-');
82 SELECT p1.oid, p1.proname
84 WHERE prolang != 13 AND probin IS NOT NULL;
86 -- Look for conflicting proc definitions (same names and input datatypes).
87 -- (This test should be dead code now that we have the unique index
88 -- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.)
90 SELECT p1.oid, p1.proname, p2.oid, p2.proname
91 FROM pg_proc AS p1, pg_proc AS p2
92 WHERE p1.oid != p2.oid AND
93 p1.proname = p2.proname AND
94 p1.pronargs = p2.pronargs AND
95 p1.proargtypes = p2.proargtypes;
97 -- Considering only built-in procs (prolang = 12), look for multiple uses
98 -- of the same internal function (ie, matching prosrc fields). It's OK to
99 -- have several entries with different pronames for the same internal function,
100 -- but conflicts in the number of arguments and other critical items should
101 -- be complained of. (We don't check data types here; see next query.)
102 -- Note: ignore aggregate functions here, since they all point to the same
103 -- dummy built-in function.
105 SELECT p1.oid, p1.proname, p2.oid, p2.proname
106 FROM pg_proc AS p1, pg_proc AS p2
107 WHERE p1.oid < p2.oid AND
108 p1.prosrc = p2.prosrc AND
109 p1.prolang = 12 AND p2.prolang = 12 AND
110 (p1.proisagg = false OR p2.proisagg = false) AND
111 (p1.prolang != p2.prolang OR
112 p1.proisagg != p2.proisagg OR
113 p1.prosecdef != p2.prosecdef OR
114 p1.proisstrict != p2.proisstrict OR
115 p1.proretset != p2.proretset OR
116 p1.provolatile != p2.provolatile OR
117 p1.pronargs != p2.pronargs);
119 -- Look for uses of different type OIDs in the argument/result type fields
120 -- for different aliases of the same built-in function.
121 -- This indicates that the types are being presumed to be binary-equivalent,
122 -- or that the built-in function is prepared to deal with different types.
123 -- That's not wrong, necessarily, but we make lists of all the types being
124 -- so treated. Note that the expected output of this part of the test will
125 -- need to be modified whenever new pairs of types are made binary-equivalent,
126 -- or when new polymorphic built-in functions are added!
127 -- Note: ignore aggregate functions here, since they all point to the same
128 -- dummy built-in function.
130 SELECT DISTINCT p1.prorettype, p2.prorettype
131 FROM pg_proc AS p1, pg_proc AS p2
132 WHERE p1.oid != p2.oid AND
133 p1.prosrc = p2.prosrc AND
134 p1.prolang = 12 AND p2.prolang = 12 AND
135 NOT p1.proisagg AND NOT p2.proisagg AND
136 (p1.prorettype < p2.prorettype)
139 SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
140 FROM pg_proc AS p1, pg_proc AS p2
141 WHERE p1.oid != p2.oid AND
142 p1.prosrc = p2.prosrc AND
143 p1.prolang = 12 AND p2.prolang = 12 AND
144 NOT p1.proisagg AND NOT p2.proisagg AND
145 (p1.proargtypes[0] < p2.proargtypes[0])
148 SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
149 FROM pg_proc AS p1, pg_proc AS p2
150 WHERE p1.oid != p2.oid AND
151 p1.prosrc = p2.prosrc AND
152 p1.prolang = 12 AND p2.prolang = 12 AND
153 NOT p1.proisagg AND NOT p2.proisagg AND
154 (p1.proargtypes[1] < p2.proargtypes[1])
157 SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
158 FROM pg_proc AS p1, pg_proc AS p2
159 WHERE p1.oid != p2.oid AND
160 p1.prosrc = p2.prosrc AND
161 p1.prolang = 12 AND p2.prolang = 12 AND
162 NOT p1.proisagg AND NOT p2.proisagg AND
163 (p1.proargtypes[2] < p2.proargtypes[2])
166 SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
167 FROM pg_proc AS p1, pg_proc AS p2
168 WHERE p1.oid != p2.oid AND
169 p1.prosrc = p2.prosrc AND
170 p1.prolang = 12 AND p2.prolang = 12 AND
171 NOT p1.proisagg AND NOT p2.proisagg AND
172 (p1.proargtypes[3] < p2.proargtypes[3])
175 SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
176 FROM pg_proc AS p1, pg_proc AS p2
177 WHERE p1.oid != p2.oid AND
178 p1.prosrc = p2.prosrc AND
179 p1.prolang = 12 AND p2.prolang = 12 AND
180 NOT p1.proisagg AND NOT p2.proisagg AND
181 (p1.proargtypes[4] < p2.proargtypes[4])
184 SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
185 FROM pg_proc AS p1, pg_proc AS p2
186 WHERE p1.oid != p2.oid AND
187 p1.prosrc = p2.prosrc AND
188 p1.prolang = 12 AND p2.prolang = 12 AND
189 NOT p1.proisagg AND NOT p2.proisagg AND
190 (p1.proargtypes[5] < p2.proargtypes[5])
193 SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
194 FROM pg_proc AS p1, pg_proc AS p2
195 WHERE p1.oid != p2.oid AND
196 p1.prosrc = p2.prosrc AND
197 p1.prolang = 12 AND p2.prolang = 12 AND
198 NOT p1.proisagg AND NOT p2.proisagg AND
199 (p1.proargtypes[6] < p2.proargtypes[6])
202 SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
203 FROM pg_proc AS p1, pg_proc AS p2
204 WHERE p1.oid != p2.oid AND
205 p1.prosrc = p2.prosrc AND
206 p1.prolang = 12 AND p2.prolang = 12 AND
207 NOT p1.proisagg AND NOT p2.proisagg AND
208 (p1.proargtypes[7] < p2.proargtypes[7])
211 -- Look for functions that return type "internal" and do not have any
212 -- "internal" argument. Such a function would be a security hole since
213 -- it might be used to call an internal function from an SQL command.
214 -- As of 7.3 this query should find only internal_in.
216 SELECT p1.oid, p1.proname
218 WHERE p1.prorettype = 'internal'::regtype AND NOT
219 'internal'::regtype = ANY (p1.proargtypes);
221 -- Check for length inconsistencies between the various argument-info arrays.
223 SELECT p1.oid, p1.proname
225 WHERE proallargtypes IS NOT NULL AND
226 array_length(proallargtypes,1) < array_length(proargtypes,1);
228 SELECT p1.oid, p1.proname
230 WHERE proargmodes IS NOT NULL AND
231 array_length(proargmodes,1) < array_length(proargtypes,1);
233 SELECT p1.oid, p1.proname
235 WHERE proargnames IS NOT NULL AND
236 array_length(proargnames,1) < array_length(proargtypes,1);
238 SELECT p1.oid, p1.proname
240 WHERE proallargtypes IS NOT NULL AND proargmodes IS NOT NULL AND
241 array_length(proallargtypes,1) <> array_length(proargmodes,1);
243 SELECT p1.oid, p1.proname
245 WHERE proallargtypes IS NOT NULL AND proargnames IS NOT NULL AND
246 array_length(proallargtypes,1) <> array_length(proargnames,1);
248 SELECT p1.oid, p1.proname
250 WHERE proargmodes IS NOT NULL AND proargnames IS NOT NULL AND
251 array_length(proargmodes,1) <> array_length(proargnames,1);
254 -- **************** pg_cast ****************
256 -- Catch bogus values in pg_cast columns (other than cases detected by
261 WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i')
262 OR castmethod NOT IN ('f', 'b' ,'i');
264 -- Check that castfunc is nonzero only for cast methods that need a function,
265 -- and zero otherwise
269 WHERE (castmethod = 'f' AND castfunc = 0)
270 OR (castmethod IN ('b', 'i') AND castfunc <> 0);
272 -- Look for casts to/from the same type that aren't length coercion functions.
273 -- (We assume they are length coercions if they take multiple arguments.)
274 -- Such entries are not necessarily harmful, but they are useless.
278 WHERE castsource = casttarget AND castfunc = 0;
281 FROM pg_cast c, pg_proc p
282 WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
284 -- Look for cast functions that don't have the right signature. The
285 -- argument and result types in pg_proc must be the same as, or binary
286 -- compatible with, what it says in pg_cast.
287 -- As a special case, we allow casts from CHAR(n) that use functions
288 -- declared to take TEXT. This does not pass the binary-coercibility test
289 -- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
290 -- are the same, so long as the function is one that ignores trailing blanks.
293 FROM pg_cast c, pg_proc p
294 WHERE c.castfunc = p.oid AND
295 (p.pronargs < 1 OR p.pronargs > 3
296 OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
297 OR (c.castsource = 'character'::regtype AND
298 p.proargtypes[0] = 'text'::regtype))
299 OR NOT binary_coercible(p.prorettype, c.casttarget));
302 FROM pg_cast c, pg_proc p
303 WHERE c.castfunc = p.oid AND
304 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
305 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
307 -- Look for binary compatible casts that do not have the reverse
308 -- direction registered as well, or where the reverse direction is not
309 -- also binary compatible. This is legal, but usually not intended.
311 -- As of 7.4, this finds the casts from text and varchar to bpchar, because
312 -- those are binary-compatible while the reverse way goes through rtrim().
314 -- As of 8.2, this finds the cast from cidr to inet, because that is a
315 -- trivial binary coercion while the other way goes through inet_to_cidr().
317 -- As of 8.3, this finds the casts from xml to text, varchar, and bpchar,
318 -- because those are binary-compatible while the reverse goes through
319 -- texttoxml(), which does an XML syntax check.
323 WHERE c.castmethod = 'b' AND
324 NOT EXISTS (SELECT 1 FROM pg_cast k
325 WHERE k.castmethod = 'b' AND
326 k.castsource = c.casttarget AND
327 k.casttarget = c.castsource);
329 -- **************** pg_operator ****************
331 -- Look for illegal values in pg_operator fields.
333 SELECT p1.oid, p1.oprname
334 FROM pg_operator as p1
335 WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
336 p1.oprresult = 0 OR p1.oprcode = 0;
338 -- Look for missing or unwanted operand types
340 SELECT p1.oid, p1.oprname
341 FROM pg_operator as p1
342 WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
343 (p1.oprleft != 0 and p1.oprkind = 'l') OR
344 (p1.oprright = 0 and p1.oprkind != 'r') OR
345 (p1.oprright != 0 and p1.oprkind = 'r');
347 -- Look for conflicting operator definitions (same names and input datatypes).
349 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
350 FROM pg_operator AS p1, pg_operator AS p2
351 WHERE p1.oid != p2.oid AND
352 p1.oprname = p2.oprname AND
353 p1.oprkind = p2.oprkind AND
354 p1.oprleft = p2.oprleft AND
355 p1.oprright = p2.oprright;
357 -- Look for commutative operators that don't commute.
358 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
359 -- We expect that B will always say that B.oprcom = A as well; that's not
360 -- inherently essential, but it would be inefficient not to mark it so.
362 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
363 FROM pg_operator AS p1, pg_operator AS p2
364 WHERE p1.oprcom = p2.oid AND
365 (p1.oprkind != 'b' OR
366 p1.oprleft != p2.oprright OR
367 p1.oprright != p2.oprleft OR
368 p1.oprresult != p2.oprresult OR
369 p1.oid != p2.oprcom);
371 -- Look for negatory operators that don't agree.
372 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
373 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
374 -- single-operand operators.
375 -- We expect that B will always say that B.oprnegate = A as well; that's not
376 -- inherently essential, but it would be inefficient not to mark it so.
377 -- Also, A and B had better not be the same operator.
379 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
380 FROM pg_operator AS p1, pg_operator AS p2
381 WHERE p1.oprnegate = p2.oid AND
382 (p1.oprkind != p2.oprkind OR
383 p1.oprleft != p2.oprleft OR
384 p1.oprright != p2.oprright OR
385 p1.oprresult != 'bool'::regtype OR
386 p2.oprresult != 'bool'::regtype OR
387 p1.oid != p2.oprnegate OR
390 -- A mergejoinable or hashjoinable operator must be binary, must return
391 -- boolean, and must have a commutator (itself, unless it's a cross-type
394 SELECT p1.oid, p1.oprname FROM pg_operator AS p1
395 WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT
396 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0);
398 -- What's more, the commutator had better be mergejoinable/hashjoinable too.
400 SELECT p1.oid, p1.oprname, p2.oid, p2.oprname
401 FROM pg_operator AS p1, pg_operator AS p2
402 WHERE p1.oprcom = p2.oid AND
403 (p1.oprcanmerge != p2.oprcanmerge OR
404 p1.oprcanhash != p2.oprcanhash);
406 -- Mergejoinable operators should appear as equality members of btree index
409 SELECT p1.oid, p1.oprname
410 FROM pg_operator AS p1
411 WHERE p1.oprcanmerge AND NOT EXISTS
412 (SELECT 1 FROM pg_amop
413 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
414 amopopr = p1.oid AND amopstrategy = 3);
418 SELECT p1.oid, p1.oprname, p.amopfamily
419 FROM pg_operator AS p1, pg_amop p
420 WHERE amopopr = p1.oid
421 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
423 AND NOT p1.oprcanmerge;
425 -- Hashable operators should appear as members of hash index opfamilies.
427 SELECT p1.oid, p1.oprname
428 FROM pg_operator AS p1
429 WHERE p1.oprcanhash AND NOT EXISTS
430 (SELECT 1 FROM pg_amop
431 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
432 amopopr = p1.oid AND amopstrategy = 1);
436 SELECT p1.oid, p1.oprname, p.amopfamily
437 FROM pg_operator AS p1, pg_amop p
438 WHERE amopopr = p1.oid
439 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
440 AND NOT p1.oprcanhash;
442 -- Check that each operator defined in pg_operator matches its oprcode entry
443 -- in pg_proc. Easiest to do this separately for each oprkind.
445 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
446 FROM pg_operator AS p1, pg_proc AS p2
447 WHERE p1.oprcode = p2.oid AND
450 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
451 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
452 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
454 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
455 FROM pg_operator AS p1, pg_proc AS p2
456 WHERE p1.oprcode = p2.oid AND
459 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
460 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
463 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
464 FROM pg_operator AS p1, pg_proc AS p2
465 WHERE p1.oprcode = p2.oid AND
468 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
469 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
470 OR p1.oprright != 0);
472 -- If the operator is mergejoinable or hashjoinable, its underlying function
473 -- should not be volatile.
475 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
476 FROM pg_operator AS p1, pg_proc AS p2
477 WHERE p1.oprcode = p2.oid AND
478 (p1.oprcanmerge OR p1.oprcanhash) AND
479 p2.provolatile = 'v';
481 -- If oprrest is set, the operator must return boolean,
482 -- and it must link to a proc with the right signature
483 -- to be a restriction selectivity estimator.
484 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
486 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
487 FROM pg_operator AS p1, pg_proc AS p2
488 WHERE p1.oprrest = p2.oid AND
489 (p1.oprresult != 'bool'::regtype OR
490 p2.prorettype != 'float8'::regtype OR p2.proretset OR
492 p2.proargtypes[0] != 'internal'::regtype OR
493 p2.proargtypes[1] != 'oid'::regtype OR
494 p2.proargtypes[2] != 'internal'::regtype OR
495 p2.proargtypes[3] != 'int4'::regtype);
497 -- If oprjoin is set, the operator must be a binary boolean op,
498 -- and it must link to a proc with the right signature
499 -- to be a join selectivity estimator.
500 -- The proc signature we want is: float8 proc(internal, oid, internal, int2, internal)
501 -- (Note: the old signature with only 4 args is still allowed, but no core
502 -- estimator should be using it.)
504 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
505 FROM pg_operator AS p1, pg_proc AS p2
506 WHERE p1.oprjoin = p2.oid AND
507 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
508 p2.prorettype != 'float8'::regtype OR p2.proretset OR
510 p2.proargtypes[0] != 'internal'::regtype OR
511 p2.proargtypes[1] != 'oid'::regtype OR
512 p2.proargtypes[2] != 'internal'::regtype OR
513 p2.proargtypes[3] != 'int2'::regtype OR
514 p2.proargtypes[4] != 'internal'::regtype);
516 -- **************** pg_aggregate ****************
518 -- Look for illegal values in pg_aggregate fields.
520 SELECT ctid, aggfnoid::oid
521 FROM pg_aggregate as p1
522 WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
524 -- Make sure the matching pg_proc entry is sensible, too.
526 SELECT a.aggfnoid::oid, p.proname
527 FROM pg_aggregate as a, pg_proc as p
528 WHERE a.aggfnoid = p.oid AND
529 (NOT p.proisagg OR p.proretset);
531 -- Make sure there are no proisagg pg_proc entries without matches.
536 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
538 -- If there is no finalfn then the output type must be the transtype.
540 SELECT a.aggfnoid::oid, p.proname
541 FROM pg_aggregate as a, pg_proc as p
542 WHERE a.aggfnoid = p.oid AND
543 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
545 -- Cross-check transfn against its entry in pg_proc.
546 -- NOTE: use physically_coercible here, not binary_coercible, because
547 -- max and min on abstime are implemented using int4larger/int4smaller.
548 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
549 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
550 WHERE a.aggfnoid = p.oid AND
551 a.aggtransfn = ptr.oid AND
553 OR NOT (ptr.pronargs = p.pronargs + 1)
554 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
555 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
556 OR (p.pronargs > 0 AND
557 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
558 OR (p.pronargs > 1 AND
559 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
560 OR (p.pronargs > 2 AND
561 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
562 -- we could carry the check further, but that's enough for now
565 -- Cross-check finalfn (if present) against its entry in pg_proc.
567 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
568 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
569 WHERE a.aggfnoid = p.oid AND
570 a.aggfinalfn = pfn.oid AND
572 OR NOT binary_coercible(pfn.prorettype, p.prorettype)
574 OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
576 -- If transfn is strict then either initval should be non-NULL, or
577 -- input type should match transtype so that the first non-null input
578 -- can be assigned as the state value.
580 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
581 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
582 WHERE a.aggfnoid = p.oid AND
583 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
584 a.agginitval IS NULL AND
585 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
587 -- Cross-check aggsortop (if present) against pg_operator.
588 -- We expect to find only "<" for "min" and ">" for "max".
590 SELECT DISTINCT proname, oprname
591 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
592 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
595 -- Check datatypes match
597 SELECT a.aggfnoid::oid, o.oid
598 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
599 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
600 (oprkind != 'b' OR oprresult != 'boolean'::regtype
601 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
603 -- Check operator is a suitable btree opfamily member
605 SELECT a.aggfnoid::oid, o.oid
606 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
607 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
608 NOT EXISTS(SELECT 1 FROM pg_amop
609 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
611 AND amoplefttype = o.oprleft
612 AND amoprighttype = o.oprright);
614 -- Check correspondence of btree strategies and names
616 SELECT DISTINCT proname, oprname, amopstrategy
617 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
619 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
621 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
624 -- **************** pg_opfamily ****************
626 -- Look for illegal values in pg_opfamily fields
629 FROM pg_opfamily as p1
630 WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
632 -- **************** pg_opclass ****************
634 -- Look for illegal values in pg_opclass fields
637 FROM pg_opclass AS p1
638 WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
641 -- opcmethod must match owning opfamily's opfmethod
643 SELECT p1.oid, p2.oid
644 FROM pg_opclass AS p1, pg_opfamily AS p2
645 WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
647 -- There should not be multiple entries in pg_opclass with opcdefault true
648 -- and the same opcmethod/opcintype combination.
650 SELECT p1.oid, p2.oid
651 FROM pg_opclass AS p1, pg_opclass AS p2
652 WHERE p1.oid != p2.oid AND
653 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
654 p1.opcdefault AND p2.opcdefault;
656 -- **************** pg_amop ****************
658 -- Look for illegal values in pg_amop fields
660 SELECT p1.amopfamily, p1.amopstrategy
662 WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
663 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
665 -- amoplefttype/amoprighttype must match the operator
667 SELECT p1.oid, p2.oid
668 FROM pg_amop AS p1, pg_operator AS p2
669 WHERE p1.amopopr = p2.oid AND NOT
670 (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);
672 -- amopmethod must match owning opfamily's opfmethod
674 SELECT p1.oid, p2.oid
675 FROM pg_amop AS p1, pg_opfamily AS p2
676 WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
678 -- Cross-check amopstrategy index against parent AM
680 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
681 FROM pg_amop AS p1, pg_am AS p2
682 WHERE p1.amopmethod = p2.oid AND
683 p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
685 -- Detect missing pg_amop entries: should have as many strategy operators
686 -- as AM expects for each datatype combination supported by the opfamily.
687 -- We can't check this for AMs with variable strategy sets.
689 SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
690 FROM pg_am AS p1, pg_amop AS p2
691 WHERE p2.amopmethod = p1.oid AND
692 p1.amstrategies <> 0 AND
693 p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
694 WHERE p3.amopfamily = p2.amopfamily AND
695 p3.amoplefttype = p2.amoplefttype AND
696 p3.amoprighttype = p2.amoprighttype);
698 -- Check that amopopr points at a reasonable-looking operator, ie a binary
699 -- operator yielding boolean.
701 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
702 FROM pg_amop AS p1, pg_operator AS p2
703 WHERE p1.amopopr = p2.oid AND
704 (p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
706 -- Make a list of all the distinct operator names being used in particular
707 -- strategy slots. This is a bit hokey, since the list might need to change
708 -- in future releases, but it's an effective way of spotting mistakes such as
709 -- swapping two operators within a family.
711 SELECT DISTINCT amopmethod, amopstrategy, oprname
712 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
715 -- Check that all operators linked to by opclass entries have selectivity
716 -- estimators. This is not absolutely required, but it seems a reasonable
717 -- thing to insist on for all standard datatypes.
719 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
720 FROM pg_amop AS p1, pg_operator AS p2
721 WHERE p1.amopopr = p2.oid AND
722 (p2.oprrest = 0 OR p2.oprjoin = 0);
724 -- Check that each opclass in an opfamily has associated operators, that is
725 -- ones whose oprleft matches opcintype (possibly by coercion).
727 SELECT p1.opcname, p1.opcfamily
728 FROM pg_opclass AS p1
729 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
730 WHERE p2.amopfamily = p1.opcfamily
731 AND binary_coercible(p1.opcintype, p2.amoplefttype));
733 -- Operators that are primary members of opclasses must be immutable (else
734 -- it suggests that the index ordering isn't fixed). Operators that are
735 -- cross-type members need only be stable, since they are just shorthands
736 -- for index probe queries.
738 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
739 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
740 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
741 p1.amoplefttype = p1.amoprighttype AND
742 p3.provolatile != 'i';
744 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
745 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
746 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
747 p1.amoplefttype != p1.amoprighttype AND
748 p3.provolatile = 'v';
750 -- Multiple-datatype btree opfamilies should provide closed sets of equality
751 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
752 -- should also provide int2 = int8 (and commutators of all these). This is
753 -- important because the planner tries to deduce additional qual clauses from
754 -- transitivity of mergejoinable operators. If there are clauses
755 -- int2var = int4var and int4var = int8var, the planner will want to deduce
756 -- int2var = int8var ... so there should be a way to represent that. While
757 -- a missing cross-type operator is now only an efficiency loss rather than
758 -- an error condition, it still seems reasonable to insist that all built-in
759 -- opfamilies be complete.
761 -- check commutative closure
762 SELECT p1.amoplefttype, p1.amoprighttype
764 WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
765 p1.amopstrategy = 3 AND
766 p1.amoplefttype != p1.amoprighttype AND
767 NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
768 p2.amopfamily = p1.amopfamily AND
769 p2.amoplefttype = p1.amoprighttype AND
770 p2.amoprighttype = p1.amoplefttype AND
771 p2.amopstrategy = 3);
773 -- check transitive closure
774 SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
775 FROM pg_amop AS p1, pg_amop AS p2
776 WHERE p1.amopfamily = p2.amopfamily AND
777 p1.amoprighttype = p2.amoplefttype AND
778 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
779 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
780 p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
781 p1.amoplefttype != p1.amoprighttype AND
782 p2.amoplefttype != p2.amoprighttype AND
783 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
784 p3.amopfamily = p1.amopfamily AND
785 p3.amoplefttype = p1.amoplefttype AND
786 p3.amoprighttype = p2.amoprighttype AND
787 p3.amopstrategy = 3);
789 -- We also expect that built-in multiple-datatype hash opfamilies provide
790 -- complete sets of cross-type operators. Again, this isn't required, but
791 -- it is reasonable to expect it for built-in opfamilies.
793 -- if same family has x=x and y=y, it should have x=y
794 SELECT p1.amoplefttype, p2.amoplefttype
795 FROM pg_amop AS p1, pg_amop AS p2
796 WHERE p1.amopfamily = p2.amopfamily AND
797 p1.amoplefttype = p1.amoprighttype AND
798 p2.amoplefttype = p2.amoprighttype AND
799 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
800 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
801 p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
802 p1.amoplefttype != p2.amoplefttype AND
803 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
804 p3.amopfamily = p1.amopfamily AND
805 p3.amoplefttype = p1.amoplefttype AND
806 p3.amoprighttype = p2.amoplefttype AND
807 p3.amopstrategy = 1);
810 -- **************** pg_amproc ****************
812 -- Look for illegal values in pg_amproc fields
814 SELECT p1.amprocfamily, p1.amprocnum
816 WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
817 OR p1.amprocnum < 1 OR p1.amproc = 0;
819 -- Cross-check amprocnum index against parent AM
821 SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
822 FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
823 WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
824 p1.amprocnum > p2.amsupport;
826 -- Detect missing pg_amproc entries: should have as many support functions
827 -- as AM expects for each datatype combination supported by the opfamily.
828 -- GIN is a special case because it has an optional support function.
830 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
831 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
832 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
833 p1.amname <> 'gin' AND
834 p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
835 WHERE p4.amprocfamily = p2.oid AND
836 p4.amproclefttype = p3.amproclefttype AND
837 p4.amprocrighttype = p3.amprocrighttype);
839 -- Similar check for GIN, allowing one optional proc
841 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
842 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
843 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
844 p1.amname = 'gin' AND
845 p1.amsupport - 1 > (SELECT count(*) FROM pg_amproc AS p4
846 WHERE p4.amprocfamily = p2.oid AND
847 p4.amproclefttype = p3.amproclefttype AND
848 p4.amprocrighttype = p3.amprocrighttype);
850 -- Also, check if there are any pg_opclass entries that don't seem to have
851 -- pg_amproc support. Again, GIN has to be checked separately.
853 SELECT amname, opcname, count(*)
854 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
855 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
856 amproclefttype = amprocrighttype AND amproclefttype = opcintype
857 WHERE am.amname <> 'gin'
858 GROUP BY amname, amsupport, opcname, amprocfamily
859 HAVING count(*) != amsupport OR amprocfamily IS NULL;
861 SELECT amname, opcname, count(*)
862 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
863 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
864 amproclefttype = amprocrighttype AND amproclefttype = opcintype
865 WHERE am.amname = 'gin'
866 GROUP BY amname, amsupport, opcname, amprocfamily
867 HAVING count(*) < amsupport - 1 OR amprocfamily IS NULL;
869 -- Unfortunately, we can't check the amproc link very well because the
870 -- signature of the function may be different for different support routines
871 -- or different base data types.
872 -- We can check that all the referenced instances of the same support
873 -- routine number take the same number of parameters, but that's about it
874 -- for a general check...
876 SELECT p1.amprocfamily, p1.amprocnum,
879 p4.amprocfamily, p4.amprocnum,
882 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
883 pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
884 WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
885 p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
886 p1.amproc = p2.oid AND p4.amproc = p5.oid AND
887 (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
889 -- For btree, though, we can do better since we know the support routines
890 -- must be of the form cmp(lefttype, righttype) returns int4.
892 SELECT p1.amprocfamily, p1.amprocnum,
895 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
896 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
897 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
900 OR prorettype != 'int4'::regtype
902 OR proargtypes[0] != amproclefttype
903 OR proargtypes[1] != amprocrighttype);
905 -- For hash we can also do a little better: the support routines must be
906 -- of the form hash(lefttype) returns int4. There are several cases where
907 -- we cheat and use a hash function that is physically compatible with the
908 -- datatype even though there's no cast, so this check does find a small
909 -- number of entries.
911 SELECT p1.amprocfamily, p1.amprocnum, p2.proname, p3.opfname
912 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
913 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
914 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
917 OR prorettype != 'int4'::regtype
919 OR NOT physically_coercible(amproclefttype, proargtypes[0])
920 OR amproclefttype != amprocrighttype)
923 -- Support routines that are primary members of opfamilies must be immutable
924 -- (else it suggests that the index ordering isn't fixed). But cross-type
925 -- members need only be stable, since they are just shorthands
926 -- for index probe queries.
928 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
929 FROM pg_amproc AS p1, pg_proc AS p2
930 WHERE p1.amproc = p2.oid AND
931 p1.amproclefttype = p1.amprocrighttype AND
932 p2.provolatile != 'i';
934 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
935 FROM pg_amproc AS p1, pg_proc AS p2
936 WHERE p1.amproc = p2.oid AND
937 p1.amproclefttype != p1.amprocrighttype AND
938 p2.provolatile = 'v';