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 -- prosrc should never be null or empty
61 SELECT p1.oid, p1.proname
63 WHERE prosrc IS NULL OR prosrc = '' OR prosrc = '-';
65 -- probin should be non-empty for C functions, null everywhere else
66 SELECT p1.oid, p1.proname
68 WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-');
70 SELECT p1.oid, p1.proname
72 WHERE prolang != 13 AND probin IS NOT NULL;
74 -- Look for conflicting proc definitions (same names and input datatypes).
75 -- (This test should be dead code now that we have the unique index
76 -- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.)
78 SELECT p1.oid, p1.proname, p2.oid, p2.proname
79 FROM pg_proc AS p1, pg_proc AS p2
80 WHERE p1.oid != p2.oid AND
81 p1.proname = p2.proname AND
82 p1.pronargs = p2.pronargs AND
83 p1.proargtypes = p2.proargtypes;
85 -- Considering only built-in procs (prolang = 12), look for multiple uses
86 -- of the same internal function (ie, matching prosrc fields). It's OK to
87 -- have several entries with different pronames for the same internal function,
88 -- but conflicts in the number of arguments and other critical items should
89 -- be complained of. (We don't check data types here; see next query.)
90 -- Note: ignore aggregate functions here, since they all point to the same
91 -- dummy built-in function.
93 SELECT p1.oid, p1.proname, p2.oid, p2.proname
94 FROM pg_proc AS p1, pg_proc AS p2
95 WHERE p1.oid < p2.oid AND
96 p1.prosrc = p2.prosrc AND
97 p1.prolang = 12 AND p2.prolang = 12 AND
98 (p1.proisagg = false OR p2.proisagg = false) AND
99 (p1.prolang != p2.prolang OR
100 p1.proisagg != p2.proisagg OR
101 p1.prosecdef != p2.prosecdef OR
102 p1.proisstrict != p2.proisstrict OR
103 p1.proretset != p2.proretset OR
104 p1.provolatile != p2.provolatile OR
105 p1.pronargs != p2.pronargs);
107 -- Look for uses of different type OIDs in the argument/result type fields
108 -- for different aliases of the same built-in function.
109 -- This indicates that the types are being presumed to be binary-equivalent,
110 -- or that the built-in function is prepared to deal with different types.
111 -- That's not wrong, necessarily, but we make lists of all the types being
112 -- so treated. Note that the expected output of this part of the test will
113 -- need to be modified whenever new pairs of types are made binary-equivalent,
114 -- or when new polymorphic built-in functions are added!
115 -- Note: ignore aggregate functions here, since they all point to the same
116 -- dummy built-in function.
118 SELECT DISTINCT p1.prorettype, p2.prorettype
119 FROM pg_proc AS p1, pg_proc AS p2
120 WHERE p1.oid != p2.oid AND
121 p1.prosrc = p2.prosrc AND
122 p1.prolang = 12 AND p2.prolang = 12 AND
123 NOT p1.proisagg AND NOT p2.proisagg AND
124 (p1.prorettype < p2.prorettype);
126 SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
127 FROM pg_proc AS p1, pg_proc AS p2
128 WHERE p1.oid != p2.oid AND
129 p1.prosrc = p2.prosrc AND
130 p1.prolang = 12 AND p2.prolang = 12 AND
131 NOT p1.proisagg AND NOT p2.proisagg AND
132 (p1.proargtypes[0] < p2.proargtypes[0]);
134 SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
135 FROM pg_proc AS p1, pg_proc AS p2
136 WHERE p1.oid != p2.oid AND
137 p1.prosrc = p2.prosrc AND
138 p1.prolang = 12 AND p2.prolang = 12 AND
139 NOT p1.proisagg AND NOT p2.proisagg AND
140 (p1.proargtypes[1] < p2.proargtypes[1]);
142 SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
143 FROM pg_proc AS p1, pg_proc AS p2
144 WHERE p1.oid != p2.oid AND
145 p1.prosrc = p2.prosrc AND
146 p1.prolang = 12 AND p2.prolang = 12 AND
147 NOT p1.proisagg AND NOT p2.proisagg AND
148 (p1.proargtypes[2] < p2.proargtypes[2]);
150 SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
151 FROM pg_proc AS p1, pg_proc AS p2
152 WHERE p1.oid != p2.oid AND
153 p1.prosrc = p2.prosrc AND
154 p1.prolang = 12 AND p2.prolang = 12 AND
155 NOT p1.proisagg AND NOT p2.proisagg AND
156 (p1.proargtypes[3] < p2.proargtypes[3]);
158 SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
159 FROM pg_proc AS p1, pg_proc AS p2
160 WHERE p1.oid != p2.oid AND
161 p1.prosrc = p2.prosrc AND
162 p1.prolang = 12 AND p2.prolang = 12 AND
163 NOT p1.proisagg AND NOT p2.proisagg AND
164 (p1.proargtypes[4] < p2.proargtypes[4]);
166 SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
167 FROM pg_proc AS p1, pg_proc AS p2
168 WHERE p1.oid != p2.oid AND
169 p1.prosrc = p2.prosrc AND
170 p1.prolang = 12 AND p2.prolang = 12 AND
171 NOT p1.proisagg AND NOT p2.proisagg AND
172 (p1.proargtypes[5] < p2.proargtypes[5]);
174 SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
175 FROM pg_proc AS p1, pg_proc AS p2
176 WHERE p1.oid != p2.oid AND
177 p1.prosrc = p2.prosrc AND
178 p1.prolang = 12 AND p2.prolang = 12 AND
179 NOT p1.proisagg AND NOT p2.proisagg AND
180 (p1.proargtypes[6] < p2.proargtypes[6]);
182 SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
183 FROM pg_proc AS p1, pg_proc AS p2
184 WHERE p1.oid != p2.oid AND
185 p1.prosrc = p2.prosrc AND
186 p1.prolang = 12 AND p2.prolang = 12 AND
187 NOT p1.proisagg AND NOT p2.proisagg AND
188 (p1.proargtypes[7] < p2.proargtypes[7]);
190 -- Look for functions that return type "internal" and do not have any
191 -- "internal" argument. Such a function would be a security hole since
192 -- it might be used to call an internal function from an SQL command.
193 -- As of 7.3 this query should find only internal_in.
195 SELECT p1.oid, p1.proname
197 WHERE p1.prorettype = 'internal'::regtype AND NOT
198 'internal'::regtype = ANY (p1.proargtypes);
201 -- **************** pg_cast ****************
203 -- Catch bogus values in pg_cast columns (other than cases detected by
208 WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i');
210 -- Look for casts to/from the same type that aren't length coercion functions.
211 -- (We assume they are length coercions if they take multiple arguments.)
212 -- Such entries are not necessarily harmful, but they are useless.
216 WHERE castsource = casttarget AND castfunc = 0;
219 FROM pg_cast c, pg_proc p
220 WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
222 -- Look for cast functions that don't have the right signature. The
223 -- argument and result types in pg_proc must be the same as, or binary
224 -- compatible with, what it says in pg_cast.
225 -- As a special case, we allow casts from CHAR(n) that use functions
226 -- declared to take TEXT. This does not pass the binary-coercibility test
227 -- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
228 -- are the same, so long as the function is one that ignores trailing blanks.
231 FROM pg_cast c, pg_proc p
232 WHERE c.castfunc = p.oid AND
233 (p.pronargs < 1 OR p.pronargs > 3
234 OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
235 OR (c.castsource = 'character'::regtype AND
236 p.proargtypes[0] = 'text'::regtype))
237 OR NOT binary_coercible(p.prorettype, c.casttarget));
240 FROM pg_cast c, pg_proc p
241 WHERE c.castfunc = p.oid AND
242 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
243 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
245 -- Look for binary compatible casts that do not have the reverse
246 -- direction registered as well, or where the reverse direction is not
247 -- also binary compatible. This is legal, but usually not intended.
249 -- As of 7.4, this finds the casts from text and varchar to bpchar, because
250 -- those are binary-compatible while the reverse way goes through rtrim().
252 -- As of 8.2, this finds the cast from cidr to inet, because that is a
253 -- trivial binary coercion while the other way goes through inet_to_cidr().
255 -- As of 8.3, this finds the casts from xml to text, varchar, and bpchar,
256 -- because those are binary-compatible while the reverse goes through
257 -- texttoxml(), which does an XML syntax check.
261 WHERE c.castfunc = 0 AND
262 NOT EXISTS (SELECT 1 FROM pg_cast k
263 WHERE k.castfunc = 0 AND
264 k.castsource = c.casttarget AND
265 k.casttarget = c.castsource);
267 -- **************** pg_operator ****************
269 -- Look for illegal values in pg_operator fields.
271 SELECT p1.oid, p1.oprname
272 FROM pg_operator as p1
273 WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
274 p1.oprresult = 0 OR p1.oprcode = 0;
276 -- Look for missing or unwanted operand types
278 SELECT p1.oid, p1.oprname
279 FROM pg_operator as p1
280 WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
281 (p1.oprleft != 0 and p1.oprkind = 'l') OR
282 (p1.oprright = 0 and p1.oprkind != 'r') OR
283 (p1.oprright != 0 and p1.oprkind = 'r');
285 -- Look for conflicting operator definitions (same names and input datatypes).
287 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
288 FROM pg_operator AS p1, pg_operator AS p2
289 WHERE p1.oid != p2.oid AND
290 p1.oprname = p2.oprname AND
291 p1.oprkind = p2.oprkind AND
292 p1.oprleft = p2.oprleft AND
293 p1.oprright = p2.oprright;
295 -- Look for commutative operators that don't commute.
296 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
297 -- We expect that B will always say that B.oprcom = A as well; that's not
298 -- inherently essential, but it would be inefficient not to mark it so.
300 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
301 FROM pg_operator AS p1, pg_operator AS p2
302 WHERE p1.oprcom = p2.oid AND
303 (p1.oprkind != 'b' OR
304 p1.oprleft != p2.oprright OR
305 p1.oprright != p2.oprleft OR
306 p1.oprresult != p2.oprresult OR
307 p1.oid != p2.oprcom);
309 -- Look for negatory operators that don't agree.
310 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
311 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
312 -- single-operand operators.
313 -- We expect that B will always say that B.oprnegate = A as well; that's not
314 -- inherently essential, but it would be inefficient not to mark it so.
315 -- Also, A and B had better not be the same operator.
317 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
318 FROM pg_operator AS p1, pg_operator AS p2
319 WHERE p1.oprnegate = p2.oid AND
320 (p1.oprkind != p2.oprkind OR
321 p1.oprleft != p2.oprleft OR
322 p1.oprright != p2.oprright OR
323 p1.oprresult != 'bool'::regtype OR
324 p2.oprresult != 'bool'::regtype OR
325 p1.oid != p2.oprnegate OR
328 -- A mergejoinable or hashjoinable operator must be binary, must return
329 -- boolean, and must have a commutator (itself, unless it's a cross-type
332 SELECT p1.oid, p1.oprname FROM pg_operator AS p1
333 WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT
334 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0);
336 -- What's more, the commutator had better be mergejoinable/hashjoinable too.
338 SELECT p1.oid, p1.oprname, p2.oid, p2.oprname
339 FROM pg_operator AS p1, pg_operator AS p2
340 WHERE p1.oprcom = p2.oid AND
341 (p1.oprcanmerge != p2.oprcanmerge OR
342 p1.oprcanhash != p2.oprcanhash);
344 -- Mergejoinable operators should appear as equality members of btree index
347 SELECT p1.oid, p1.oprname
348 FROM pg_operator AS p1
349 WHERE p1.oprcanmerge AND NOT EXISTS
350 (SELECT 1 FROM pg_amop
351 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
352 amopopr = p1.oid AND amopstrategy = 3);
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 = 'btree')
361 AND NOT p1.oprcanmerge;
363 -- Hashable operators should appear as members of hash index opfamilies.
365 SELECT p1.oid, p1.oprname
366 FROM pg_operator AS p1
367 WHERE p1.oprcanhash AND NOT EXISTS
368 (SELECT 1 FROM pg_amop
369 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
370 amopopr = p1.oid AND amopstrategy = 1);
374 SELECT p1.oid, p1.oprname, p.amopfamily
375 FROM pg_operator AS p1, pg_amop p
376 WHERE amopopr = p1.oid
377 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
378 AND NOT p1.oprcanhash;
380 -- Check that each operator defined in pg_operator matches its oprcode entry
381 -- in pg_proc. Easiest to do this separately for each oprkind.
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 NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
392 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
393 FROM pg_operator AS p1, pg_proc AS p2
394 WHERE p1.oprcode = p2.oid AND
397 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
398 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
401 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
402 FROM pg_operator AS p1, pg_proc AS p2
403 WHERE p1.oprcode = p2.oid AND
406 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
407 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
408 OR p1.oprright != 0);
410 -- If the operator is mergejoinable or hashjoinable, its underlying function
411 -- should not be volatile.
413 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
414 FROM pg_operator AS p1, pg_proc AS p2
415 WHERE p1.oprcode = p2.oid AND
416 (p1.oprcanmerge OR p1.oprcanhash) AND
417 p2.provolatile = 'v';
419 -- If oprrest is set, the operator must return boolean,
420 -- and it must link to a proc with the right signature
421 -- to be a restriction selectivity estimator.
422 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
424 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
425 FROM pg_operator AS p1, pg_proc AS p2
426 WHERE p1.oprrest = p2.oid AND
427 (p1.oprresult != 'bool'::regtype OR
428 p2.prorettype != 'float8'::regtype OR p2.proretset OR
430 p2.proargtypes[0] != 'internal'::regtype OR
431 p2.proargtypes[1] != 'oid'::regtype OR
432 p2.proargtypes[2] != 'internal'::regtype OR
433 p2.proargtypes[3] != 'int4'::regtype);
435 -- If oprjoin is set, the operator must be a binary boolean op,
436 -- and it must link to a proc with the right signature
437 -- to be a join selectivity estimator.
438 -- The proc signature we want is: float8 proc(internal, oid, internal, int2)
440 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
441 FROM pg_operator AS p1, pg_proc AS p2
442 WHERE p1.oprjoin = p2.oid AND
443 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
444 p2.prorettype != 'float8'::regtype OR p2.proretset OR
446 p2.proargtypes[0] != 'internal'::regtype OR
447 p2.proargtypes[1] != 'oid'::regtype OR
448 p2.proargtypes[2] != 'internal'::regtype OR
449 p2.proargtypes[3] != 'int2'::regtype);
451 -- **************** pg_aggregate ****************
453 -- Look for illegal values in pg_aggregate fields.
455 SELECT ctid, aggfnoid::oid
456 FROM pg_aggregate as p1
457 WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
459 -- Make sure the matching pg_proc entry is sensible, too.
461 SELECT a.aggfnoid::oid, p.proname
462 FROM pg_aggregate as a, pg_proc as p
463 WHERE a.aggfnoid = p.oid AND
464 (NOT p.proisagg OR p.proretset);
466 -- Make sure there are no proisagg pg_proc entries without matches.
471 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
473 -- If there is no finalfn then the output type must be the transtype.
475 SELECT a.aggfnoid::oid, p.proname
476 FROM pg_aggregate as a, pg_proc as p
477 WHERE a.aggfnoid = p.oid AND
478 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
480 -- Cross-check transfn against its entry in pg_proc.
481 -- NOTE: use physically_coercible here, not binary_coercible, because
482 -- max and min on abstime are implemented using int4larger/int4smaller.
483 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
484 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
485 WHERE a.aggfnoid = p.oid AND
486 a.aggtransfn = ptr.oid AND
488 OR NOT (ptr.pronargs = p.pronargs + 1)
489 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
490 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
491 OR (p.pronargs > 0 AND
492 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
493 OR (p.pronargs > 1 AND
494 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
495 OR (p.pronargs > 2 AND
496 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
497 -- we could carry the check further, but that's enough for now
500 -- Cross-check finalfn (if present) against its entry in pg_proc.
502 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
503 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
504 WHERE a.aggfnoid = p.oid AND
505 a.aggfinalfn = pfn.oid AND
507 OR NOT binary_coercible(pfn.prorettype, p.prorettype)
509 OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
511 -- If transfn is strict then either initval should be non-NULL, or
512 -- input type should match transtype so that the first non-null input
513 -- can be assigned as the state value.
515 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
516 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
517 WHERE a.aggfnoid = p.oid AND
518 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
519 a.agginitval IS NULL AND
520 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
522 -- Cross-check aggsortop (if present) against pg_operator.
523 -- We expect to find only "<" for "min" and ">" for "max".
525 SELECT DISTINCT proname, oprname
526 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
527 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
530 -- Check datatypes match
532 SELECT a.aggfnoid::oid, o.oid
533 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
534 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
535 (oprkind != 'b' OR oprresult != 'boolean'::regtype
536 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
538 -- Check operator is a suitable btree opfamily member
540 SELECT a.aggfnoid::oid, o.oid
541 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
542 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
543 NOT EXISTS(SELECT 1 FROM pg_amop
544 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
546 AND amoplefttype = o.oprleft
547 AND amoprighttype = o.oprright);
549 -- Check correspondence of btree strategies and names
551 SELECT DISTINCT proname, oprname, amopstrategy
552 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
554 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
556 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
559 -- **************** pg_opfamily ****************
561 -- Look for illegal values in pg_opfamily fields
564 FROM pg_opfamily as p1
565 WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
567 -- **************** pg_opclass ****************
569 -- Look for illegal values in pg_opclass fields
572 FROM pg_opclass AS p1
573 WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
576 -- opcmethod must match owning opfamily's opfmethod
578 SELECT p1.oid, p2.oid
579 FROM pg_opclass AS p1, pg_opfamily AS p2
580 WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
582 -- There should not be multiple entries in pg_opclass with opcdefault true
583 -- and the same opcmethod/opcintype combination.
585 SELECT p1.oid, p2.oid
586 FROM pg_opclass AS p1, pg_opclass AS p2
587 WHERE p1.oid != p2.oid AND
588 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
589 p1.opcdefault AND p2.opcdefault;
591 -- **************** pg_amop ****************
593 -- Look for illegal values in pg_amop fields
595 SELECT p1.amopfamily, p1.amopstrategy
597 WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
598 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
600 -- amoplefttype/amoprighttype must match the operator
602 SELECT p1.oid, p2.oid
603 FROM pg_amop AS p1, pg_operator AS p2
604 WHERE p1.amopopr = p2.oid AND NOT
605 (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);
607 -- amopmethod must match owning opfamily's opfmethod
609 SELECT p1.oid, p2.oid
610 FROM pg_amop AS p1, pg_opfamily AS p2
611 WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
613 -- Cross-check amopstrategy index against parent AM
615 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
616 FROM pg_amop AS p1, pg_am AS p2
617 WHERE p1.amopmethod = p2.oid AND
618 p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
620 -- Detect missing pg_amop entries: should have as many strategy operators
621 -- as AM expects for each datatype combination supported by the opfamily.
622 -- We can't check this for AMs with variable strategy sets.
624 SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
625 FROM pg_am AS p1, pg_amop AS p2
626 WHERE p2.amopmethod = p1.oid AND
627 p1.amstrategies <> 0 AND
628 p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
629 WHERE p3.amopfamily = p2.amopfamily AND
630 p3.amoplefttype = p2.amoplefttype AND
631 p3.amoprighttype = p2.amoprighttype);
633 -- Check that amopopr points at a reasonable-looking operator, ie a binary
634 -- operator yielding boolean.
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.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
641 -- Make a list of all the distinct operator names being used in particular
642 -- strategy slots. This is a bit hokey, since the list might need to change
643 -- in future releases, but it's an effective way of spotting mistakes such as
644 -- swapping two operators within a family.
646 SELECT DISTINCT amopmethod, amopstrategy, oprname
647 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
650 -- Check that all operators linked to by opclass entries have selectivity
651 -- estimators. This is not absolutely required, but it seems a reasonable
652 -- thing to insist on for all standard datatypes.
654 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
655 FROM pg_amop AS p1, pg_operator AS p2
656 WHERE p1.amopopr = p2.oid AND
657 (p2.oprrest = 0 OR p2.oprjoin = 0);
659 -- Check that each opclass in an opfamily has associated operators, that is
660 -- ones whose oprleft matches opcintype (possibly by coercion).
662 SELECT p1.opcname, p1.opcfamily
663 FROM pg_opclass AS p1
664 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
665 WHERE p2.amopfamily = p1.opcfamily
666 AND binary_coercible(p1.opcintype, p2.amoplefttype));
668 -- Operators that are primary members of opclasses must be immutable (else
669 -- it suggests that the index ordering isn't fixed). Operators that are
670 -- cross-type members need only be stable, since they are just shorthands
671 -- for index probe queries.
673 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
674 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
675 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
676 p1.amoplefttype = p1.amoprighttype AND
677 p3.provolatile != 'i';
679 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
680 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
681 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
682 p1.amoplefttype != p1.amoprighttype AND
683 p3.provolatile = 'v';
685 -- Multiple-datatype btree opfamilies should provide closed sets of equality
686 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
687 -- should also provide int2 = int8 (and commutators of all these). This is
688 -- important because the planner tries to deduce additional qual clauses from
689 -- transitivity of mergejoinable operators. If there are clauses
690 -- int2var = int4var and int4var = int8var, the planner will want to deduce
691 -- int2var = int8var ... so there should be a way to represent that. While
692 -- a missing cross-type operator is now only an efficiency loss rather than
693 -- an error condition, it still seems reasonable to insist that all built-in
694 -- opfamilies be complete.
696 -- check commutative closure
697 SELECT p1.amoplefttype, p1.amoprighttype
699 WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
700 p1.amopstrategy = 3 AND
701 p1.amoplefttype != p1.amoprighttype AND
702 NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
703 p2.amopfamily = p1.amopfamily AND
704 p2.amoplefttype = p1.amoprighttype AND
705 p2.amoprighttype = p1.amoplefttype AND
706 p2.amopstrategy = 3);
708 -- check transitive closure
709 SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
710 FROM pg_amop AS p1, pg_amop AS p2
711 WHERE p1.amopfamily = p2.amopfamily AND
712 p1.amoprighttype = p2.amoplefttype AND
713 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
714 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
715 p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
716 p1.amoplefttype != p1.amoprighttype AND
717 p2.amoplefttype != p2.amoprighttype AND
718 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
719 p3.amopfamily = p1.amopfamily AND
720 p3.amoplefttype = p1.amoplefttype AND
721 p3.amoprighttype = p2.amoprighttype AND
722 p3.amopstrategy = 3);
724 -- We also expect that built-in multiple-datatype hash opfamilies provide
725 -- complete sets of cross-type operators. Again, this isn't required, but
726 -- it is reasonable to expect it for built-in opfamilies.
728 -- if same family has x=x and y=y, it should have x=y
729 SELECT p1.amoplefttype, p2.amoplefttype
730 FROM pg_amop AS p1, pg_amop AS p2
731 WHERE p1.amopfamily = p2.amopfamily AND
732 p1.amoplefttype = p1.amoprighttype AND
733 p2.amoplefttype = p2.amoprighttype AND
734 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
735 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
736 p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
737 p1.amoplefttype != p2.amoplefttype AND
738 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
739 p3.amopfamily = p1.amopfamily AND
740 p3.amoplefttype = p1.amoplefttype AND
741 p3.amoprighttype = p2.amoplefttype AND
742 p3.amopstrategy = 1);
745 -- **************** pg_amproc ****************
747 -- Look for illegal values in pg_amproc fields
749 SELECT p1.amprocfamily, p1.amprocnum
751 WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
752 OR p1.amprocnum < 1 OR p1.amproc = 0;
754 -- Cross-check amprocnum index against parent AM
756 SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
757 FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
758 WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
759 p1.amprocnum > p2.amsupport;
761 -- Detect missing pg_amproc entries: should have as many support functions
762 -- as AM expects for each datatype combination supported by the opfamily.
763 -- GIN is a special case because it has an optional support function.
765 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
766 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
767 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
768 p1.amname <> 'gin' AND
769 p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
770 WHERE p4.amprocfamily = p2.oid AND
771 p4.amproclefttype = p3.amproclefttype AND
772 p4.amprocrighttype = p3.amprocrighttype);
774 -- Similar check for GIN, allowing one optional proc
776 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
777 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
778 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
779 p1.amname = 'gin' AND
780 p1.amsupport - 1 > (SELECT count(*) FROM pg_amproc AS p4
781 WHERE p4.amprocfamily = p2.oid AND
782 p4.amproclefttype = p3.amproclefttype AND
783 p4.amprocrighttype = p3.amprocrighttype);
785 -- Also, check if there are any pg_opclass entries that don't seem to have
786 -- pg_amproc support. Again, GIN has to be checked separately.
788 SELECT amname, opcname, count(*)
789 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
790 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
791 amproclefttype = amprocrighttype AND amproclefttype = opcintype
792 WHERE am.amname <> 'gin'
793 GROUP BY amname, amsupport, opcname, amprocfamily
794 HAVING count(*) != amsupport OR amprocfamily IS NULL;
796 SELECT amname, opcname, count(*)
797 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
798 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
799 amproclefttype = amprocrighttype AND amproclefttype = opcintype
800 WHERE am.amname = 'gin'
801 GROUP BY amname, amsupport, opcname, amprocfamily
802 HAVING count(*) < amsupport - 1 OR amprocfamily IS NULL;
804 -- Unfortunately, we can't check the amproc link very well because the
805 -- signature of the function may be different for different support routines
806 -- or different base data types.
807 -- We can check that all the referenced instances of the same support
808 -- routine number take the same number of parameters, but that's about it
809 -- for a general check...
811 SELECT p1.amprocfamily, p1.amprocnum,
814 p4.amprocfamily, p4.amprocnum,
817 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
818 pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
819 WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
820 p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
821 p1.amproc = p2.oid AND p4.amproc = p5.oid AND
822 (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
824 -- For btree, though, we can do better since we know the support routines
825 -- must be of the form cmp(lefttype, righttype) returns int4.
827 SELECT p1.amprocfamily, p1.amprocnum,
830 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
831 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
832 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
835 OR prorettype != 'int4'::regtype
837 OR proargtypes[0] != amproclefttype
838 OR proargtypes[1] != amprocrighttype);
840 -- For hash we can also do a little better: the support routines must be
841 -- of the form hash(lefttype) returns int4. There are several cases where
842 -- we cheat and use a hash function that is physically compatible with the
843 -- datatype even though there's no cast, so this check does find a small
844 -- number of entries.
846 SELECT p1.amprocfamily, p1.amprocnum, p2.proname, p3.opfname
847 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
848 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
849 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
852 OR prorettype != 'int4'::regtype
854 OR NOT physically_coercible(amproclefttype, proargtypes[0])
855 OR amproclefttype != amprocrighttype)
858 -- Support routines that are primary members of opfamilies must be immutable
859 -- (else it suggests that the index ordering isn't fixed). But cross-type
860 -- members need only be stable, since they are just shorthands
861 -- for index probe queries.
863 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
864 FROM pg_amproc AS p1, pg_proc AS p2
865 WHERE p1.amproc = p2.oid AND
866 p1.amproclefttype = p1.amprocrighttype AND
867 p2.provolatile != 'i';
869 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
870 FROM pg_amproc AS p1, pg_proc AS p2
871 WHERE p1.amproc = p2.oid AND
872 p1.amproclefttype != p1.amprocrighttype AND
873 p2.provolatile = 'v';