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...
18 -- Helper functions to deal with cases where binary-coercible matches are
20 -- This should match IsBinaryCoercible() in parse_coerce.c.
21 create function binary_coercible(oid, oid) returns bool as $$
23 EXISTS(select 1 from pg_catalog.pg_cast where
24 castsource = $1 and casttarget = $2 and
25 castfunc = 0 and castcontext = 'i') OR
26 ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
27 EXISTS(select 1 from pg_catalog.pg_type where
28 oid = $1 and typelem != 0 and typlen = -1))
29 $$ language sql strict stable;
30 -- This one ignores castcontext, so it considers only physical equivalence
31 -- and not whether the coercion can be invoked implicitly.
32 create function physically_coercible(oid, oid) returns bool as $$
34 EXISTS(select 1 from pg_catalog.pg_cast where
35 castsource = $1 and casttarget = $2 and
37 ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
38 EXISTS(select 1 from pg_catalog.pg_type where
39 oid = $1 and typelem != 0 and typlen = -1))
40 $$ language sql strict stable;
41 -- **************** pg_proc ****************
42 -- Look for illegal values in pg_proc fields.
43 SELECT p1.oid, p1.proname
45 WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
47 array_lower(p1.proargtypes, 1) != 0 OR
48 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR
49 0::oid = ANY (p1.proargtypes) OR
51 CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END;
56 -- prosrc should never be null or empty
57 SELECT p1.oid, p1.proname
59 WHERE prosrc IS NULL OR prosrc = '' OR prosrc = '-';
64 -- probin should be non-empty for C functions, null everywhere else
65 SELECT p1.oid, p1.proname
67 WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-');
72 SELECT p1.oid, p1.proname
74 WHERE prolang != 13 AND probin IS NOT NULL;
79 -- Look for conflicting proc definitions (same names and input datatypes).
80 -- (This test should be dead code now that we have the unique index
81 -- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.)
82 SELECT p1.oid, p1.proname, p2.oid, p2.proname
83 FROM pg_proc AS p1, pg_proc AS p2
84 WHERE p1.oid != p2.oid AND
85 p1.proname = p2.proname AND
86 p1.pronargs = p2.pronargs AND
87 p1.proargtypes = p2.proargtypes;
88 oid | proname | oid | proname
89 -----+---------+-----+---------
92 -- Considering only built-in procs (prolang = 12), look for multiple uses
93 -- of the same internal function (ie, matching prosrc fields). It's OK to
94 -- have several entries with different pronames for the same internal function,
95 -- but conflicts in the number of arguments and other critical items should
96 -- be complained of. (We don't check data types here; see next query.)
97 -- Note: ignore aggregate functions here, since they all point to the same
98 -- dummy built-in function.
99 SELECT p1.oid, p1.proname, p2.oid, p2.proname
100 FROM pg_proc AS p1, pg_proc AS p2
101 WHERE p1.oid < p2.oid AND
102 p1.prosrc = p2.prosrc AND
103 p1.prolang = 12 AND p2.prolang = 12 AND
104 (p1.proisagg = false OR p2.proisagg = false) AND
105 (p1.prolang != p2.prolang OR
106 p1.proisagg != p2.proisagg OR
107 p1.prosecdef != p2.prosecdef OR
108 p1.proisstrict != p2.proisstrict OR
109 p1.proretset != p2.proretset OR
110 p1.provolatile != p2.provolatile OR
111 p1.pronargs != p2.pronargs);
112 oid | proname | oid | proname
113 -----+---------+-----+---------
116 -- Look for uses of different type OIDs in the argument/result type fields
117 -- for different aliases of the same built-in function.
118 -- This indicates that the types are being presumed to be binary-equivalent,
119 -- or that the built-in function is prepared to deal with different types.
120 -- That's not wrong, necessarily, but we make lists of all the types being
121 -- so treated. Note that the expected output of this part of the test will
122 -- need to be modified whenever new pairs of types are made binary-equivalent,
123 -- or when new polymorphic built-in functions are added!
124 -- Note: ignore aggregate functions here, since they all point to the same
125 -- dummy built-in function.
126 SELECT DISTINCT p1.prorettype, p2.prorettype
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.prorettype < p2.prorettype)
134 prorettype | prorettype
135 ------------+------------
140 SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
141 FROM pg_proc AS p1, pg_proc AS p2
142 WHERE p1.oid != p2.oid AND
143 p1.prosrc = p2.prosrc AND
144 p1.prolang = 12 AND p2.prolang = 12 AND
145 NOT p1.proisagg AND NOT p2.proisagg AND
146 (p1.proargtypes[0] < p2.proargtypes[0])
148 proargtypes | proargtypes
149 -------------+-------------
157 SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
158 FROM pg_proc AS p1, pg_proc AS p2
159 WHERE p1.oid != p2.oid AND
160 p1.prosrc = p2.prosrc AND
161 p1.prolang = 12 AND p2.prolang = 12 AND
162 NOT p1.proisagg AND NOT p2.proisagg AND
163 (p1.proargtypes[1] < p2.proargtypes[1])
165 proargtypes | proargtypes
166 -------------+-------------
173 SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
174 FROM pg_proc AS p1, pg_proc AS p2
175 WHERE p1.oid != p2.oid AND
176 p1.prosrc = p2.prosrc AND
177 p1.prolang = 12 AND p2.prolang = 12 AND
178 NOT p1.proisagg AND NOT p2.proisagg AND
179 (p1.proargtypes[2] < p2.proargtypes[2])
181 proargtypes | proargtypes
182 -------------+-------------
186 SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
187 FROM pg_proc AS p1, pg_proc AS p2
188 WHERE p1.oid != p2.oid AND
189 p1.prosrc = p2.prosrc AND
190 p1.prolang = 12 AND p2.prolang = 12 AND
191 NOT p1.proisagg AND NOT p2.proisagg AND
192 (p1.proargtypes[3] < p2.proargtypes[3])
194 proargtypes | proargtypes
195 -------------+-------------
199 SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
200 FROM pg_proc AS p1, pg_proc AS p2
201 WHERE p1.oid != p2.oid AND
202 p1.prosrc = p2.prosrc AND
203 p1.prolang = 12 AND p2.prolang = 12 AND
204 NOT p1.proisagg AND NOT p2.proisagg AND
205 (p1.proargtypes[4] < p2.proargtypes[4])
207 proargtypes | proargtypes
208 -------------+-------------
211 SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
212 FROM pg_proc AS p1, pg_proc AS p2
213 WHERE p1.oid != p2.oid AND
214 p1.prosrc = p2.prosrc AND
215 p1.prolang = 12 AND p2.prolang = 12 AND
216 NOT p1.proisagg AND NOT p2.proisagg AND
217 (p1.proargtypes[5] < p2.proargtypes[5])
219 proargtypes | proargtypes
220 -------------+-------------
223 SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
224 FROM pg_proc AS p1, pg_proc AS p2
225 WHERE p1.oid != p2.oid AND
226 p1.prosrc = p2.prosrc AND
227 p1.prolang = 12 AND p2.prolang = 12 AND
228 NOT p1.proisagg AND NOT p2.proisagg AND
229 (p1.proargtypes[6] < p2.proargtypes[6])
231 proargtypes | proargtypes
232 -------------+-------------
235 SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
236 FROM pg_proc AS p1, pg_proc AS p2
237 WHERE p1.oid != p2.oid AND
238 p1.prosrc = p2.prosrc AND
239 p1.prolang = 12 AND p2.prolang = 12 AND
240 NOT p1.proisagg AND NOT p2.proisagg AND
241 (p1.proargtypes[7] < p2.proargtypes[7])
243 proargtypes | proargtypes
244 -------------+-------------
247 -- Look for functions that return type "internal" and do not have any
248 -- "internal" argument. Such a function would be a security hole since
249 -- it might be used to call an internal function from an SQL command.
250 -- As of 7.3 this query should find only internal_in.
251 SELECT p1.oid, p1.proname
253 WHERE p1.prorettype = 'internal'::regtype AND NOT
254 'internal'::regtype = ANY (p1.proargtypes);
260 -- **************** pg_cast ****************
261 -- Catch bogus values in pg_cast columns (other than cases detected by
265 WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i');
266 castsource | casttarget | castfunc | castcontext
267 ------------+------------+----------+-------------
270 -- Look for casts to/from the same type that aren't length coercion functions.
271 -- (We assume they are length coercions if they take multiple arguments.)
272 -- Such entries are not necessarily harmful, but they are useless.
275 WHERE castsource = casttarget AND castfunc = 0;
276 castsource | casttarget | castfunc | castcontext
277 ------------+------------+----------+-------------
281 FROM pg_cast c, pg_proc p
282 WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
283 castsource | casttarget | castfunc | castcontext
284 ------------+------------+----------+-------------
287 -- Look for cast functions that don't have the right signature. The
288 -- argument and result types in pg_proc must be the same as, or binary
289 -- compatible with, what it says in pg_cast.
290 -- As a special case, we allow casts from CHAR(n) that use functions
291 -- declared to take TEXT. This does not pass the binary-coercibility test
292 -- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
293 -- are the same, so long as the function is one that ignores trailing blanks.
295 FROM pg_cast c, pg_proc p
296 WHERE c.castfunc = p.oid AND
297 (p.pronargs < 1 OR p.pronargs > 3
298 OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
299 OR (c.castsource = 'character'::regtype AND
300 p.proargtypes[0] = 'text'::regtype))
301 OR NOT binary_coercible(p.prorettype, c.casttarget));
302 castsource | casttarget | castfunc | castcontext
303 ------------+------------+----------+-------------
307 FROM pg_cast c, pg_proc p
308 WHERE c.castfunc = p.oid AND
309 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
310 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
311 castsource | casttarget | castfunc | castcontext
312 ------------+------------+----------+-------------
315 -- Look for binary compatible casts that do not have the reverse
316 -- direction registered as well, or where the reverse direction is not
317 -- also binary compatible. This is legal, but usually not intended.
318 -- As of 7.4, this finds the casts from text and varchar to bpchar, because
319 -- those are binary-compatible while the reverse way goes through rtrim().
320 -- As of 8.2, this finds the cast from cidr to inet, because that is a
321 -- trivial binary coercion while the other way goes through inet_to_cidr().
322 -- As of 8.3, this finds the casts from xml to text, varchar, and bpchar,
323 -- because those are binary-compatible while the reverse goes through
324 -- texttoxml(), which does an XML syntax check.
327 WHERE c.castfunc = 0 AND
328 NOT EXISTS (SELECT 1 FROM pg_cast k
329 WHERE k.castfunc = 0 AND
330 k.castsource = c.casttarget AND
331 k.casttarget = c.castsource);
332 castsource | casttarget | castfunc | castcontext
333 ------------+------------+----------+-------------
342 -- **************** pg_operator ****************
343 -- Look for illegal values in pg_operator fields.
344 SELECT p1.oid, p1.oprname
345 FROM pg_operator as p1
346 WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
347 p1.oprresult = 0 OR p1.oprcode = 0;
352 -- Look for missing or unwanted operand types
353 SELECT p1.oid, p1.oprname
354 FROM pg_operator as p1
355 WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
356 (p1.oprleft != 0 and p1.oprkind = 'l') OR
357 (p1.oprright = 0 and p1.oprkind != 'r') OR
358 (p1.oprright != 0 and p1.oprkind = 'r');
363 -- Look for conflicting operator definitions (same names and input datatypes).
364 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
365 FROM pg_operator AS p1, pg_operator AS p2
366 WHERE p1.oid != p2.oid AND
367 p1.oprname = p2.oprname AND
368 p1.oprkind = p2.oprkind AND
369 p1.oprleft = p2.oprleft AND
370 p1.oprright = p2.oprright;
371 oid | oprcode | oid | oprcode
372 -----+---------+-----+---------
375 -- Look for commutative operators that don't commute.
376 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
377 -- We expect that B will always say that B.oprcom = A as well; that's not
378 -- inherently essential, but it would be inefficient not to mark it so.
379 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
380 FROM pg_operator AS p1, pg_operator AS p2
381 WHERE p1.oprcom = p2.oid AND
382 (p1.oprkind != 'b' OR
383 p1.oprleft != p2.oprright OR
384 p1.oprright != p2.oprleft OR
385 p1.oprresult != p2.oprresult OR
386 p1.oid != p2.oprcom);
387 oid | oprcode | oid | oprcode
388 -----+---------+-----+---------
391 -- Look for negatory operators that don't agree.
392 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
393 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
394 -- single-operand operators.
395 -- We expect that B will always say that B.oprnegate = A as well; that's not
396 -- inherently essential, but it would be inefficient not to mark it so.
397 -- Also, A and B had better not be the same operator.
398 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
399 FROM pg_operator AS p1, pg_operator AS p2
400 WHERE p1.oprnegate = p2.oid AND
401 (p1.oprkind != p2.oprkind OR
402 p1.oprleft != p2.oprleft OR
403 p1.oprright != p2.oprright OR
404 p1.oprresult != 'bool'::regtype OR
405 p2.oprresult != 'bool'::regtype OR
406 p1.oid != p2.oprnegate OR
408 oid | oprcode | oid | oprcode
409 -----+---------+-----+---------
412 -- A mergejoinable or hashjoinable operator must be binary, must return
413 -- boolean, and must have a commutator (itself, unless it's a cross-type
415 SELECT p1.oid, p1.oprname FROM pg_operator AS p1
416 WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT
417 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0);
422 -- What's more, the commutator had better be mergejoinable/hashjoinable too.
423 SELECT p1.oid, p1.oprname, p2.oid, p2.oprname
424 FROM pg_operator AS p1, pg_operator AS p2
425 WHERE p1.oprcom = p2.oid AND
426 (p1.oprcanmerge != p2.oprcanmerge OR
427 p1.oprcanhash != p2.oprcanhash);
428 oid | oprname | oid | oprname
429 -----+---------+-----+---------
432 -- Mergejoinable operators should appear as equality members of btree index
434 SELECT p1.oid, p1.oprname
435 FROM pg_operator AS p1
436 WHERE p1.oprcanmerge AND NOT EXISTS
437 (SELECT 1 FROM pg_amop
438 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
439 amopopr = p1.oid AND amopstrategy = 3);
445 SELECT p1.oid, p1.oprname, p.amopfamily
446 FROM pg_operator AS p1, pg_amop p
447 WHERE amopopr = p1.oid
448 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
450 AND NOT p1.oprcanmerge;
451 oid | oprname | amopfamily
452 -----+---------+------------
455 -- Hashable operators should appear as members of hash index opfamilies.
456 SELECT p1.oid, p1.oprname
457 FROM pg_operator AS p1
458 WHERE p1.oprcanhash AND NOT EXISTS
459 (SELECT 1 FROM pg_amop
460 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
461 amopopr = p1.oid AND amopstrategy = 1);
467 SELECT p1.oid, p1.oprname, p.amopfamily
468 FROM pg_operator AS p1, pg_amop p
469 WHERE amopopr = p1.oid
470 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
471 AND NOT p1.oprcanhash;
472 oid | oprname | amopfamily
473 -----+---------+------------
476 -- Check that each operator defined in pg_operator matches its oprcode entry
477 -- in pg_proc. Easiest to do this separately for each oprkind.
478 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
479 FROM pg_operator AS p1, pg_proc AS p2
480 WHERE p1.oprcode = p2.oid AND
483 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
484 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
485 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
486 oid | oprname | oid | proname
487 -----+---------+-----+---------
490 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
491 FROM pg_operator AS p1, pg_proc AS p2
492 WHERE p1.oprcode = p2.oid AND
495 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
496 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
498 oid | oprname | oid | proname
499 -----+---------+-----+---------
502 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
503 FROM pg_operator AS p1, pg_proc AS p2
504 WHERE p1.oprcode = p2.oid AND
507 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
508 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
509 OR p1.oprright != 0);
510 oid | oprname | oid | proname
511 -----+---------+-----+---------
514 -- If the operator is mergejoinable or hashjoinable, its underlying function
515 -- should not be volatile.
516 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
517 FROM pg_operator AS p1, pg_proc AS p2
518 WHERE p1.oprcode = p2.oid AND
519 (p1.oprcanmerge OR p1.oprcanhash) AND
520 p2.provolatile = 'v';
521 oid | oprname | oid | proname
522 -----+---------+-----+---------
525 -- If oprrest is set, the operator must return boolean,
526 -- and it must link to a proc with the right signature
527 -- to be a restriction selectivity estimator.
528 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
529 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
530 FROM pg_operator AS p1, pg_proc AS p2
531 WHERE p1.oprrest = p2.oid AND
532 (p1.oprresult != 'bool'::regtype OR
533 p2.prorettype != 'float8'::regtype OR p2.proretset OR
535 p2.proargtypes[0] != 'internal'::regtype OR
536 p2.proargtypes[1] != 'oid'::regtype OR
537 p2.proargtypes[2] != 'internal'::regtype OR
538 p2.proargtypes[3] != 'int4'::regtype);
539 oid | oprname | oid | proname
540 -----+---------+-----+---------
543 -- If oprjoin is set, the operator must be a binary boolean op,
544 -- and it must link to a proc with the right signature
545 -- to be a join selectivity estimator.
546 -- The proc signature we want is: float8 proc(internal, oid, internal, int2)
547 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
548 FROM pg_operator AS p1, pg_proc AS p2
549 WHERE p1.oprjoin = p2.oid AND
550 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
551 p2.prorettype != 'float8'::regtype OR p2.proretset OR
553 p2.proargtypes[0] != 'internal'::regtype OR
554 p2.proargtypes[1] != 'oid'::regtype OR
555 p2.proargtypes[2] != 'internal'::regtype OR
556 p2.proargtypes[3] != 'int2'::regtype);
557 oid | oprname | oid | proname
558 -----+---------+-----+---------
561 -- **************** pg_aggregate ****************
562 -- Look for illegal values in pg_aggregate fields.
563 SELECT ctid, aggfnoid::oid
564 FROM pg_aggregate as p1
565 WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
570 -- Make sure the matching pg_proc entry is sensible, too.
571 SELECT a.aggfnoid::oid, p.proname
572 FROM pg_aggregate as a, pg_proc as p
573 WHERE a.aggfnoid = p.oid AND
574 (NOT p.proisagg OR p.proretset);
579 -- Make sure there are no proisagg pg_proc entries without matches.
583 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
588 -- If there is no finalfn then the output type must be the transtype.
589 SELECT a.aggfnoid::oid, p.proname
590 FROM pg_aggregate as a, pg_proc as p
591 WHERE a.aggfnoid = p.oid AND
592 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
597 -- Cross-check transfn against its entry in pg_proc.
598 -- NOTE: use physically_coercible here, not binary_coercible, because
599 -- max and min on abstime are implemented using int4larger/int4smaller.
600 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
601 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
602 WHERE a.aggfnoid = p.oid AND
603 a.aggtransfn = ptr.oid AND
605 OR NOT (ptr.pronargs = p.pronargs + 1)
606 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
607 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
608 OR (p.pronargs > 0 AND
609 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
610 OR (p.pronargs > 1 AND
611 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
612 OR (p.pronargs > 2 AND
613 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
614 -- we could carry the check further, but that's enough for now
616 aggfnoid | proname | oid | proname
617 ----------+---------+-----+---------
620 -- Cross-check finalfn (if present) against its entry in pg_proc.
621 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
622 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
623 WHERE a.aggfnoid = p.oid AND
624 a.aggfinalfn = pfn.oid AND
626 OR NOT binary_coercible(pfn.prorettype, p.prorettype)
628 OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
629 aggfnoid | proname | oid | proname
630 ----------+---------+-----+---------
633 -- If transfn is strict then either initval should be non-NULL, or
634 -- input type should match transtype so that the first non-null input
635 -- can be assigned as the state value.
636 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
637 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
638 WHERE a.aggfnoid = p.oid AND
639 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
640 a.agginitval IS NULL AND
641 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
642 aggfnoid | proname | oid | proname
643 ----------+---------+-----+---------
646 -- Cross-check aggsortop (if present) against pg_operator.
647 -- We expect to find only "<" for "min" and ">" for "max".
648 SELECT DISTINCT proname, oprname
649 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
650 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
658 -- Check datatypes match
659 SELECT a.aggfnoid::oid, o.oid
660 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
661 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
662 (oprkind != 'b' OR oprresult != 'boolean'::regtype
663 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
668 -- Check operator is a suitable btree opfamily member
669 SELECT a.aggfnoid::oid, o.oid
670 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
671 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
672 NOT EXISTS(SELECT 1 FROM pg_amop
673 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
675 AND amoplefttype = o.oprleft
676 AND amoprighttype = o.oprright);
681 -- Check correspondence of btree strategies and names
682 SELECT DISTINCT proname, oprname, amopstrategy
683 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
685 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
687 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
689 proname | oprname | amopstrategy
690 ---------+---------+--------------
695 -- **************** pg_opfamily ****************
696 -- Look for illegal values in pg_opfamily fields
698 FROM pg_opfamily as p1
699 WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
704 -- **************** pg_opclass ****************
705 -- Look for illegal values in pg_opclass fields
707 FROM pg_opclass AS p1
708 WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
714 -- opcmethod must match owning opfamily's opfmethod
715 SELECT p1.oid, p2.oid
716 FROM pg_opclass AS p1, pg_opfamily AS p2
717 WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
722 -- There should not be multiple entries in pg_opclass with opcdefault true
723 -- and the same opcmethod/opcintype combination.
724 SELECT p1.oid, p2.oid
725 FROM pg_opclass AS p1, pg_opclass AS p2
726 WHERE p1.oid != p2.oid AND
727 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
728 p1.opcdefault AND p2.opcdefault;
733 -- **************** pg_amop ****************
734 -- Look for illegal values in pg_amop fields
735 SELECT p1.amopfamily, p1.amopstrategy
737 WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
738 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
739 amopfamily | amopstrategy
740 ------------+--------------
743 -- amoplefttype/amoprighttype must match the operator
744 SELECT p1.oid, p2.oid
745 FROM pg_amop AS p1, pg_operator AS p2
746 WHERE p1.amopopr = p2.oid AND NOT
747 (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);
752 -- amopmethod must match owning opfamily's opfmethod
753 SELECT p1.oid, p2.oid
754 FROM pg_amop AS p1, pg_opfamily AS p2
755 WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
760 -- Cross-check amopstrategy index against parent AM
761 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
762 FROM pg_amop AS p1, pg_am AS p2
763 WHERE p1.amopmethod = p2.oid AND
764 p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
765 amopfamily | amopopr | oid | amname
766 ------------+---------+-----+--------
769 -- Detect missing pg_amop entries: should have as many strategy operators
770 -- as AM expects for each datatype combination supported by the opfamily.
771 -- We can't check this for AMs with variable strategy sets.
772 SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
773 FROM pg_am AS p1, pg_amop AS p2
774 WHERE p2.amopmethod = p1.oid AND
775 p1.amstrategies <> 0 AND
776 p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
777 WHERE p3.amopfamily = p2.amopfamily AND
778 p3.amoplefttype = p2.amoplefttype AND
779 p3.amoprighttype = p2.amoprighttype);
780 amname | amoplefttype | amoprighttype
781 --------+--------------+---------------
784 -- Check that amopopr points at a reasonable-looking operator, ie a binary
785 -- operator yielding boolean.
786 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
787 FROM pg_amop AS p1, pg_operator AS p2
788 WHERE p1.amopopr = p2.oid AND
789 (p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
790 amopfamily | amopopr | oid | oprname
791 ------------+---------+-----+---------
794 -- Make a list of all the distinct operator names being used in particular
795 -- strategy slots. This is a bit hokey, since the list might need to change
796 -- in future releases, but it's an effective way of spotting mistakes such as
797 -- swapping two operators within a family.
798 SELECT DISTINCT amopmethod, amopstrategy, oprname
799 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
801 amopmethod | amopstrategy | oprname
802 ------------+--------------+---------
836 -- Check that all operators linked to by opclass entries have selectivity
837 -- estimators. This is not absolutely required, but it seems a reasonable
838 -- thing to insist on for all standard datatypes.
839 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
840 FROM pg_amop AS p1, pg_operator AS p2
841 WHERE p1.amopopr = p2.oid AND
842 (p2.oprrest = 0 OR p2.oprjoin = 0);
843 amopfamily | amopopr | oid | oprname
844 ------------+---------+-----+---------
847 -- Check that each opclass in an opfamily has associated operators, that is
848 -- ones whose oprleft matches opcintype (possibly by coercion).
849 SELECT p1.opcname, p1.opcfamily
850 FROM pg_opclass AS p1
851 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
852 WHERE p2.amopfamily = p1.opcfamily
853 AND binary_coercible(p1.opcintype, p2.amoplefttype));
855 ---------+-----------
858 -- Operators that are primary members of opclasses must be immutable (else
859 -- it suggests that the index ordering isn't fixed). Operators that are
860 -- cross-type members need only be stable, since they are just shorthands
861 -- for index probe queries.
862 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
863 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
864 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
865 p1.amoplefttype = p1.amoprighttype AND
866 p3.provolatile != 'i';
867 amopfamily | amopopr | oprname | prosrc
868 ------------+---------+---------+--------
871 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
872 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
873 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
874 p1.amoplefttype != p1.amoprighttype AND
875 p3.provolatile = 'v';
876 amopfamily | amopopr | oprname | prosrc
877 ------------+---------+---------+--------
880 -- Multiple-datatype btree opfamilies should provide closed sets of equality
881 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
882 -- should also provide int2 = int8 (and commutators of all these). This is
883 -- important because the planner tries to deduce additional qual clauses from
884 -- transitivity of mergejoinable operators. If there are clauses
885 -- int2var = int4var and int4var = int8var, the planner will want to deduce
886 -- int2var = int8var ... so there should be a way to represent that. While
887 -- a missing cross-type operator is now only an efficiency loss rather than
888 -- an error condition, it still seems reasonable to insist that all built-in
889 -- opfamilies be complete.
890 -- check commutative closure
891 SELECT p1.amoplefttype, p1.amoprighttype
893 WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
894 p1.amopstrategy = 3 AND
895 p1.amoplefttype != p1.amoprighttype AND
896 NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
897 p2.amopfamily = p1.amopfamily AND
898 p2.amoplefttype = p1.amoprighttype AND
899 p2.amoprighttype = p1.amoplefttype AND
900 p2.amopstrategy = 3);
901 amoplefttype | amoprighttype
902 --------------+---------------
905 -- check transitive closure
906 SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
907 FROM pg_amop AS p1, pg_amop AS p2
908 WHERE p1.amopfamily = p2.amopfamily AND
909 p1.amoprighttype = p2.amoplefttype AND
910 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
911 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
912 p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
913 p1.amoplefttype != p1.amoprighttype AND
914 p2.amoplefttype != p2.amoprighttype AND
915 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
916 p3.amopfamily = p1.amopfamily AND
917 p3.amoplefttype = p1.amoplefttype AND
918 p3.amoprighttype = p2.amoprighttype AND
919 p3.amopstrategy = 3);
920 amoplefttype | amoprighttype | amoprighttype
921 --------------+---------------+---------------
924 -- We also expect that built-in multiple-datatype hash opfamilies provide
925 -- complete sets of cross-type operators. Again, this isn't required, but
926 -- it is reasonable to expect it for built-in opfamilies.
927 -- if same family has x=x and y=y, it should have x=y
928 SELECT p1.amoplefttype, p2.amoplefttype
929 FROM pg_amop AS p1, pg_amop AS p2
930 WHERE p1.amopfamily = p2.amopfamily AND
931 p1.amoplefttype = p1.amoprighttype AND
932 p2.amoplefttype = p2.amoprighttype AND
933 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
934 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
935 p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
936 p1.amoplefttype != p2.amoplefttype AND
937 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
938 p3.amopfamily = p1.amopfamily AND
939 p3.amoplefttype = p1.amoplefttype AND
940 p3.amoprighttype = p2.amoplefttype AND
941 p3.amopstrategy = 1);
942 amoplefttype | amoplefttype
943 --------------+--------------
946 -- **************** pg_amproc ****************
947 -- Look for illegal values in pg_amproc fields
948 SELECT p1.amprocfamily, p1.amprocnum
950 WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
951 OR p1.amprocnum < 1 OR p1.amproc = 0;
952 amprocfamily | amprocnum
953 --------------+-----------
956 -- Cross-check amprocnum index against parent AM
957 SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
958 FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
959 WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
960 p1.amprocnum > p2.amsupport;
961 amprocfamily | amprocnum | oid | amname
962 --------------+-----------+-----+--------
965 -- Detect missing pg_amproc entries: should have as many support functions
966 -- as AM expects for each datatype combination supported by the opfamily.
967 -- GIN is a special case because it has an optional support function.
968 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
969 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
970 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
971 p1.amname <> 'gin' AND
972 p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
973 WHERE p4.amprocfamily = p2.oid AND
974 p4.amproclefttype = p3.amproclefttype AND
975 p4.amprocrighttype = p3.amprocrighttype);
976 amname | opfname | amproclefttype | amprocrighttype
977 --------+---------+----------------+-----------------
980 -- Similar check for GIN, allowing one optional proc
981 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
982 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
983 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
984 p1.amname = 'gin' AND
985 p1.amsupport - 1 > (SELECT count(*) FROM pg_amproc AS p4
986 WHERE p4.amprocfamily = p2.oid AND
987 p4.amproclefttype = p3.amproclefttype AND
988 p4.amprocrighttype = p3.amprocrighttype);
989 amname | opfname | amproclefttype | amprocrighttype
990 --------+---------+----------------+-----------------
993 -- Also, check if there are any pg_opclass entries that don't seem to have
994 -- pg_amproc support. Again, GIN has to be checked separately.
995 SELECT amname, opcname, count(*)
996 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
997 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
998 amproclefttype = amprocrighttype AND amproclefttype = opcintype
999 WHERE am.amname <> 'gin'
1000 GROUP BY amname, amsupport, opcname, amprocfamily
1001 HAVING count(*) != amsupport OR amprocfamily IS NULL;
1002 amname | opcname | count
1003 --------+---------+-------
1006 SELECT amname, opcname, count(*)
1007 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
1008 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
1009 amproclefttype = amprocrighttype AND amproclefttype = opcintype
1010 WHERE am.amname = 'gin'
1011 GROUP BY amname, amsupport, opcname, amprocfamily
1012 HAVING count(*) < amsupport - 1 OR amprocfamily IS NULL;
1013 amname | opcname | count
1014 --------+---------+-------
1017 -- Unfortunately, we can't check the amproc link very well because the
1018 -- signature of the function may be different for different support routines
1019 -- or different base data types.
1020 -- We can check that all the referenced instances of the same support
1021 -- routine number take the same number of parameters, but that's about it
1022 -- for a general check...
1023 SELECT p1.amprocfamily, p1.amprocnum,
1026 p4.amprocfamily, p4.amprocnum,
1029 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
1030 pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
1031 WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
1032 p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
1033 p1.amproc = p2.oid AND p4.amproc = p5.oid AND
1034 (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
1035 amprocfamily | amprocnum | oid | proname | opfname | amprocfamily | amprocnum | oid | proname | opfname
1036 --------------+-----------+-----+---------+---------+--------------+-----------+-----+---------+---------
1039 -- For btree, though, we can do better since we know the support routines
1040 -- must be of the form cmp(lefttype, righttype) returns int4.
1041 SELECT p1.amprocfamily, p1.amprocnum,
1044 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
1045 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
1046 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
1049 OR prorettype != 'int4'::regtype
1051 OR proargtypes[0] != amproclefttype
1052 OR proargtypes[1] != amprocrighttype);
1053 amprocfamily | amprocnum | oid | proname | opfname
1054 --------------+-----------+-----+---------+---------
1057 -- For hash we can also do a little better: the support routines must be
1058 -- of the form hash(lefttype) returns int4. There are several cases where
1059 -- we cheat and use a hash function that is physically compatible with the
1060 -- datatype even though there's no cast, so this check does find a small
1061 -- number of entries.
1062 SELECT p1.amprocfamily, p1.amprocnum, p2.proname, p3.opfname
1063 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
1064 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
1065 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
1068 OR prorettype != 'int4'::regtype
1070 OR NOT physically_coercible(amproclefttype, proargtypes[0])
1071 OR amproclefttype != amprocrighttype)
1073 amprocfamily | amprocnum | proname | opfname
1074 --------------+-----------+----------------+-----------------
1075 435 | 1 | hashint4 | date_ops
1076 1999 | 1 | timestamp_hash | timestamptz_ops
1077 2222 | 1 | hashchar | bool_ops
1078 2223 | 1 | hashvarlena | bytea_ops
1079 2225 | 1 | hashint4 | xid_ops
1080 2226 | 1 | hashint4 | cid_ops
1083 -- Support routines that are primary members of opfamilies must be immutable
1084 -- (else it suggests that the index ordering isn't fixed). But cross-type
1085 -- members need only be stable, since they are just shorthands
1086 -- for index probe queries.
1087 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
1088 FROM pg_amproc AS p1, pg_proc AS p2
1089 WHERE p1.amproc = p2.oid AND
1090 p1.amproclefttype = p1.amprocrighttype AND
1091 p2.provolatile != 'i';
1092 amprocfamily | amproc | prosrc
1093 --------------+--------+--------
1096 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
1097 FROM pg_amproc AS p1, pg_proc AS p2
1098 WHERE p1.amproc = p2.oid AND
1099 p1.amproclefttype != p1.amprocrighttype AND
1100 p2.provolatile = 'v';
1101 amprocfamily | amproc | prosrc
1102 --------------+--------+--------