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 castfunc = 0 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 array_lower(p1.proargtypes, 1) != 0 OR
55 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR
56 0::oid = ANY (p1.proargtypes) OR
58 CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END;
60 -- Look for conflicting proc definitions (same names and input datatypes).
61 -- (This test should be dead code now that we have the unique index
62 -- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.)
64 SELECT p1.oid, p1.proname, p2.oid, p2.proname
65 FROM pg_proc AS p1, pg_proc AS p2
66 WHERE p1.oid != p2.oid AND
67 p1.proname = p2.proname AND
68 p1.pronargs = p2.pronargs AND
69 p1.proargtypes = p2.proargtypes;
71 -- Considering only built-in procs (prolang = 12), look for multiple uses
72 -- of the same internal function (ie, matching prosrc fields). It's OK to
73 -- have several entries with different pronames for the same internal function,
74 -- but conflicts in the number of arguments and other critical items should
75 -- be complained of. (We don't check data types here; see next query.)
76 -- Note: ignore aggregate functions here, since they all point to the same
77 -- dummy built-in function.
79 SELECT p1.oid, p1.proname, p2.oid, p2.proname
80 FROM pg_proc AS p1, pg_proc AS p2
81 WHERE p1.oid < p2.oid AND
82 p1.prosrc = p2.prosrc AND
83 p1.prolang = 12 AND p2.prolang = 12 AND
84 (p1.proisagg = false OR p2.proisagg = false) AND
85 (p1.prolang != p2.prolang OR
86 p1.proisagg != p2.proisagg OR
87 p1.prosecdef != p2.prosecdef OR
88 p1.proisstrict != p2.proisstrict OR
89 p1.proretset != p2.proretset OR
90 p1.provolatile != p2.provolatile OR
91 p1.pronargs != p2.pronargs);
93 -- Look for uses of different type OIDs in the argument/result type fields
94 -- for different aliases of the same built-in function.
95 -- This indicates that the types are being presumed to be binary-equivalent,
96 -- or that the built-in function is prepared to deal with different types.
97 -- That's not wrong, necessarily, but we make lists of all the types being
98 -- so treated. Note that the expected output of this part of the test will
99 -- need to be modified whenever new pairs of types are made binary-equivalent,
100 -- or when new polymorphic built-in functions are added!
101 -- Note: ignore aggregate functions here, since they all point to the same
102 -- dummy built-in function.
104 SELECT DISTINCT p1.prorettype, p2.prorettype
105 FROM pg_proc AS p1, pg_proc AS p2
106 WHERE p1.oid != p2.oid AND
107 p1.prosrc = p2.prosrc AND
108 p1.prolang = 12 AND p2.prolang = 12 AND
109 NOT p1.proisagg AND NOT p2.proisagg AND
110 (p1.prorettype < p2.prorettype);
112 SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
113 FROM pg_proc AS p1, pg_proc AS p2
114 WHERE p1.oid != p2.oid AND
115 p1.prosrc = p2.prosrc AND
116 p1.prolang = 12 AND p2.prolang = 12 AND
117 NOT p1.proisagg AND NOT p2.proisagg AND
118 (p1.proargtypes[0] < p2.proargtypes[0]);
120 SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
121 FROM pg_proc AS p1, pg_proc AS p2
122 WHERE p1.oid != p2.oid AND
123 p1.prosrc = p2.prosrc AND
124 p1.prolang = 12 AND p2.prolang = 12 AND
125 NOT p1.proisagg AND NOT p2.proisagg AND
126 (p1.proargtypes[1] < p2.proargtypes[1]);
128 SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
129 FROM pg_proc AS p1, pg_proc AS p2
130 WHERE p1.oid != p2.oid AND
131 p1.prosrc = p2.prosrc AND
132 p1.prolang = 12 AND p2.prolang = 12 AND
133 NOT p1.proisagg AND NOT p2.proisagg AND
134 (p1.proargtypes[2] < p2.proargtypes[2]);
136 SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
137 FROM pg_proc AS p1, pg_proc AS p2
138 WHERE p1.oid != p2.oid AND
139 p1.prosrc = p2.prosrc AND
140 p1.prolang = 12 AND p2.prolang = 12 AND
141 NOT p1.proisagg AND NOT p2.proisagg AND
142 (p1.proargtypes[3] < p2.proargtypes[3]);
144 SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
145 FROM pg_proc AS p1, pg_proc AS p2
146 WHERE p1.oid != p2.oid AND
147 p1.prosrc = p2.prosrc AND
148 p1.prolang = 12 AND p2.prolang = 12 AND
149 NOT p1.proisagg AND NOT p2.proisagg AND
150 (p1.proargtypes[4] < p2.proargtypes[4]);
152 SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
153 FROM pg_proc AS p1, pg_proc AS p2
154 WHERE p1.oid != p2.oid AND
155 p1.prosrc = p2.prosrc AND
156 p1.prolang = 12 AND p2.prolang = 12 AND
157 NOT p1.proisagg AND NOT p2.proisagg AND
158 (p1.proargtypes[5] < p2.proargtypes[5]);
160 SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
161 FROM pg_proc AS p1, pg_proc AS p2
162 WHERE p1.oid != p2.oid AND
163 p1.prosrc = p2.prosrc AND
164 p1.prolang = 12 AND p2.prolang = 12 AND
165 NOT p1.proisagg AND NOT p2.proisagg AND
166 (p1.proargtypes[6] < p2.proargtypes[6]);
168 SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
169 FROM pg_proc AS p1, pg_proc AS p2
170 WHERE p1.oid != p2.oid AND
171 p1.prosrc = p2.prosrc AND
172 p1.prolang = 12 AND p2.prolang = 12 AND
173 NOT p1.proisagg AND NOT p2.proisagg AND
174 (p1.proargtypes[7] < p2.proargtypes[7]);
176 -- Look for functions that return type "internal" and do not have any
177 -- "internal" argument. Such a function would be a security hole since
178 -- it might be used to call an internal function from an SQL command.
179 -- As of 7.3 this query should find only internal_in.
181 SELECT p1.oid, p1.proname
183 WHERE p1.prorettype = 'internal'::regtype AND NOT
184 'internal'::regtype = ANY (p1.proargtypes);
187 -- **************** pg_cast ****************
189 -- Catch bogus values in pg_cast columns (other than cases detected by
194 WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i');
196 -- Look for casts to/from the same type that aren't length coercion functions.
197 -- (We assume they are length coercions if they take multiple arguments.)
198 -- Such entries are not necessarily harmful, but they are useless.
202 WHERE castsource = casttarget AND castfunc = 0;
205 FROM pg_cast c, pg_proc p
206 WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
208 -- Look for cast functions that don't have the right signature. The
209 -- argument and result types in pg_proc must be the same as, or binary
210 -- compatible with, what it says in pg_cast.
211 -- As a special case, we allow casts from CHAR(n) that use functions
212 -- declared to take TEXT. This does not pass the binary-coercibility test
213 -- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
214 -- are the same, so long as the function is one that ignores trailing blanks.
217 FROM pg_cast c, pg_proc p
218 WHERE c.castfunc = p.oid AND
219 (p.pronargs < 1 OR p.pronargs > 3
220 OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
221 OR (c.castsource = 'character'::regtype AND
222 p.proargtypes[0] = 'text'::regtype))
223 OR NOT binary_coercible(p.prorettype, c.casttarget));
226 FROM pg_cast c, pg_proc p
227 WHERE c.castfunc = p.oid AND
228 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
229 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
231 -- Look for binary compatible casts that do not have the reverse
232 -- direction registered as well, or where the reverse direction is not
233 -- also binary compatible. This is legal, but usually not intended.
235 -- As of 7.4, this finds the casts from text and varchar to bpchar, because
236 -- those are binary-compatible while the reverse way goes through rtrim().
238 -- As of 8.2, this finds the cast from cidr to inet, because that is a
239 -- trivial binary coercion while the other way goes through inet_to_cidr().
243 WHERE c.castfunc = 0 AND
244 NOT EXISTS (SELECT 1 FROM pg_cast k
245 WHERE k.castfunc = 0 AND
246 k.castsource = c.casttarget AND
247 k.casttarget = c.castsource);
249 -- **************** pg_operator ****************
251 -- Look for illegal values in pg_operator fields.
253 SELECT p1.oid, p1.oprname
254 FROM pg_operator as p1
255 WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
256 p1.oprresult = 0 OR p1.oprcode = 0;
258 -- Look for missing or unwanted operand types
260 SELECT p1.oid, p1.oprname
261 FROM pg_operator as p1
262 WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
263 (p1.oprleft != 0 and p1.oprkind = 'l') OR
264 (p1.oprright = 0 and p1.oprkind != 'r') OR
265 (p1.oprright != 0 and p1.oprkind = 'r');
267 -- Look for conflicting operator definitions (same names and input datatypes).
269 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
270 FROM pg_operator AS p1, pg_operator AS p2
271 WHERE p1.oid != p2.oid AND
272 p1.oprname = p2.oprname AND
273 p1.oprkind = p2.oprkind AND
274 p1.oprleft = p2.oprleft AND
275 p1.oprright = p2.oprright;
277 -- Look for commutative operators that don't commute.
278 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
279 -- We expect that B will always say that B.oprcom = A as well; that's not
280 -- inherently essential, but it would be inefficient not to mark it so.
282 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
283 FROM pg_operator AS p1, pg_operator AS p2
284 WHERE p1.oprcom = p2.oid AND
285 (p1.oprkind != 'b' OR
286 p1.oprleft != p2.oprright OR
287 p1.oprright != p2.oprleft OR
288 p1.oprresult != p2.oprresult OR
289 p1.oid != p2.oprcom);
291 -- Look for negatory operators that don't agree.
292 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
293 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
294 -- single-operand operators.
295 -- We expect that B will always say that B.oprnegate = A as well; that's not
296 -- inherently essential, but it would be inefficient not to mark it so.
297 -- Also, A and B had better not be the same operator.
299 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
300 FROM pg_operator AS p1, pg_operator AS p2
301 WHERE p1.oprnegate = p2.oid AND
302 (p1.oprkind != p2.oprkind OR
303 p1.oprleft != p2.oprleft OR
304 p1.oprright != p2.oprright OR
305 p1.oprresult != 'bool'::regtype OR
306 p2.oprresult != 'bool'::regtype OR
307 p1.oid != p2.oprnegate OR
310 -- A mergejoinable or hashjoinable operator must be binary, must return
311 -- boolean, and must have a commutator (itself, unless it's a cross-type
314 SELECT p1.oid, p1.oprname FROM pg_operator AS p1
315 WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT
316 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0);
318 -- Mergejoinable operators should appear as equality members of btree index
321 SELECT p1.oid, p1.oprname
322 FROM pg_operator AS p1
323 WHERE p1.oprcanmerge AND NOT EXISTS
324 (SELECT 1 FROM pg_amop
325 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
326 amopopr = p1.oid AND amopstrategy = 3);
330 SELECT p1.oid, p1.oprname, p.amopfamily
331 FROM pg_operator AS p1, pg_amop p
332 WHERE amopopr = p1.oid
333 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
335 AND NOT p1.oprcanmerge;
337 -- Hashable operators should appear as members of hash index opfamilies.
339 SELECT p1.oid, p1.oprname
340 FROM pg_operator AS p1
341 WHERE p1.oprcanhash AND NOT EXISTS
342 (SELECT 1 FROM pg_amop
343 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
344 amopopr = p1.oid AND amopstrategy = 1);
348 SELECT p1.oid, p1.oprname, p.amopfamily
349 FROM pg_operator AS p1, pg_amop p
350 WHERE amopopr = p1.oid
351 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
352 AND NOT p1.oprcanhash;
354 -- Check that each operator defined in pg_operator matches its oprcode entry
355 -- in pg_proc. Easiest to do this separately for each oprkind.
357 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
358 FROM pg_operator AS p1, pg_proc AS p2
359 WHERE p1.oprcode = p2.oid AND
362 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
363 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
364 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
366 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
367 FROM pg_operator AS p1, pg_proc AS p2
368 WHERE p1.oprcode = p2.oid AND
371 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
372 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
375 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
376 FROM pg_operator AS p1, pg_proc AS p2
377 WHERE p1.oprcode = p2.oid AND
380 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
381 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
382 OR p1.oprright != 0);
384 -- If the operator is mergejoinable or hashjoinable, its underlying function
385 -- should not be volatile.
387 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
388 FROM pg_operator AS p1, pg_proc AS p2
389 WHERE p1.oprcode = p2.oid AND
390 (p1.oprcanmerge OR p1.oprcanhash) AND
391 p2.provolatile = 'v';
393 -- If oprrest is set, the operator must return boolean,
394 -- and it must link to a proc with the right signature
395 -- to be a restriction selectivity estimator.
396 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
398 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
399 FROM pg_operator AS p1, pg_proc AS p2
400 WHERE p1.oprrest = p2.oid AND
401 (p1.oprresult != 'bool'::regtype OR
402 p2.prorettype != 'float8'::regtype OR p2.proretset OR
404 p2.proargtypes[0] != 'internal'::regtype OR
405 p2.proargtypes[1] != 'oid'::regtype OR
406 p2.proargtypes[2] != 'internal'::regtype OR
407 p2.proargtypes[3] != 'int4'::regtype);
409 -- If oprjoin is set, the operator must be a binary boolean op,
410 -- and it must link to a proc with the right signature
411 -- to be a join selectivity estimator.
412 -- The proc signature we want is: float8 proc(internal, oid, internal, int2)
414 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
415 FROM pg_operator AS p1, pg_proc AS p2
416 WHERE p1.oprjoin = p2.oid AND
417 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
418 p2.prorettype != 'float8'::regtype OR p2.proretset OR
420 p2.proargtypes[0] != 'internal'::regtype OR
421 p2.proargtypes[1] != 'oid'::regtype OR
422 p2.proargtypes[2] != 'internal'::regtype OR
423 p2.proargtypes[3] != 'int2'::regtype);
425 -- **************** pg_aggregate ****************
427 -- Look for illegal values in pg_aggregate fields.
429 SELECT ctid, aggfnoid::oid
430 FROM pg_aggregate as p1
431 WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
433 -- Make sure the matching pg_proc entry is sensible, too.
435 SELECT a.aggfnoid::oid, p.proname
436 FROM pg_aggregate as a, pg_proc as p
437 WHERE a.aggfnoid = p.oid AND
438 (NOT p.proisagg OR p.proretset);
440 -- Make sure there are no proisagg pg_proc entries without matches.
445 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
447 -- If there is no finalfn then the output type must be the transtype.
449 SELECT a.aggfnoid::oid, p.proname
450 FROM pg_aggregate as a, pg_proc as p
451 WHERE a.aggfnoid = p.oid AND
452 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
454 -- Cross-check transfn against its entry in pg_proc.
455 -- NOTE: use physically_coercible here, not binary_coercible, because
456 -- max and min on abstime are implemented using int4larger/int4smaller.
457 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
458 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
459 WHERE a.aggfnoid = p.oid AND
460 a.aggtransfn = ptr.oid AND
462 OR NOT (ptr.pronargs = p.pronargs + 1)
463 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
464 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
465 OR (p.pronargs > 0 AND
466 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
467 OR (p.pronargs > 1 AND
468 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
469 OR (p.pronargs > 2 AND
470 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
471 -- we could carry the check further, but that's enough for now
474 -- Cross-check finalfn (if present) against its entry in pg_proc.
476 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
477 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
478 WHERE a.aggfnoid = p.oid AND
479 a.aggfinalfn = pfn.oid AND
481 OR NOT binary_coercible(pfn.prorettype, p.prorettype)
483 OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
485 -- If transfn is strict then either initval should be non-NULL, or
486 -- input type should match transtype so that the first non-null input
487 -- can be assigned as the state value.
489 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
490 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
491 WHERE a.aggfnoid = p.oid AND
492 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
493 a.agginitval IS NULL AND
494 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
496 -- Cross-check aggsortop (if present) against pg_operator.
497 -- We expect to find only "<" for "min" and ">" for "max".
499 SELECT DISTINCT proname, oprname
500 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
501 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
504 -- Check datatypes match
506 SELECT a.aggfnoid::oid, o.oid
507 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
508 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
509 (oprkind != 'b' OR oprresult != 'boolean'::regtype
510 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
512 -- Check operator is a suitable btree opfamily member
514 SELECT a.aggfnoid::oid, o.oid
515 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
516 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
517 NOT EXISTS(SELECT 1 FROM pg_amop
518 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
520 AND amoplefttype = o.oprleft
521 AND amoprighttype = o.oprright);
523 -- Check correspondence of btree strategies and names
525 SELECT DISTINCT proname, oprname, amopstrategy
526 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
528 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
530 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
533 -- **************** pg_opfamily ****************
535 -- Look for illegal values in pg_opfamily fields
538 FROM pg_opfamily as p1
539 WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
541 -- **************** pg_opclass ****************
543 -- Look for illegal values in pg_opclass fields
546 FROM pg_opclass AS p1
547 WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
550 -- opcmethod must match owning opfamily's opfmethod
552 SELECT p1.oid, p2.oid
553 FROM pg_opclass AS p1, pg_opfamily AS p2
554 WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
556 -- There should not be multiple entries in pg_opclass with opcdefault true
557 -- and the same opcmethod/opcintype combination.
559 SELECT p1.oid, p2.oid
560 FROM pg_opclass AS p1, pg_opclass AS p2
561 WHERE p1.oid != p2.oid AND
562 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
563 p1.opcdefault AND p2.opcdefault;
565 -- **************** pg_amop ****************
567 -- Look for illegal values in pg_amop fields
569 SELECT p1.amopfamily, p1.amopstrategy
571 WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
572 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
574 -- amoplefttype/amoprighttype must match the operator
576 SELECT p1.oid, p2.oid
577 FROM pg_amop AS p1, pg_operator AS p2
578 WHERE p1.amopopr = p2.oid AND NOT
579 (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);
581 -- amopmethod must match owning opfamily's opfmethod
583 SELECT p1.oid, p2.oid
584 FROM pg_amop AS p1, pg_opfamily AS p2
585 WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
587 -- Cross-check amopstrategy index against parent AM
589 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
590 FROM pg_amop AS p1, pg_am AS p2
591 WHERE p1.amopmethod = p2.oid AND
592 p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
594 -- Detect missing pg_amop entries: should have as many strategy operators
595 -- as AM expects for each datatype combination supported by the opfamily.
596 -- We can't check this for AMs with variable strategy sets.
598 SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
599 FROM pg_am AS p1, pg_amop AS p2
600 WHERE p2.amopmethod = p1.oid AND
601 p1.amstrategies <> 0 AND
602 p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
603 WHERE p3.amopfamily = p2.amopfamily AND
604 p3.amoplefttype = p2.amoplefttype AND
605 p3.amoprighttype = p2.amoprighttype);
607 -- Check that amopopr points at a reasonable-looking operator, ie a binary
608 -- operator yielding boolean.
610 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
611 FROM pg_amop AS p1, pg_operator AS p2
612 WHERE p1.amopopr = p2.oid AND
613 (p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
615 -- Make a list of all the distinct operator names being used in particular
616 -- strategy slots. This is a bit hokey, since the list might need to change
617 -- in future releases, but it's an effective way of spotting mistakes such as
618 -- swapping two operators within a family.
620 SELECT DISTINCT amopmethod, amopstrategy, oprname
621 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
624 -- Check that all operators linked to by opclass entries have selectivity
625 -- estimators. This is not absolutely required, but it seems a reasonable
626 -- thing to insist on for all standard datatypes.
628 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
629 FROM pg_amop AS p1, pg_operator AS p2
630 WHERE p1.amopopr = p2.oid AND
631 (p2.oprrest = 0 OR p2.oprjoin = 0);
633 -- Check that each opclass in an opfamily has associated operators, that is
634 -- ones whose oprleft matches opcintype (possibly by coercion).
636 SELECT p1.opcname, p1.opcfamily
637 FROM pg_opclass AS p1
638 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
639 WHERE p2.amopfamily = p1.opcfamily
640 AND binary_coercible(p1.opcintype, p2.amoplefttype));
642 -- Operators that are primary members of opclasses must be immutable (else
643 -- it suggests that the index ordering isn't fixed). Operators that are
644 -- cross-type members need only be stable, since they are just shorthands
645 -- for index probe queries.
647 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
648 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
649 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
650 p1.amoplefttype = p1.amoprighttype AND
651 p3.provolatile != 'i';
653 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
654 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
655 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
656 p1.amoplefttype != p1.amoprighttype AND
657 p3.provolatile = 'v';
659 -- Multiple-datatype btree opfamilies should provide closed sets of equality
660 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
661 -- should also provide int2 = int8 (and commutators of all these). This is
662 -- important because the planner tries to deduce additional qual clauses from
663 -- transitivity of mergejoinable operators. If there are clauses
664 -- int2var = int4var and int4var = int8var, the planner will want to deduce
665 -- int2var = int8var ... so there should be a way to represent that. While
666 -- a missing cross-type operator is now only an efficiency loss rather than
667 -- an error condition, it still seems reasonable to insist that all built-in
668 -- opfamilies be complete.
670 -- check commutative closure
671 SELECT p1.amoplefttype, p1.amoprighttype
673 WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
674 p1.amopstrategy = 3 AND
675 p1.amoplefttype != p1.amoprighttype AND
676 NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
677 p2.amopfamily = p1.amopfamily AND
678 p2.amoplefttype = p1.amoprighttype AND
679 p2.amoprighttype = p1.amoplefttype AND
680 p2.amopstrategy = 3);
682 -- check transitive closure
683 SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
684 FROM pg_amop AS p1, pg_amop AS p2
685 WHERE p1.amopfamily = p2.amopfamily AND
686 p1.amoprighttype = p2.amoplefttype AND
687 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
688 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
689 p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
690 p1.amoplefttype != p1.amoprighttype AND
691 p2.amoplefttype != p2.amoprighttype AND
692 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
693 p3.amopfamily = p1.amopfamily AND
694 p3.amoplefttype = p1.amoplefttype AND
695 p3.amoprighttype = p2.amoprighttype AND
696 p3.amopstrategy = 3);
698 -- We also expect that built-in multiple-datatype hash opfamilies provide
699 -- complete sets of cross-type operators. Again, this isn't required, but
700 -- it is reasonable to expect it for built-in opfamilies.
702 -- if same family has x=x and y=y, it should have x=y
703 SELECT p1.amoplefttype, p2.amoplefttype
704 FROM pg_amop AS p1, pg_amop AS p2
705 WHERE p1.amopfamily = p2.amopfamily AND
706 p1.amoplefttype = p1.amoprighttype AND
707 p2.amoplefttype = p2.amoprighttype AND
708 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
709 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
710 p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
711 p1.amoplefttype != p2.amoplefttype AND
712 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
713 p3.amopfamily = p1.amopfamily AND
714 p3.amoplefttype = p1.amoplefttype AND
715 p3.amoprighttype = p2.amoplefttype AND
716 p3.amopstrategy = 1);
719 -- **************** pg_amproc ****************
721 -- Look for illegal values in pg_amproc fields
723 SELECT p1.amprocfamily, p1.amprocnum
725 WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
726 OR p1.amprocnum < 1 OR p1.amproc = 0;
728 -- Cross-check amprocnum index against parent AM
730 SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
731 FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
732 WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
733 p1.amprocnum > p2.amsupport;
735 -- Detect missing pg_amproc entries: should have as many support functions
736 -- as AM expects for each datatype combination supported by the opfamily.
738 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
739 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
740 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
741 p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
742 WHERE p4.amprocfamily = p2.oid AND
743 p4.amproclefttype = p3.amproclefttype AND
744 p4.amprocrighttype = p3.amprocrighttype);
746 -- Unfortunately, we can't check the amproc link very well because the
747 -- signature of the function may be different for different support routines
748 -- or different base data types.
749 -- We can check that all the referenced instances of the same support
750 -- routine number take the same number of parameters, but that's about it
751 -- for a general check...
753 SELECT p1.amprocfamily, p1.amprocnum,
756 p4.amprocfamily, p4.amprocnum,
759 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
760 pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
761 WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
762 p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
763 p1.amproc = p2.oid AND p4.amproc = p5.oid AND
764 (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
766 -- For btree, though, we can do better since we know the support routines
767 -- must be of the form cmp(lefttype, righttype) returns int4.
769 SELECT p1.amprocfamily, p1.amprocnum,
772 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
773 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
774 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
777 OR prorettype != 'int4'::regtype
779 OR proargtypes[0] != amproclefttype
780 OR proargtypes[1] != amprocrighttype);
782 -- For hash we can also do a little better: the support routines must be
783 -- of the form hash(lefttype) returns int4. There are several cases where
784 -- we cheat and use a hash function that is physically compatible with the
785 -- datatype even though there's no cast, so for now we can't check that.
787 SELECT p1.amprocfamily, p1.amprocnum,
790 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
791 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
792 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
795 OR prorettype != 'int4'::regtype
797 -- OR NOT physically_coercible(amproclefttype, proargtypes[0])
798 OR amproclefttype != amprocrighttype);
800 -- Support routines that are primary members of opfamilies must be immutable
801 -- (else it suggests that the index ordering isn't fixed). But cross-type
802 -- members need only be stable, since they are just shorthands
803 -- for index probe queries.
805 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
806 FROM pg_amproc AS p1, pg_proc AS p2
807 WHERE p1.amproc = p2.oid AND
808 p1.amproclefttype = p1.amprocrighttype AND
809 p2.provolatile != 'i';
811 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
812 FROM pg_amproc AS p1, pg_proc AS p2
813 WHERE p1.amproc = p2.oid AND
814 p1.amproclefttype != p1.amprocrighttype AND
815 p2.provolatile = 'v';