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 -- Check that there are not aggregates with the same name and different
625 -- numbers of arguments. While not technically wrong, we have a project policy
626 -- to avoid this because it opens the door for confusion in connection with
627 -- ORDER BY: novices frequently put the ORDER BY in the wrong place.
628 -- See the fate of the single-argument form of string_agg() for history.
629 -- The only aggregates that should show up here are count(x) and count(*).
631 SELECT p1.oid::regprocedure, p2.oid::regprocedure
632 FROM pg_proc AS p1, pg_proc AS p2
633 WHERE p1.oid < p2.oid AND p1.proname = p2.proname AND
634 p1.proisagg AND p2.proisagg AND
635 array_dims(p1.proargtypes) != array_dims(p2.proargtypes)
638 -- For the same reason, aggregates with default arguments are no good.
642 WHERE proisagg AND proargdefaults IS NOT NULL;
644 -- **************** pg_opfamily ****************
646 -- Look for illegal values in pg_opfamily fields
649 FROM pg_opfamily as p1
650 WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
652 -- **************** pg_opclass ****************
654 -- Look for illegal values in pg_opclass fields
657 FROM pg_opclass AS p1
658 WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
661 -- opcmethod must match owning opfamily's opfmethod
663 SELECT p1.oid, p2.oid
664 FROM pg_opclass AS p1, pg_opfamily AS p2
665 WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
667 -- There should not be multiple entries in pg_opclass with opcdefault true
668 -- and the same opcmethod/opcintype combination.
670 SELECT p1.oid, p2.oid
671 FROM pg_opclass AS p1, pg_opclass AS p2
672 WHERE p1.oid != p2.oid AND
673 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
674 p1.opcdefault AND p2.opcdefault;
676 -- **************** pg_amop ****************
678 -- Look for illegal values in pg_amop fields
680 SELECT p1.amopfamily, p1.amopstrategy
682 WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
683 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
685 -- amoplefttype/amoprighttype must match the operator
687 SELECT p1.oid, p2.oid
688 FROM pg_amop AS p1, pg_operator AS p2
689 WHERE p1.amopopr = p2.oid AND NOT
690 (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);
692 -- amopmethod must match owning opfamily's opfmethod
694 SELECT p1.oid, p2.oid
695 FROM pg_amop AS p1, pg_opfamily AS p2
696 WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
698 -- Cross-check amopstrategy index against parent AM
700 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
701 FROM pg_amop AS p1, pg_am AS p2
702 WHERE p1.amopmethod = p2.oid AND
703 p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
705 -- Detect missing pg_amop entries: should have as many strategy operators
706 -- as AM expects for each datatype combination supported by the opfamily.
707 -- We can't check this for AMs with variable strategy sets.
709 SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
710 FROM pg_am AS p1, pg_amop AS p2
711 WHERE p2.amopmethod = p1.oid AND
712 p1.amstrategies <> 0 AND
713 p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
714 WHERE p3.amopfamily = p2.amopfamily AND
715 p3.amoplefttype = p2.amoplefttype AND
716 p3.amoprighttype = p2.amoprighttype);
718 -- Check that amopopr points at a reasonable-looking operator, ie a binary
719 -- operator yielding boolean.
721 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
722 FROM pg_amop AS p1, pg_operator AS p2
723 WHERE p1.amopopr = p2.oid AND
724 (p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
726 -- Make a list of all the distinct operator names being used in particular
727 -- strategy slots. This is a bit hokey, since the list might need to change
728 -- in future releases, but it's an effective way of spotting mistakes such as
729 -- swapping two operators within a family.
731 SELECT DISTINCT amopmethod, amopstrategy, oprname
732 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
735 -- Check that all operators linked to by opclass entries have selectivity
736 -- estimators. This is not absolutely required, but it seems a reasonable
737 -- thing to insist on for all standard datatypes.
739 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
740 FROM pg_amop AS p1, pg_operator AS p2
741 WHERE p1.amopopr = p2.oid AND
742 (p2.oprrest = 0 OR p2.oprjoin = 0);
744 -- Check that each opclass in an opfamily has associated operators, that is
745 -- ones whose oprleft matches opcintype (possibly by coercion).
747 SELECT p1.opcname, p1.opcfamily
748 FROM pg_opclass AS p1
749 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
750 WHERE p2.amopfamily = p1.opcfamily
751 AND binary_coercible(p1.opcintype, p2.amoplefttype));
753 -- Operators that are primary members of opclasses must be immutable (else
754 -- it suggests that the index ordering isn't fixed). Operators that are
755 -- cross-type members need only be stable, since they are just shorthands
756 -- for index probe queries.
758 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
759 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
760 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
761 p1.amoplefttype = p1.amoprighttype AND
762 p3.provolatile != 'i';
764 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
765 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
766 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
767 p1.amoplefttype != p1.amoprighttype AND
768 p3.provolatile = 'v';
770 -- Multiple-datatype btree opfamilies should provide closed sets of equality
771 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
772 -- should also provide int2 = int8 (and commutators of all these). This is
773 -- important because the planner tries to deduce additional qual clauses from
774 -- transitivity of mergejoinable operators. If there are clauses
775 -- int2var = int4var and int4var = int8var, the planner will want to deduce
776 -- int2var = int8var ... so there should be a way to represent that. While
777 -- a missing cross-type operator is now only an efficiency loss rather than
778 -- an error condition, it still seems reasonable to insist that all built-in
779 -- opfamilies be complete.
781 -- check commutative closure
782 SELECT p1.amoplefttype, p1.amoprighttype
784 WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
785 p1.amopstrategy = 3 AND
786 p1.amoplefttype != p1.amoprighttype AND
787 NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
788 p2.amopfamily = p1.amopfamily AND
789 p2.amoplefttype = p1.amoprighttype AND
790 p2.amoprighttype = p1.amoplefttype AND
791 p2.amopstrategy = 3);
793 -- check transitive closure
794 SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
795 FROM pg_amop AS p1, pg_amop AS p2
796 WHERE p1.amopfamily = p2.amopfamily AND
797 p1.amoprighttype = p2.amoplefttype AND
798 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
799 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
800 p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
801 p1.amoplefttype != p1.amoprighttype AND
802 p2.amoplefttype != p2.amoprighttype 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.amoprighttype AND
807 p3.amopstrategy = 3);
809 -- We also expect that built-in multiple-datatype hash opfamilies provide
810 -- complete sets of cross-type operators. Again, this isn't required, but
811 -- it is reasonable to expect it for built-in opfamilies.
813 -- if same family has x=x and y=y, it should have x=y
814 SELECT p1.amoplefttype, p2.amoplefttype
815 FROM pg_amop AS p1, pg_amop AS p2
816 WHERE p1.amopfamily = p2.amopfamily AND
817 p1.amoplefttype = p1.amoprighttype AND
818 p2.amoplefttype = p2.amoprighttype AND
819 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
820 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
821 p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
822 p1.amoplefttype != p2.amoplefttype AND
823 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
824 p3.amopfamily = p1.amopfamily AND
825 p3.amoplefttype = p1.amoplefttype AND
826 p3.amoprighttype = p2.amoplefttype AND
827 p3.amopstrategy = 1);
830 -- **************** pg_amproc ****************
832 -- Look for illegal values in pg_amproc fields
834 SELECT p1.amprocfamily, p1.amprocnum
836 WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
837 OR p1.amprocnum < 1 OR p1.amproc = 0;
839 -- Cross-check amprocnum index against parent AM
841 SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
842 FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
843 WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
844 p1.amprocnum > p2.amsupport;
846 -- Detect missing pg_amproc entries: should have as many support functions
847 -- as AM expects for each datatype combination supported by the opfamily.
848 -- GIN is a special case because it has an optional support function.
850 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
851 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
852 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
853 p1.amname <> 'gin' AND
854 p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
855 WHERE p4.amprocfamily = p2.oid AND
856 p4.amproclefttype = p3.amproclefttype AND
857 p4.amprocrighttype = p3.amprocrighttype);
859 -- Similar check for GIN, allowing one optional proc
861 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
862 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
863 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
864 p1.amname = 'gin' AND
865 p1.amsupport - 1 > (SELECT count(*) FROM pg_amproc AS p4
866 WHERE p4.amprocfamily = p2.oid AND
867 p4.amproclefttype = p3.amproclefttype AND
868 p4.amprocrighttype = p3.amprocrighttype);
870 -- Also, check if there are any pg_opclass entries that don't seem to have
871 -- pg_amproc support. Again, GIN has to be checked separately.
873 SELECT amname, opcname, count(*)
874 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
875 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
876 amproclefttype = amprocrighttype AND amproclefttype = opcintype
877 WHERE am.amname <> 'gin'
878 GROUP BY amname, amsupport, opcname, amprocfamily
879 HAVING count(*) != amsupport OR amprocfamily IS NULL;
881 SELECT amname, opcname, count(*)
882 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
883 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
884 amproclefttype = amprocrighttype AND amproclefttype = opcintype
885 WHERE am.amname = 'gin'
886 GROUP BY amname, amsupport, opcname, amprocfamily
887 HAVING count(*) < amsupport - 1 OR amprocfamily IS NULL;
889 -- Unfortunately, we can't check the amproc link very well because the
890 -- signature of the function may be different for different support routines
891 -- or different base data types.
892 -- We can check that all the referenced instances of the same support
893 -- routine number take the same number of parameters, but that's about it
894 -- for a general check...
896 SELECT p1.amprocfamily, p1.amprocnum,
899 p4.amprocfamily, p4.amprocnum,
902 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
903 pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
904 WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
905 p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
906 p1.amproc = p2.oid AND p4.amproc = p5.oid AND
907 (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
909 -- For btree, though, we can do better since we know the support routines
910 -- must be of the form cmp(lefttype, righttype) returns int4.
912 SELECT p1.amprocfamily, p1.amprocnum,
915 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
916 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
917 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
920 OR prorettype != 'int4'::regtype
922 OR proargtypes[0] != amproclefttype
923 OR proargtypes[1] != amprocrighttype);
925 -- For hash we can also do a little better: the support routines must be
926 -- of the form hash(lefttype) returns int4. There are several cases where
927 -- we cheat and use a hash function that is physically compatible with the
928 -- datatype even though there's no cast, so this check does find a small
929 -- number of entries.
931 SELECT p1.amprocfamily, p1.amprocnum, p2.proname, p3.opfname
932 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
933 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
934 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
937 OR prorettype != 'int4'::regtype
939 OR NOT physically_coercible(amproclefttype, proargtypes[0])
940 OR amproclefttype != amprocrighttype)
943 -- Support routines that are primary members of opfamilies must be immutable
944 -- (else it suggests that the index ordering isn't fixed). But cross-type
945 -- members need only be stable, since they are just shorthands
946 -- for index probe queries.
948 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
949 FROM pg_amproc AS p1, pg_proc AS p2
950 WHERE p1.amproc = p2.oid AND
951 p1.amproclefttype = p1.amprocrighttype AND
952 p2.provolatile != 'i';
954 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
955 FROM pg_amproc AS p1, pg_proc AS p2
956 WHERE p1.amproc = p2.oid AND
957 p1.amproclefttype != p1.amprocrighttype AND
958 p2.provolatile = 'v';