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 -- What's more, the commutator had better be mergejoinable/hashjoinable too.
320 SELECT p1.oid, p1.oprname, p2.oid, p2.oprname
321 FROM pg_operator AS p1, pg_operator AS p2
322 WHERE p1.oprcom = p2.oid AND
323 (p1.oprcanmerge != p2.oprcanmerge OR
324 p1.oprcanhash != p2.oprcanhash);
326 -- Mergejoinable operators should appear as equality members of btree index
329 SELECT p1.oid, p1.oprname
330 FROM pg_operator AS p1
331 WHERE p1.oprcanmerge AND NOT EXISTS
332 (SELECT 1 FROM pg_amop
333 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
334 amopopr = p1.oid AND amopstrategy = 3);
338 SELECT p1.oid, p1.oprname, p.amopfamily
339 FROM pg_operator AS p1, pg_amop p
340 WHERE amopopr = p1.oid
341 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
343 AND NOT p1.oprcanmerge;
345 -- Hashable operators should appear as members of hash index opfamilies.
347 SELECT p1.oid, p1.oprname
348 FROM pg_operator AS p1
349 WHERE p1.oprcanhash AND NOT EXISTS
350 (SELECT 1 FROM pg_amop
351 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
352 amopopr = p1.oid AND amopstrategy = 1);
356 SELECT p1.oid, p1.oprname, p.amopfamily
357 FROM pg_operator AS p1, pg_amop p
358 WHERE amopopr = p1.oid
359 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
360 AND NOT p1.oprcanhash;
362 -- Check that each operator defined in pg_operator matches its oprcode entry
363 -- in pg_proc. Easiest to do this separately for each oprkind.
365 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
366 FROM pg_operator AS p1, pg_proc AS p2
367 WHERE p1.oprcode = p2.oid AND
370 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
371 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
372 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
374 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
375 FROM pg_operator AS p1, pg_proc AS p2
376 WHERE p1.oprcode = p2.oid AND
379 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
380 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
383 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
384 FROM pg_operator AS p1, pg_proc AS p2
385 WHERE p1.oprcode = p2.oid AND
388 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
389 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
390 OR p1.oprright != 0);
392 -- If the operator is mergejoinable or hashjoinable, its underlying function
393 -- should not be volatile.
395 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
396 FROM pg_operator AS p1, pg_proc AS p2
397 WHERE p1.oprcode = p2.oid AND
398 (p1.oprcanmerge OR p1.oprcanhash) AND
399 p2.provolatile = 'v';
401 -- If oprrest is set, the operator must return boolean,
402 -- and it must link to a proc with the right signature
403 -- to be a restriction selectivity estimator.
404 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
406 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
407 FROM pg_operator AS p1, pg_proc AS p2
408 WHERE p1.oprrest = p2.oid AND
409 (p1.oprresult != 'bool'::regtype OR
410 p2.prorettype != 'float8'::regtype OR p2.proretset OR
412 p2.proargtypes[0] != 'internal'::regtype OR
413 p2.proargtypes[1] != 'oid'::regtype OR
414 p2.proargtypes[2] != 'internal'::regtype OR
415 p2.proargtypes[3] != 'int4'::regtype);
417 -- If oprjoin is set, the operator must be a binary boolean op,
418 -- and it must link to a proc with the right signature
419 -- to be a join selectivity estimator.
420 -- The proc signature we want is: float8 proc(internal, oid, internal, int2)
422 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
423 FROM pg_operator AS p1, pg_proc AS p2
424 WHERE p1.oprjoin = p2.oid AND
425 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
426 p2.prorettype != 'float8'::regtype OR p2.proretset OR
428 p2.proargtypes[0] != 'internal'::regtype OR
429 p2.proargtypes[1] != 'oid'::regtype OR
430 p2.proargtypes[2] != 'internal'::regtype OR
431 p2.proargtypes[3] != 'int2'::regtype);
433 -- **************** pg_aggregate ****************
435 -- Look for illegal values in pg_aggregate fields.
437 SELECT ctid, aggfnoid::oid
438 FROM pg_aggregate as p1
439 WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
441 -- Make sure the matching pg_proc entry is sensible, too.
443 SELECT a.aggfnoid::oid, p.proname
444 FROM pg_aggregate as a, pg_proc as p
445 WHERE a.aggfnoid = p.oid AND
446 (NOT p.proisagg OR p.proretset);
448 -- Make sure there are no proisagg pg_proc entries without matches.
453 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
455 -- If there is no finalfn then the output type must be the transtype.
457 SELECT a.aggfnoid::oid, p.proname
458 FROM pg_aggregate as a, pg_proc as p
459 WHERE a.aggfnoid = p.oid AND
460 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
462 -- Cross-check transfn against its entry in pg_proc.
463 -- NOTE: use physically_coercible here, not binary_coercible, because
464 -- max and min on abstime are implemented using int4larger/int4smaller.
465 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
466 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
467 WHERE a.aggfnoid = p.oid AND
468 a.aggtransfn = ptr.oid AND
470 OR NOT (ptr.pronargs = p.pronargs + 1)
471 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
472 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
473 OR (p.pronargs > 0 AND
474 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
475 OR (p.pronargs > 1 AND
476 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
477 OR (p.pronargs > 2 AND
478 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
479 -- we could carry the check further, but that's enough for now
482 -- Cross-check finalfn (if present) against its entry in pg_proc.
484 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
485 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
486 WHERE a.aggfnoid = p.oid AND
487 a.aggfinalfn = pfn.oid AND
489 OR NOT binary_coercible(pfn.prorettype, p.prorettype)
491 OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
493 -- If transfn is strict then either initval should be non-NULL, or
494 -- input type should match transtype so that the first non-null input
495 -- can be assigned as the state value.
497 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
498 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
499 WHERE a.aggfnoid = p.oid AND
500 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
501 a.agginitval IS NULL AND
502 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
504 -- Cross-check aggsortop (if present) against pg_operator.
505 -- We expect to find only "<" for "min" and ">" for "max".
507 SELECT DISTINCT proname, oprname
508 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
509 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
512 -- Check datatypes match
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 (oprkind != 'b' OR oprresult != 'boolean'::regtype
518 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
520 -- Check operator is a suitable btree opfamily member
522 SELECT a.aggfnoid::oid, o.oid
523 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
524 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
525 NOT EXISTS(SELECT 1 FROM pg_amop
526 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
528 AND amoplefttype = o.oprleft
529 AND amoprighttype = o.oprright);
531 -- Check correspondence of btree strategies and names
533 SELECT DISTINCT proname, oprname, amopstrategy
534 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
536 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
538 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
541 -- **************** pg_opfamily ****************
543 -- Look for illegal values in pg_opfamily fields
546 FROM pg_opfamily as p1
547 WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
549 -- **************** pg_opclass ****************
551 -- Look for illegal values in pg_opclass fields
554 FROM pg_opclass AS p1
555 WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
558 -- opcmethod must match owning opfamily's opfmethod
560 SELECT p1.oid, p2.oid
561 FROM pg_opclass AS p1, pg_opfamily AS p2
562 WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
564 -- There should not be multiple entries in pg_opclass with opcdefault true
565 -- and the same opcmethod/opcintype combination.
567 SELECT p1.oid, p2.oid
568 FROM pg_opclass AS p1, pg_opclass AS p2
569 WHERE p1.oid != p2.oid AND
570 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
571 p1.opcdefault AND p2.opcdefault;
573 -- **************** pg_amop ****************
575 -- Look for illegal values in pg_amop fields
577 SELECT p1.amopfamily, p1.amopstrategy
579 WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
580 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
582 -- amoplefttype/amoprighttype must match the operator
584 SELECT p1.oid, p2.oid
585 FROM pg_amop AS p1, pg_operator AS p2
586 WHERE p1.amopopr = p2.oid AND NOT
587 (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);
589 -- amopmethod must match owning opfamily's opfmethod
591 SELECT p1.oid, p2.oid
592 FROM pg_amop AS p1, pg_opfamily AS p2
593 WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
595 -- Cross-check amopstrategy index against parent AM
597 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
598 FROM pg_amop AS p1, pg_am AS p2
599 WHERE p1.amopmethod = p2.oid AND
600 p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
602 -- Detect missing pg_amop entries: should have as many strategy operators
603 -- as AM expects for each datatype combination supported by the opfamily.
604 -- We can't check this for AMs with variable strategy sets.
606 SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
607 FROM pg_am AS p1, pg_amop AS p2
608 WHERE p2.amopmethod = p1.oid AND
609 p1.amstrategies <> 0 AND
610 p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
611 WHERE p3.amopfamily = p2.amopfamily AND
612 p3.amoplefttype = p2.amoplefttype AND
613 p3.amoprighttype = p2.amoprighttype);
615 -- Check that amopopr points at a reasonable-looking operator, ie a binary
616 -- operator yielding boolean.
618 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
619 FROM pg_amop AS p1, pg_operator AS p2
620 WHERE p1.amopopr = p2.oid AND
621 (p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
623 -- Make a list of all the distinct operator names being used in particular
624 -- strategy slots. This is a bit hokey, since the list might need to change
625 -- in future releases, but it's an effective way of spotting mistakes such as
626 -- swapping two operators within a family.
628 SELECT DISTINCT amopmethod, amopstrategy, oprname
629 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
632 -- Check that all operators linked to by opclass entries have selectivity
633 -- estimators. This is not absolutely required, but it seems a reasonable
634 -- thing to insist on for all standard datatypes.
636 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
637 FROM pg_amop AS p1, pg_operator AS p2
638 WHERE p1.amopopr = p2.oid AND
639 (p2.oprrest = 0 OR p2.oprjoin = 0);
641 -- Check that each opclass in an opfamily has associated operators, that is
642 -- ones whose oprleft matches opcintype (possibly by coercion).
644 SELECT p1.opcname, p1.opcfamily
645 FROM pg_opclass AS p1
646 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
647 WHERE p2.amopfamily = p1.opcfamily
648 AND binary_coercible(p1.opcintype, p2.amoplefttype));
650 -- Operators that are primary members of opclasses must be immutable (else
651 -- it suggests that the index ordering isn't fixed). Operators that are
652 -- cross-type members need only be stable, since they are just shorthands
653 -- for index probe queries.
655 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
656 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
657 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
658 p1.amoplefttype = p1.amoprighttype AND
659 p3.provolatile != 'i';
661 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
662 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
663 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
664 p1.amoplefttype != p1.amoprighttype AND
665 p3.provolatile = 'v';
667 -- Multiple-datatype btree opfamilies should provide closed sets of equality
668 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
669 -- should also provide int2 = int8 (and commutators of all these). This is
670 -- important because the planner tries to deduce additional qual clauses from
671 -- transitivity of mergejoinable operators. If there are clauses
672 -- int2var = int4var and int4var = int8var, the planner will want to deduce
673 -- int2var = int8var ... so there should be a way to represent that. While
674 -- a missing cross-type operator is now only an efficiency loss rather than
675 -- an error condition, it still seems reasonable to insist that all built-in
676 -- opfamilies be complete.
678 -- check commutative closure
679 SELECT p1.amoplefttype, p1.amoprighttype
681 WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
682 p1.amopstrategy = 3 AND
683 p1.amoplefttype != p1.amoprighttype AND
684 NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
685 p2.amopfamily = p1.amopfamily AND
686 p2.amoplefttype = p1.amoprighttype AND
687 p2.amoprighttype = p1.amoplefttype AND
688 p2.amopstrategy = 3);
690 -- check transitive closure
691 SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
692 FROM pg_amop AS p1, pg_amop AS p2
693 WHERE p1.amopfamily = p2.amopfamily AND
694 p1.amoprighttype = p2.amoplefttype AND
695 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
696 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
697 p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
698 p1.amoplefttype != p1.amoprighttype AND
699 p2.amoplefttype != p2.amoprighttype AND
700 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
701 p3.amopfamily = p1.amopfamily AND
702 p3.amoplefttype = p1.amoplefttype AND
703 p3.amoprighttype = p2.amoprighttype AND
704 p3.amopstrategy = 3);
706 -- We also expect that built-in multiple-datatype hash opfamilies provide
707 -- complete sets of cross-type operators. Again, this isn't required, but
708 -- it is reasonable to expect it for built-in opfamilies.
710 -- if same family has x=x and y=y, it should have x=y
711 SELECT p1.amoplefttype, p2.amoplefttype
712 FROM pg_amop AS p1, pg_amop AS p2
713 WHERE p1.amopfamily = p2.amopfamily AND
714 p1.amoplefttype = p1.amoprighttype AND
715 p2.amoplefttype = p2.amoprighttype AND
716 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
717 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
718 p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
719 p1.amoplefttype != p2.amoplefttype AND
720 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
721 p3.amopfamily = p1.amopfamily AND
722 p3.amoplefttype = p1.amoplefttype AND
723 p3.amoprighttype = p2.amoplefttype AND
724 p3.amopstrategy = 1);
727 -- **************** pg_amproc ****************
729 -- Look for illegal values in pg_amproc fields
731 SELECT p1.amprocfamily, p1.amprocnum
733 WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
734 OR p1.amprocnum < 1 OR p1.amproc = 0;
736 -- Cross-check amprocnum index against parent AM
738 SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
739 FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
740 WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
741 p1.amprocnum > p2.amsupport;
743 -- Detect missing pg_amproc entries: should have as many support functions
744 -- as AM expects for each datatype combination supported by the opfamily.
746 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
747 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
748 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
749 p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
750 WHERE p4.amprocfamily = p2.oid AND
751 p4.amproclefttype = p3.amproclefttype AND
752 p4.amprocrighttype = p3.amprocrighttype);
754 -- Unfortunately, we can't check the amproc link very well because the
755 -- signature of the function may be different for different support routines
756 -- or different base data types.
757 -- We can check that all the referenced instances of the same support
758 -- routine number take the same number of parameters, but that's about it
759 -- for a general check...
761 SELECT p1.amprocfamily, p1.amprocnum,
764 p4.amprocfamily, p4.amprocnum,
767 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
768 pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
769 WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
770 p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
771 p1.amproc = p2.oid AND p4.amproc = p5.oid AND
772 (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
774 -- For btree, though, we can do better since we know the support routines
775 -- must be of the form cmp(lefttype, righttype) returns int4.
777 SELECT p1.amprocfamily, p1.amprocnum,
780 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
781 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
782 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
785 OR prorettype != 'int4'::regtype
787 OR proargtypes[0] != amproclefttype
788 OR proargtypes[1] != amprocrighttype);
790 -- For hash we can also do a little better: the support routines must be
791 -- of the form hash(lefttype) returns int4. There are several cases where
792 -- we cheat and use a hash function that is physically compatible with the
793 -- datatype even though there's no cast, so for now we can't check that.
795 SELECT p1.amprocfamily, p1.amprocnum,
798 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
799 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
800 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
803 OR prorettype != 'int4'::regtype
805 -- OR NOT physically_coercible(amproclefttype, proargtypes[0])
806 OR amproclefttype != amprocrighttype);
808 -- Support routines that are primary members of opfamilies must be immutable
809 -- (else it suggests that the index ordering isn't fixed). But cross-type
810 -- members need only be stable, since they are just shorthands
811 -- for index probe queries.
813 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
814 FROM pg_amproc AS p1, pg_proc AS p2
815 WHERE p1.amproc = p2.oid AND
816 p1.amproclefttype = p1.amprocrighttype AND
817 p2.provolatile != 'i';
819 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
820 FROM pg_amproc AS p1, pg_proc AS p2
821 WHERE p1.amproc = p2.oid AND
822 p1.amproclefttype != p1.amprocrighttype AND
823 p2.provolatile = 'v';