3 -- Sanity checks for common errors in making operator/procedure system tables:
4 -- pg_operator, pg_proc, pg_cast, pg_aggregate, pg_am, pg_amop, pg_amproc, pg_opclass.
6 -- None of the SELECTs here should ever find any matching entries,
7 -- so the expected output is easy to maintain ;-).
8 -- A test failure indicates someone messed up an entry in the system tables.
10 -- NB: we assume the oidjoins test will have caught any dangling links,
11 -- that is OID or REGPROC fields that are not zero and do not match some
12 -- row in the linked-to table. However, if we want to enforce that a link
13 -- field can't be 0, we have to check it here.
15 -- NB: run this test earlier than the create_operator test, because
16 -- that test creates some bogus operators...
19 -- Helper functions to deal with cases where binary-coercible matches are
22 -- This should match IsBinaryCoercible() in parse_coerce.c.
23 create function binary_coercible(oid, oid) returns bool as
25 EXISTS(select 1 from pg_cast where
26 castsource = $1 and casttarget = $2 and
27 castfunc = 0 and castcontext = ''i'') OR
28 ( EXISTS(select 1 from pg_type source where
29 source.oid = $1 and source.typelem != 0 )
31 EXISTS(select 1 from pg_type target where
32 target.oid = $2 and target.typname = ''anyarray'' ) )'
35 -- This one ignores castcontext, so it considers only physical equivalence
36 -- and not whether the coercion can be invoked implicitly.
37 create function physically_coercible(oid, oid) returns bool as
39 EXISTS(select 1 from pg_cast where
40 castsource = $1 and casttarget = $2 and
44 -- **************** pg_proc ****************
46 -- Look for illegal values in pg_proc fields.
48 SELECT p1.oid, p1.proname
50 WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
52 array_lower(p1.proargtypes, 1) != 0 OR
53 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR
54 0::oid = ANY (p1.proargtypes);
56 -- Look for conflicting proc definitions (same names and input datatypes).
57 -- (This test should be dead code now that we have the unique index
58 -- pg_proc_proname_narg_type_index, but I'll leave it in anyway.)
60 SELECT p1.oid, p1.proname, p2.oid, p2.proname
61 FROM pg_proc AS p1, pg_proc AS p2
62 WHERE p1.oid != p2.oid AND
63 p1.proname = p2.proname AND
64 p1.pronargs = p2.pronargs AND
65 p1.proargtypes = p2.proargtypes;
67 -- Considering only built-in procs (prolang = 12), look for multiple uses
68 -- of the same internal function (ie, matching prosrc fields). It's OK to
69 -- have several entries with different pronames for the same internal function,
70 -- but conflicts in the number of arguments and other critical items should
73 SELECT p1.oid, p1.proname, p2.oid, p2.proname
74 FROM pg_proc AS p1, pg_proc AS p2
75 WHERE p1.oid != p2.oid AND
76 p1.prosrc = p2.prosrc AND
77 p1.prolang = 12 AND p2.prolang = 12 AND
78 (p1.prolang != p2.prolang OR
79 p1.proisagg != p2.proisagg OR
80 p1.prosecdef != p2.prosecdef OR
81 p1.proisstrict != p2.proisstrict OR
82 p1.proretset != p2.proretset OR
83 p1.provolatile != p2.provolatile OR
84 p1.pronargs != p2.pronargs);
86 -- Look for uses of different type OIDs in the argument/result type fields
87 -- for different aliases of the same built-in function.
88 -- This indicates that the types are being presumed to be binary-equivalent,
89 -- or that the built-in function is prepared to deal with different types.
90 -- That's not wrong, necessarily, but we make lists of all the types being
91 -- so treated. Note that the expected output of this part of the test will
92 -- need to be modified whenever new pairs of types are made binary-equivalent,
93 -- or when new polymorphic built-in functions are added!
94 -- Note: ignore aggregate functions here, since they all point to the same
95 -- dummy built-in function.
97 SELECT DISTINCT p1.prorettype, p2.prorettype
98 FROM pg_proc AS p1, pg_proc AS p2
99 WHERE p1.oid != p2.oid AND
100 p1.prosrc = p2.prosrc AND
101 p1.prolang = 12 AND p2.prolang = 12 AND
102 NOT p1.proisagg AND NOT p2.proisagg AND
103 (p1.prorettype < p2.prorettype);
105 SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
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 NOT p1.proisagg AND NOT p2.proisagg AND
111 (p1.proargtypes[0] < p2.proargtypes[0]);
113 SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
114 FROM pg_proc AS p1, pg_proc AS p2
115 WHERE p1.oid != p2.oid AND
116 p1.prosrc = p2.prosrc AND
117 p1.prolang = 12 AND p2.prolang = 12 AND
118 NOT p1.proisagg AND NOT p2.proisagg AND
119 (p1.proargtypes[1] < p2.proargtypes[1]);
121 SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
122 FROM pg_proc AS p1, pg_proc AS p2
123 WHERE p1.oid != p2.oid AND
124 p1.prosrc = p2.prosrc AND
125 p1.prolang = 12 AND p2.prolang = 12 AND
126 NOT p1.proisagg AND NOT p2.proisagg AND
127 (p1.proargtypes[2] < p2.proargtypes[2]);
129 SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
130 FROM pg_proc AS p1, pg_proc AS p2
131 WHERE p1.oid != p2.oid AND
132 p1.prosrc = p2.prosrc AND
133 p1.prolang = 12 AND p2.prolang = 12 AND
134 NOT p1.proisagg AND NOT p2.proisagg AND
135 (p1.proargtypes[3] < p2.proargtypes[3]);
137 SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
138 FROM pg_proc AS p1, pg_proc AS p2
139 WHERE p1.oid != p2.oid AND
140 p1.prosrc = p2.prosrc AND
141 p1.prolang = 12 AND p2.prolang = 12 AND
142 NOT p1.proisagg AND NOT p2.proisagg AND
143 (p1.proargtypes[4] < p2.proargtypes[4]);
145 SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
146 FROM pg_proc AS p1, pg_proc AS p2
147 WHERE p1.oid != p2.oid AND
148 p1.prosrc = p2.prosrc AND
149 p1.prolang = 12 AND p2.prolang = 12 AND
150 NOT p1.proisagg AND NOT p2.proisagg AND
151 (p1.proargtypes[5] < p2.proargtypes[5]);
153 SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
154 FROM pg_proc AS p1, pg_proc AS p2
155 WHERE p1.oid != p2.oid AND
156 p1.prosrc = p2.prosrc AND
157 p1.prolang = 12 AND p2.prolang = 12 AND
158 NOT p1.proisagg AND NOT p2.proisagg AND
159 (p1.proargtypes[6] < p2.proargtypes[6]);
161 SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
162 FROM pg_proc AS p1, pg_proc AS p2
163 WHERE p1.oid != p2.oid AND
164 p1.prosrc = p2.prosrc AND
165 p1.prolang = 12 AND p2.prolang = 12 AND
166 NOT p1.proisagg AND NOT p2.proisagg AND
167 (p1.proargtypes[7] < p2.proargtypes[7]);
169 -- Look for functions that return type "internal" and do not have any
170 -- "internal" argument. Such a function would be a security hole since
171 -- it might be used to call an internal function from an SQL command.
172 -- As of 7.3 this query should find only internal_in.
174 SELECT p1.oid, p1.proname
176 WHERE p1.prorettype = 'internal'::regtype AND NOT
177 'internal'::regtype = ANY (p1.proargtypes);
180 -- **************** pg_cast ****************
182 -- Catch bogus values in pg_cast columns (other than cases detected by
187 WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i');
189 -- Look for casts to/from the same type that aren't length coercion functions.
190 -- (We assume they are length coercions if they take multiple arguments.)
191 -- Such entries are not necessarily harmful, but they are useless.
195 WHERE castsource = casttarget AND castfunc = 0;
198 FROM pg_cast c, pg_proc p
199 WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
201 -- Look for cast functions that don't have the right signature. The
202 -- argument and result types in pg_proc must be the same as, or binary
203 -- compatible with, what it says in pg_cast.
204 -- As a special case, we allow casts from CHAR(n) that use functions
205 -- declared to take TEXT. This does not pass the binary-coercibility test
206 -- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
207 -- are the same, so long as the function is one that ignores trailing blanks.
210 FROM pg_cast c, pg_proc p
211 WHERE c.castfunc = p.oid AND
212 (p.pronargs < 1 OR p.pronargs > 3
213 OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
214 OR (c.castsource = 'character'::regtype AND
215 p.proargtypes[0] = 'text'::regtype))
216 OR NOT binary_coercible(p.prorettype, c.casttarget));
219 FROM pg_cast c, pg_proc p
220 WHERE c.castfunc = p.oid AND
221 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
222 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
224 -- Look for binary compatible casts that do not have the reverse
225 -- direction registered as well, or where the reverse direction is not
226 -- also binary compatible. This is legal, but usually not intended.
228 -- As of 7.4, this finds the casts from text and varchar to bpchar, because
229 -- those are binary-compatible while the reverse way goes through rtrim().
231 -- As of 8.2, this finds the cast from cidr to inet, because that is a
232 -- trivial binary coercion while the other way goes through inet_to_cidr().
236 WHERE c.castfunc = 0 AND
237 NOT EXISTS (SELECT 1 FROM pg_cast k
238 WHERE k.castfunc = 0 AND
239 k.castsource = c.casttarget AND
240 k.casttarget = c.castsource);
242 -- **************** pg_operator ****************
244 -- Look for illegal values in pg_operator fields.
246 SELECT p1.oid, p1.oprname
247 FROM pg_operator as p1
248 WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
249 p1.oprresult = 0 OR p1.oprcode = 0;
251 -- Look for missing or unwanted operand types
253 SELECT p1.oid, p1.oprname
254 FROM pg_operator as p1
255 WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
256 (p1.oprleft != 0 and p1.oprkind = 'l') OR
257 (p1.oprright = 0 and p1.oprkind != 'r') OR
258 (p1.oprright != 0 and p1.oprkind = 'r');
260 -- Look for conflicting operator definitions (same names and input datatypes).
262 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
263 FROM pg_operator AS p1, pg_operator AS p2
264 WHERE p1.oid != p2.oid AND
265 p1.oprname = p2.oprname AND
266 p1.oprkind = p2.oprkind AND
267 p1.oprleft = p2.oprleft AND
268 p1.oprright = p2.oprright;
270 -- Look for commutative operators that don't commute.
271 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
272 -- We expect that B will always say that B.oprcom = A as well; that's not
273 -- inherently essential, but it would be inefficient not to mark it so.
275 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
276 FROM pg_operator AS p1, pg_operator AS p2
277 WHERE p1.oprcom = p2.oid AND
278 (p1.oprkind != 'b' OR
279 p1.oprleft != p2.oprright OR
280 p1.oprright != p2.oprleft OR
281 p1.oprresult != p2.oprresult OR
282 p1.oid != p2.oprcom);
284 -- Look for negatory operators that don't agree.
285 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
286 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
287 -- single-operand operators.
288 -- We expect that B will always say that B.oprnegate = A as well; that's not
289 -- inherently essential, but it would be inefficient not to mark it so.
290 -- Also, A and B had better not be the same operator.
292 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
293 FROM pg_operator AS p1, pg_operator AS p2
294 WHERE p1.oprnegate = p2.oid AND
295 (p1.oprkind != p2.oprkind OR
296 p1.oprleft != p2.oprleft OR
297 p1.oprright != p2.oprright OR
298 p1.oprresult != 'bool'::regtype OR
299 p2.oprresult != 'bool'::regtype OR
300 p1.oid != p2.oprnegate OR
303 -- Look for mergejoin operators that don't match their links.
304 -- An lsortop/rsortop link leads from an '=' operator to the
305 -- sort operator ('<' operator) that's appropriate for
306 -- its left-side or right-side data type.
307 -- An ltcmpop/gtcmpop link leads from an '=' operator to the
308 -- '<' or '>' operator of the same input datatypes.
309 -- (If the '=' operator has identical L and R input datatypes,
310 -- then lsortop, rsortop, and ltcmpop are all the same operator.)
312 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
313 FROM pg_operator AS p1, pg_operator AS p2
314 WHERE p1.oprlsortop = p2.oid AND
315 (p1.oprname NOT IN ('=', '~=~') OR p2.oprname NOT IN ('<', '~<~') OR
316 p1.oprkind != 'b' OR p2.oprkind != 'b' OR
317 p1.oprleft != p2.oprleft OR
318 p1.oprleft != p2.oprright OR
319 p1.oprresult != 'bool'::regtype OR
320 p2.oprresult != 'bool'::regtype);
322 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
323 FROM pg_operator AS p1, pg_operator AS p2
324 WHERE p1.oprrsortop = p2.oid AND
325 (p1.oprname NOT IN ('=', '~=~') OR p2.oprname NOT IN ('<', '~<~') OR
326 p1.oprkind != 'b' OR p2.oprkind != 'b' OR
327 p1.oprright != p2.oprleft OR
328 p1.oprright != p2.oprright OR
329 p1.oprresult != 'bool'::regtype OR
330 p2.oprresult != 'bool'::regtype);
332 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
333 FROM pg_operator AS p1, pg_operator AS p2
334 WHERE p1.oprltcmpop = p2.oid AND
335 (p1.oprname NOT IN ('=', '~=~') OR p2.oprname NOT IN ('<', '~<~') OR
336 p1.oprkind != 'b' OR p2.oprkind != 'b' OR
337 p1.oprleft != p2.oprleft OR
338 p1.oprright != p2.oprright OR
339 p1.oprresult != 'bool'::regtype OR
340 p2.oprresult != 'bool'::regtype);
342 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
343 FROM pg_operator AS p1, pg_operator AS p2
344 WHERE p1.oprgtcmpop = p2.oid AND
345 (p1.oprname NOT IN ('=', '~=~') OR p2.oprname NOT IN ('>', '~>~') OR
346 p1.oprkind != 'b' OR p2.oprkind != 'b' OR
347 p1.oprleft != p2.oprleft OR
348 p1.oprright != p2.oprright OR
349 p1.oprresult != 'bool'::regtype OR
350 p2.oprresult != 'bool'::regtype);
352 -- Make sure all four links are specified if any are.
354 SELECT p1.oid, p1.oprcode
355 FROM pg_operator AS p1
356 WHERE NOT ((oprlsortop = 0 AND oprrsortop = 0 AND
357 oprltcmpop = 0 AND oprgtcmpop = 0) OR
358 (oprlsortop != 0 AND oprrsortop != 0 AND
359 oprltcmpop != 0 AND oprgtcmpop != 0));
361 -- A mergejoinable = operator must have a commutator (usually itself).
363 SELECT p1.oid, p1.oprname FROM pg_operator AS p1
364 WHERE p1.oprlsortop != 0 AND
367 -- Mergejoinable operators across datatypes must come in closed sets, that
368 -- is if you provide int2 = int4 and int4 = int8 then you must also provide
369 -- int2 = int8 (and commutators of all these). This is necessary because
370 -- the planner tries to deduce additional qual clauses from transitivity
371 -- of mergejoinable operators. If there are clauses int2var = int4var and
372 -- int4var = int8var, the planner will deduce int2var = int8var ... and it
373 -- had better have a way to represent it.
375 SELECT p1.oid, p2.oid FROM pg_operator AS p1, pg_operator AS p2
376 WHERE p1.oprlsortop != p1.oprrsortop AND
377 p1.oprrsortop = p2.oprlsortop AND
378 p2.oprlsortop != p2.oprrsortop AND
379 NOT EXISTS (SELECT 1 FROM pg_operator p3 WHERE
380 p3.oprlsortop = p1.oprlsortop AND p3.oprrsortop = p2.oprrsortop);
383 -- Hashing only works on simple equality operators "type = sametype",
384 -- since the hash itself depends on the bitwise representation of the type.
385 -- Check that allegedly hashable operators look like they might be "=".
387 SELECT p1.oid, p1.oprname
388 FROM pg_operator AS p1
389 WHERE p1.oprcanhash AND NOT
390 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND
391 p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND
394 -- In 6.5 we accepted hashable array equality operators when the array element
395 -- type is hashable. However, what we actually need to make hashjoin work on
396 -- an array is a hashable element type *and* no padding between elements in
397 -- the array storage (or, perhaps, guaranteed-zero padding). Currently,
398 -- since the padding code in arrayfuncs.c is pretty bogus, it seems safest
399 -- to just forbid hashjoin on array equality ops.
400 -- This should be reconsidered someday.
402 -- -- Look for array equality operators that are hashable when the underlying
403 -- -- type is not, or vice versa. This is presumably bogus.
405 -- SELECT p1.oid, p1.oprcanhash, p2.oid, p2.oprcanhash, t1.typname, t2.typname
406 -- FROM pg_operator AS p1, pg_operator AS p2, pg_type AS t1, pg_type AS t2
407 -- WHERE p1.oprname = '=' AND p1.oprleft = p1.oprright AND
408 -- p2.oprname = '=' AND p2.oprleft = p2.oprright AND
409 -- p1.oprleft = t1.oid AND p2.oprleft = t2.oid AND t1.typelem = t2.oid AND
410 -- p1.oprcanhash != p2.oprcanhash;
412 -- Substitute check: forbid hashable array ops, period.
413 SELECT p1.oid, p1.oprname
414 FROM pg_operator AS p1, pg_proc AS p2
415 WHERE p1.oprcanhash AND p1.oprcode = p2.oid AND p2.proname = 'array_eq';
417 -- Hashable operators should appear as members of hash index opclasses.
419 SELECT p1.oid, p1.oprname
420 FROM pg_operator AS p1
421 WHERE p1.oprcanhash AND NOT EXISTS
422 (SELECT 1 FROM pg_opclass op JOIN pg_amop p ON op.oid = amopclaid
423 WHERE opcamid = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
428 SELECT p1.oid, p1.oprname, op.opcname
429 FROM pg_operator AS p1, pg_opclass op, pg_amop p
430 WHERE amopopr = p1.oid AND amopclaid = op.oid
431 AND opcamid = (SELECT oid FROM pg_am WHERE amname = 'hash')
432 AND NOT p1.oprcanhash;
434 -- Check that each operator defined in pg_operator matches its oprcode entry
435 -- in pg_proc. Easiest to do this separately for each oprkind.
437 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
438 FROM pg_operator AS p1, pg_proc AS p2
439 WHERE p1.oprcode = p2.oid AND
442 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
443 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
444 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
446 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
447 FROM pg_operator AS p1, pg_proc AS p2
448 WHERE p1.oprcode = p2.oid AND
451 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
452 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
455 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
456 FROM pg_operator AS p1, pg_proc AS p2
457 WHERE p1.oprcode = p2.oid AND
460 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
461 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
462 OR p1.oprright != 0);
464 -- If the operator is mergejoinable or hashjoinable, its underlying function
465 -- should not be volatile.
467 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
468 FROM pg_operator AS p1, pg_proc AS p2
469 WHERE p1.oprcode = p2.oid AND
470 (p1.oprlsortop != 0 OR p1.oprcanhash) AND
471 p2.provolatile = 'v';
473 -- If oprrest is set, the operator must return boolean,
474 -- and it must link to a proc with the right signature
475 -- to be a restriction selectivity estimator.
476 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
478 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
479 FROM pg_operator AS p1, pg_proc AS p2
480 WHERE p1.oprrest = p2.oid AND
481 (p1.oprresult != 'bool'::regtype OR
482 p2.prorettype != 'float8'::regtype OR p2.proretset OR
484 p2.proargtypes[0] != 'internal'::regtype OR
485 p2.proargtypes[1] != 'oid'::regtype OR
486 p2.proargtypes[2] != 'internal'::regtype OR
487 p2.proargtypes[3] != 'int4'::regtype);
489 -- If oprjoin is set, the operator must be a binary boolean op,
490 -- and it must link to a proc with the right signature
491 -- to be a join selectivity estimator.
492 -- The proc signature we want is: float8 proc(internal, oid, internal, int2)
494 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
495 FROM pg_operator AS p1, pg_proc AS p2
496 WHERE p1.oprjoin = p2.oid AND
497 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
498 p2.prorettype != 'float8'::regtype OR p2.proretset OR
500 p2.proargtypes[0] != 'internal'::regtype OR
501 p2.proargtypes[1] != 'oid'::regtype OR
502 p2.proargtypes[2] != 'internal'::regtype OR
503 p2.proargtypes[3] != 'int2'::regtype);
505 -- **************** pg_aggregate ****************
507 -- Look for illegal values in pg_aggregate fields.
509 SELECT ctid, aggfnoid::oid
510 FROM pg_aggregate as p1
511 WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
513 -- Make sure the matching pg_proc entry is sensible, too.
515 SELECT a.aggfnoid::oid, p.proname
516 FROM pg_aggregate as a, pg_proc as p
517 WHERE a.aggfnoid = p.oid AND
518 (NOT p.proisagg OR p.pronargs != 1 OR p.proretset);
520 -- Make sure there are no proisagg pg_proc entries without matches.
525 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
527 -- If there is no finalfn then the output type must be the transtype.
529 SELECT a.aggfnoid::oid, p.proname
530 FROM pg_aggregate as a, pg_proc as p
531 WHERE a.aggfnoid = p.oid AND
532 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
534 -- Cross-check transfn against its entry in pg_proc.
535 -- NOTE: use physically_coercible here, not binary_coercible, because
536 -- max and min on abstime are implemented using int4larger/int4smaller.
537 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
538 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
539 WHERE a.aggfnoid = p.oid AND
540 a.aggtransfn = ptr.oid AND
542 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
543 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
544 OR NOT ((ptr.pronargs = 2 AND
545 physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
547 (ptr.pronargs = 1 AND
548 p.proargtypes[0] = '"any"'::regtype)));
550 -- Cross-check finalfn (if present) against its entry in pg_proc.
552 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
553 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
554 WHERE a.aggfnoid = p.oid AND
555 a.aggfinalfn = pfn.oid AND
557 OR NOT binary_coercible(pfn.prorettype, p.prorettype)
559 OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
561 -- If transfn is strict then either initval should be non-NULL, or
562 -- input type should match transtype so that the first non-null input
563 -- can be assigned as the state value.
565 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
566 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
567 WHERE a.aggfnoid = p.oid AND
568 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
569 a.agginitval IS NULL AND
570 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
572 -- Cross-check aggsortop (if present) against pg_operator.
573 -- We expect to find only "<" for "min" and ">" for "max".
575 SELECT DISTINCT proname, oprname
576 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
577 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
580 -- Check datatypes match
582 SELECT a.aggfnoid::oid, o.oid
583 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
584 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
585 (oprkind != 'b' OR oprresult != 'boolean'::regtype
586 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
588 -- Check operator is a suitable btree opclass member
590 SELECT a.aggfnoid::oid, o.oid
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 AND
593 NOT EXISTS(SELECT 1 FROM pg_amop ao, pg_opclass oc
594 WHERE amopclaid = oc.oid AND amopsubtype = 0
595 AND amopopr = o.oid AND opcamid = 403
596 AND opcintype = o.oprleft AND opcdefault);
598 -- Check correspondence of btree strategies and names
600 SELECT DISTINCT proname, oprname, amopstrategy
601 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
602 pg_amop as ao, pg_opclass oc
603 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
604 amopclaid = oc.oid AND amopopr = o.oid AND opcamid = 403
607 -- **************** pg_opclass ****************
609 -- Look for illegal values in pg_opclass fields
612 FROM pg_opclass as p1
613 WHERE p1.opcamid = 0 OR p1.opcintype = 0;
615 -- There should not be multiple entries in pg_opclass with opcdefault true
616 -- and the same opcamid/opcintype combination.
618 SELECT p1.oid, p2.oid
619 FROM pg_opclass AS p1, pg_opclass AS p2
620 WHERE p1.oid != p2.oid AND
621 p1.opcamid = p2.opcamid AND p1.opcintype = p2.opcintype AND
622 p1.opcdefault AND p2.opcdefault;
624 -- **************** pg_amop ****************
626 -- Look for illegal values in pg_amop fields
628 SELECT p1.amopclaid, p1.amopstrategy
630 WHERE p1.amopclaid = 0 OR p1.amopstrategy <= 0 OR p1.amopopr = 0;
632 -- Cross-check amopstrategy index against parent AM
634 SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.amname
635 FROM pg_amop AS p1, pg_am AS p2, pg_opclass AS p3
636 WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND
637 p1.amopstrategy > p2.amstrategies;
639 -- Detect missing pg_amop entries: should have as many strategy operators
640 -- as AM expects for each opclass for the AM. When nondefault subtypes are
641 -- present, enforce condition separately for each subtype.
642 -- We have to exclude GiST and GIN, unfortunately, since its havn't got any fixed
643 -- requirements about strategy operators.
645 SELECT p1.oid, p1.amname, p2.oid, p2.opcname, p3.amopsubtype
646 FROM pg_am AS p1, pg_opclass AS p2, pg_amop AS p3
647 WHERE p2.opcamid = p1.oid AND p3.amopclaid = p2.oid AND
648 p1.amname != 'gist' AND p1.amname != 'gin' AND
649 p1.amstrategies != (SELECT count(*) FROM pg_amop AS p4
650 WHERE p4.amopclaid = p2.oid AND
651 p4.amopsubtype = p3.amopsubtype);
653 -- Check that amopopr points at a reasonable-looking operator, ie a binary
654 -- operator yielding boolean.
656 SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname
657 FROM pg_amop AS p1, pg_operator AS p2
658 WHERE p1.amopopr = p2.oid AND
659 (p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
661 -- Make a list of all the distinct operator names being used in particular
662 -- strategy slots. This is a bit hokey, since the list might need to change
663 -- in future releases, but it's an effective way of spotting mistakes such as
664 -- swapping two operators within a class.
666 SELECT DISTINCT opcamid, amopstrategy, oprname
667 FROM pg_amop p1 LEFT JOIN pg_opclass p2 ON amopclaid = p2.oid
668 LEFT JOIN pg_operator p3 ON amopopr = p3.oid
671 -- Check that all operators linked to by opclass entries have selectivity
672 -- estimators. This is not absolutely required, but it seems a reasonable
673 -- thing to insist on for all standard datatypes.
675 SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname
676 FROM pg_amop AS p1, pg_operator AS p2
677 WHERE p1.amopopr = p2.oid AND
678 (p2.oprrest = 0 OR p2.oprjoin = 0);
680 -- Check that operator input types match the opclass
681 -- For 8.0, we require that oprleft match opcintype (possibly by coercion).
682 -- When amopsubtype is zero (default), oprright must equal oprleft;
683 -- when amopsubtype is not zero, oprright must equal amopsubtype.
685 SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
686 FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
687 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
688 NOT binary_coercible(p3.opcintype, p2.oprleft);
690 SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
691 FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
692 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
693 p1.amopsubtype = 0 AND
694 p2.oprleft != p2.oprright;
696 SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
697 FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
698 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
699 p1.amopsubtype != 0 AND
700 p1.amopsubtype != p2.oprright;
702 -- Operators that are primary members of opclasses must be immutable (else
703 -- it suggests that the index ordering isn't fixed). Operators that are
704 -- cross-type members need only be stable, since they are just shorthands
705 -- for index probe queries.
707 SELECT p1.amopclaid, p1.amopopr, p2.oprname, p3.prosrc
708 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
709 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
710 p1.amopsubtype = 0 AND
711 p3.provolatile != 'i';
713 SELECT p1.amopclaid, p1.amopopr, p2.oprname, p3.prosrc
714 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
715 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
716 p1.amopsubtype != 0 AND
717 p3.provolatile = 'v';
719 -- **************** pg_amproc ****************
721 -- Look for illegal values in pg_amproc fields
723 SELECT p1.amopclaid, p1.amprocnum
725 WHERE p1.amopclaid = 0 OR p1.amprocnum <= 0 OR p1.amproc = 0;
727 -- Cross-check amprocnum index against parent AM
729 SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.amname
730 FROM pg_amproc AS p1, pg_am AS p2, pg_opclass AS p3
731 WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND
732 p1.amprocnum > p2.amsupport;
734 -- Detect missing pg_amproc entries: should have as many support functions
735 -- as AM expects for each opclass for the AM. When nondefault subtypes are
736 -- present, enforce condition separately for each subtype.
738 SELECT p1.oid, p1.amname, p2.oid, p2.opcname, p3.amprocsubtype
739 FROM pg_am AS p1, pg_opclass AS p2, pg_amproc AS p3
740 WHERE p2.opcamid = p1.oid AND p3.amopclaid = p2.oid AND
741 p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
742 WHERE p4.amopclaid = p2.oid AND
743 p4.amprocsubtype = p3.amprocsubtype);
745 -- Unfortunately, we can't check the amproc link very well because the
746 -- signature of the function may be different for different support routines
747 -- or different base data types.
748 -- We can check that all the referenced instances of the same support
749 -- routine number take the same number of parameters, but that's about it
750 -- for a general check...
752 SELECT p1.amopclaid, p1.amprocnum,
755 p4.amopclaid, p4.amprocnum,
758 FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3,
759 pg_amproc AS p4, pg_proc AS p5, pg_opclass AS p6
760 WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND
761 p3.opcamid = p6.opcamid AND p1.amprocnum = p4.amprocnum AND
762 p1.amproc = p2.oid AND p4.amproc = p5.oid AND
763 (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
765 -- For btree, though, we can do better since we know the support routines
766 -- must be of the form cmp(input, input) returns int4 in the default case
767 -- (subtype = 0), and cmp(input, subtype) returns int4 when subtype != 0.
769 SELECT p1.amopclaid, p1.amprocnum,
772 FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3
773 WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
774 AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND
775 amprocsubtype = 0 AND
781 OR NOT binary_coercible(opcintype, proargtypes[0])
782 OR proargtypes[0] != proargtypes[1]);
784 SELECT p1.amopclaid, p1.amprocnum,
787 FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3
788 WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
789 AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND
790 amprocsubtype != 0 AND
796 OR NOT binary_coercible(opcintype, proargtypes[0])
797 OR proargtypes[1] != amprocsubtype);
799 -- For hash we can also do a little better: the support routines must be
800 -- of the form hash(something) returns int4. Ideally we'd check that the
801 -- opcintype is binary-coercible to the function's input, but there are
802 -- enough cases where that fails that I'll just leave out the check for now.
804 SELECT p1.amopclaid, p1.amprocnum,
807 FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3
808 WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'hash')
809 AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND
815 -- OR NOT physically_coercible(opcintype, proargtypes[0])
818 -- Support routines that are primary members of opclasses must be immutable
819 -- (else it suggests that the index ordering isn't fixed). But cross-type
820 -- members need only be stable, since they are just shorthands
821 -- for index probe queries.
823 SELECT p1.amopclaid, p1.amproc, p2.prosrc
824 FROM pg_amproc AS p1, pg_proc AS p2
825 WHERE p1.amproc = p2.oid AND
826 p1.amprocsubtype = 0 AND
827 p2.provolatile != 'i';
829 SELECT p1.amopclaid, p1.amproc, p2.prosrc
830 FROM pg_amproc AS p1, pg_proc AS p2
831 WHERE p1.amproc = p2.oid AND
832 p1.amprocsubtype != 0 AND
833 p2.provolatile = 'v';