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 castmethod = 'b' 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 p1.pronargdefaults < 0 OR
48 p1.pronargdefaults > p1.pronargs OR
49 array_lower(p1.proargtypes, 1) != 0 OR
50 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR
51 0::oid = ANY (p1.proargtypes) OR
53 CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END;
58 -- prosrc should never be null or empty
59 SELECT p1.oid, p1.proname
61 WHERE prosrc IS NULL OR prosrc = '' OR prosrc = '-';
66 -- proiswindow shouldn't be set together with proisagg or proretset
67 SELECT p1.oid, p1.proname
69 WHERE proiswindow AND (proisagg OR proretset);
74 -- pronargdefaults should be 0 iff proargdefaults is null
75 SELECT p1.oid, p1.proname
77 WHERE (pronargdefaults <> 0) != (proargdefaults IS NOT NULL);
82 -- probin should be non-empty for C functions, null everywhere else
83 SELECT p1.oid, p1.proname
85 WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-');
90 SELECT p1.oid, p1.proname
92 WHERE prolang != 13 AND probin IS NOT NULL;
97 -- Look for conflicting proc definitions (same names and input datatypes).
98 -- (This test should be dead code now that we have the unique index
99 -- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.)
100 SELECT p1.oid, p1.proname, p2.oid, p2.proname
101 FROM pg_proc AS p1, pg_proc AS p2
102 WHERE p1.oid != p2.oid AND
103 p1.proname = p2.proname AND
104 p1.pronargs = p2.pronargs AND
105 p1.proargtypes = p2.proargtypes;
106 oid | proname | oid | proname
107 -----+---------+-----+---------
110 -- Considering only built-in procs (prolang = 12), look for multiple uses
111 -- of the same internal function (ie, matching prosrc fields). It's OK to
112 -- have several entries with different pronames for the same internal function,
113 -- but conflicts in the number of arguments and other critical items should
114 -- be complained of. (We don't check data types here; see next query.)
115 -- Note: ignore aggregate functions here, since they all point to the same
116 -- dummy built-in function.
117 SELECT p1.oid, p1.proname, p2.oid, p2.proname
118 FROM pg_proc AS p1, pg_proc AS p2
119 WHERE p1.oid < p2.oid AND
120 p1.prosrc = p2.prosrc AND
121 p1.prolang = 12 AND p2.prolang = 12 AND
122 (p1.proisagg = false OR p2.proisagg = false) AND
123 (p1.prolang != p2.prolang OR
124 p1.proisagg != p2.proisagg OR
125 p1.prosecdef != p2.prosecdef OR
126 p1.proisstrict != p2.proisstrict OR
127 p1.proretset != p2.proretset OR
128 p1.provolatile != p2.provolatile OR
129 p1.pronargs != p2.pronargs);
130 oid | proname | oid | proname
131 -----+---------+-----+---------
134 -- Look for uses of different type OIDs in the argument/result type fields
135 -- for different aliases of the same built-in function.
136 -- This indicates that the types are being presumed to be binary-equivalent,
137 -- or that the built-in function is prepared to deal with different types.
138 -- That's not wrong, necessarily, but we make lists of all the types being
139 -- so treated. Note that the expected output of this part of the test will
140 -- need to be modified whenever new pairs of types are made binary-equivalent,
141 -- or when new polymorphic built-in functions are added!
142 -- Note: ignore aggregate functions here, since they all point to the same
143 -- dummy built-in function. Likewise, ignore range constructor functions.
144 SELECT DISTINCT p1.prorettype, p2.prorettype
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.prosrc NOT LIKE E'range\\_constructor_' AND
151 p2.prosrc NOT LIKE E'range\\_constructor_' AND
152 (p1.prorettype < p2.prorettype)
154 prorettype | prorettype
155 ------------+------------
160 SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
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.prosrc NOT LIKE E'range\\_constructor_' AND
167 p2.prosrc NOT LIKE E'range\\_constructor_' AND
168 (p1.proargtypes[0] < p2.proargtypes[0])
170 proargtypes | proargtypes
171 -------------+-------------
179 SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
180 FROM pg_proc AS p1, pg_proc AS p2
181 WHERE p1.oid != p2.oid AND
182 p1.prosrc = p2.prosrc AND
183 p1.prolang = 12 AND p2.prolang = 12 AND
184 NOT p1.proisagg AND NOT p2.proisagg AND
185 p1.prosrc NOT LIKE E'range\\_constructor_' AND
186 p2.prosrc NOT LIKE E'range\\_constructor_' AND
187 (p1.proargtypes[1] < p2.proargtypes[1])
189 proargtypes | proargtypes
190 -------------+-------------
197 SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
198 FROM pg_proc AS p1, pg_proc AS p2
199 WHERE p1.oid != p2.oid AND
200 p1.prosrc = p2.prosrc AND
201 p1.prolang = 12 AND p2.prolang = 12 AND
202 NOT p1.proisagg AND NOT p2.proisagg AND
203 (p1.proargtypes[2] < p2.proargtypes[2])
205 proargtypes | proargtypes
206 -------------+-------------
210 SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
211 FROM pg_proc AS p1, pg_proc AS p2
212 WHERE p1.oid != p2.oid AND
213 p1.prosrc = p2.prosrc AND
214 p1.prolang = 12 AND p2.prolang = 12 AND
215 NOT p1.proisagg AND NOT p2.proisagg AND
216 (p1.proargtypes[3] < p2.proargtypes[3])
218 proargtypes | proargtypes
219 -------------+-------------
223 SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
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[4] < p2.proargtypes[4])
231 proargtypes | proargtypes
232 -------------+-------------
235 SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
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[5] < p2.proargtypes[5])
243 proargtypes | proargtypes
244 -------------+-------------
247 SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
248 FROM pg_proc AS p1, pg_proc AS p2
249 WHERE p1.oid != p2.oid AND
250 p1.prosrc = p2.prosrc AND
251 p1.prolang = 12 AND p2.prolang = 12 AND
252 NOT p1.proisagg AND NOT p2.proisagg AND
253 (p1.proargtypes[6] < p2.proargtypes[6])
255 proargtypes | proargtypes
256 -------------+-------------
259 SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
260 FROM pg_proc AS p1, pg_proc AS p2
261 WHERE p1.oid != p2.oid AND
262 p1.prosrc = p2.prosrc AND
263 p1.prolang = 12 AND p2.prolang = 12 AND
264 NOT p1.proisagg AND NOT p2.proisagg AND
265 (p1.proargtypes[7] < p2.proargtypes[7])
267 proargtypes | proargtypes
268 -------------+-------------
271 -- Look for functions that return type "internal" and do not have any
272 -- "internal" argument. Such a function would be a security hole since
273 -- it might be used to call an internal function from an SQL command.
274 -- As of 7.3 this query should find only internal_in.
275 SELECT p1.oid, p1.proname
277 WHERE p1.prorettype = 'internal'::regtype AND NOT
278 'internal'::regtype = ANY (p1.proargtypes);
284 -- Check for length inconsistencies between the various argument-info arrays.
285 SELECT p1.oid, p1.proname
287 WHERE proallargtypes IS NOT NULL AND
288 array_length(proallargtypes,1) < array_length(proargtypes,1);
293 SELECT p1.oid, p1.proname
295 WHERE proargmodes IS NOT NULL AND
296 array_length(proargmodes,1) < array_length(proargtypes,1);
301 SELECT p1.oid, p1.proname
303 WHERE proargnames IS NOT NULL AND
304 array_length(proargnames,1) < array_length(proargtypes,1);
309 SELECT p1.oid, p1.proname
311 WHERE proallargtypes IS NOT NULL AND proargmodes IS NOT NULL AND
312 array_length(proallargtypes,1) <> array_length(proargmodes,1);
317 SELECT p1.oid, p1.proname
319 WHERE proallargtypes IS NOT NULL AND proargnames IS NOT NULL AND
320 array_length(proallargtypes,1) <> array_length(proargnames,1);
325 SELECT p1.oid, p1.proname
327 WHERE proargmodes IS NOT NULL AND proargnames IS NOT NULL AND
328 array_length(proargmodes,1) <> array_length(proargnames,1);
333 -- Insist that all built-in pg_proc entries have descriptions
334 SELECT p1.oid, p1.proname
335 FROM pg_proc as p1 LEFT JOIN pg_description as d
336 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0
337 WHERE d.classoid IS NULL AND p1.oid <= 9999;
342 -- **************** pg_cast ****************
343 -- Catch bogus values in pg_cast columns (other than cases detected by
347 WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i')
348 OR castmethod NOT IN ('f', 'b' ,'i');
349 castsource | casttarget | castfunc | castcontext | castmethod
350 ------------+------------+----------+-------------+------------
353 -- Check that castfunc is nonzero only for cast methods that need a function,
354 -- and zero otherwise
357 WHERE (castmethod = 'f' AND castfunc = 0)
358 OR (castmethod IN ('b', 'i') AND castfunc <> 0);
359 castsource | casttarget | castfunc | castcontext | castmethod
360 ------------+------------+----------+-------------+------------
363 -- Look for casts to/from the same type that aren't length coercion functions.
364 -- (We assume they are length coercions if they take multiple arguments.)
365 -- Such entries are not necessarily harmful, but they are useless.
368 WHERE castsource = casttarget AND castfunc = 0;
369 castsource | casttarget | castfunc | castcontext | castmethod
370 ------------+------------+----------+-------------+------------
374 FROM pg_cast c, pg_proc p
375 WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
376 castsource | casttarget | castfunc | castcontext | castmethod
377 ------------+------------+----------+-------------+------------
380 -- Look for cast functions that don't have the right signature. The
381 -- argument and result types in pg_proc must be the same as, or binary
382 -- compatible with, what it says in pg_cast.
383 -- As a special case, we allow casts from CHAR(n) that use functions
384 -- declared to take TEXT. This does not pass the binary-coercibility test
385 -- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
386 -- are the same, so long as the function is one that ignores trailing blanks.
388 FROM pg_cast c, pg_proc p
389 WHERE c.castfunc = p.oid AND
390 (p.pronargs < 1 OR p.pronargs > 3
391 OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
392 OR (c.castsource = 'character'::regtype AND
393 p.proargtypes[0] = 'text'::regtype))
394 OR NOT binary_coercible(p.prorettype, c.casttarget));
395 castsource | casttarget | castfunc | castcontext | castmethod
396 ------------+------------+----------+-------------+------------
400 FROM pg_cast c, pg_proc p
401 WHERE c.castfunc = p.oid AND
402 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
403 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
404 castsource | casttarget | castfunc | castcontext | castmethod
405 ------------+------------+----------+-------------+------------
408 -- Look for binary compatible casts that do not have the reverse
409 -- direction registered as well, or where the reverse direction is not
410 -- also binary compatible. This is legal, but usually not intended.
411 -- As of 7.4, this finds the casts from text and varchar to bpchar, because
412 -- those are binary-compatible while the reverse way goes through rtrim().
413 -- As of 8.2, this finds the cast from cidr to inet, because that is a
414 -- trivial binary coercion while the other way goes through inet_to_cidr().
415 -- As of 8.3, this finds the casts from xml to text, varchar, and bpchar,
416 -- because those are binary-compatible while the reverse goes through
417 -- texttoxml(), which does an XML syntax check.
418 -- As of 9.1, this finds the cast from pg_node_tree to text, which we
419 -- intentionally do not provide a reverse pathway for.
420 SELECT castsource::regtype, casttarget::regtype, castfunc, castcontext
422 WHERE c.castmethod = 'b' AND
423 NOT EXISTS (SELECT 1 FROM pg_cast k
424 WHERE k.castmethod = 'b' AND
425 k.castsource = c.casttarget AND
426 k.casttarget = c.castsource);
427 castsource | casttarget | castfunc | castcontext
428 -------------------+-------------------+----------+-------------
429 text | character | 0 | i
430 character varying | character | 0 | i
431 pg_node_tree | text | 0 | i
434 xml | character varying | 0 | a
435 xml | character | 0 | a
438 -- **************** pg_operator ****************
439 -- Look for illegal values in pg_operator fields.
440 SELECT p1.oid, p1.oprname
441 FROM pg_operator as p1
442 WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
443 p1.oprresult = 0 OR p1.oprcode = 0;
448 -- Look for missing or unwanted operand types
449 SELECT p1.oid, p1.oprname
450 FROM pg_operator as p1
451 WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
452 (p1.oprleft != 0 and p1.oprkind = 'l') OR
453 (p1.oprright = 0 and p1.oprkind != 'r') OR
454 (p1.oprright != 0 and p1.oprkind = 'r');
459 -- Look for conflicting operator definitions (same names and input datatypes).
460 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
461 FROM pg_operator AS p1, pg_operator AS p2
462 WHERE p1.oid != p2.oid AND
463 p1.oprname = p2.oprname AND
464 p1.oprkind = p2.oprkind AND
465 p1.oprleft = p2.oprleft AND
466 p1.oprright = p2.oprright;
467 oid | oprcode | oid | oprcode
468 -----+---------+-----+---------
471 -- Look for commutative operators that don't commute.
472 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
473 -- We expect that B will always say that B.oprcom = A as well; that's not
474 -- inherently essential, but it would be inefficient not to mark it so.
475 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
476 FROM pg_operator AS p1, pg_operator AS p2
477 WHERE p1.oprcom = p2.oid AND
478 (p1.oprkind != 'b' OR
479 p1.oprleft != p2.oprright OR
480 p1.oprright != p2.oprleft OR
481 p1.oprresult != p2.oprresult OR
482 p1.oid != p2.oprcom);
483 oid | oprcode | oid | oprcode
484 -----+---------+-----+---------
487 -- Look for negatory operators that don't agree.
488 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
489 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
490 -- single-operand operators.
491 -- We expect that B will always say that B.oprnegate = A as well; that's not
492 -- inherently essential, but it would be inefficient not to mark it so.
493 -- Also, A and B had better not be the same operator.
494 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
495 FROM pg_operator AS p1, pg_operator AS p2
496 WHERE p1.oprnegate = p2.oid AND
497 (p1.oprkind != p2.oprkind OR
498 p1.oprleft != p2.oprleft OR
499 p1.oprright != p2.oprright OR
500 p1.oprresult != 'bool'::regtype OR
501 p2.oprresult != 'bool'::regtype OR
502 p1.oid != p2.oprnegate OR
504 oid | oprcode | oid | oprcode
505 -----+---------+-----+---------
508 -- A mergejoinable or hashjoinable operator must be binary, must return
509 -- boolean, and must have a commutator (itself, unless it's a cross-type
511 SELECT p1.oid, p1.oprname FROM pg_operator AS p1
512 WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT
513 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0);
518 -- What's more, the commutator had better be mergejoinable/hashjoinable too.
519 SELECT p1.oid, p1.oprname, p2.oid, p2.oprname
520 FROM pg_operator AS p1, pg_operator AS p2
521 WHERE p1.oprcom = p2.oid AND
522 (p1.oprcanmerge != p2.oprcanmerge OR
523 p1.oprcanhash != p2.oprcanhash);
524 oid | oprname | oid | oprname
525 -----+---------+-----+---------
528 -- Mergejoinable operators should appear as equality members of btree index
530 SELECT p1.oid, p1.oprname
531 FROM pg_operator AS p1
532 WHERE p1.oprcanmerge AND NOT EXISTS
533 (SELECT 1 FROM pg_amop
534 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
535 amopopr = p1.oid AND amopstrategy = 3);
541 SELECT p1.oid, p1.oprname, p.amopfamily
542 FROM pg_operator AS p1, pg_amop p
543 WHERE amopopr = p1.oid
544 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
546 AND NOT p1.oprcanmerge;
547 oid | oprname | amopfamily
548 -----+---------+------------
551 -- Hashable operators should appear as members of hash index opfamilies.
552 SELECT p1.oid, p1.oprname
553 FROM pg_operator AS p1
554 WHERE p1.oprcanhash AND NOT EXISTS
555 (SELECT 1 FROM pg_amop
556 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
557 amopopr = p1.oid AND amopstrategy = 1);
563 SELECT p1.oid, p1.oprname, p.amopfamily
564 FROM pg_operator AS p1, pg_amop p
565 WHERE amopopr = p1.oid
566 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
567 AND NOT p1.oprcanhash;
568 oid | oprname | amopfamily
569 -----+---------+------------
572 -- Check that each operator defined in pg_operator matches its oprcode entry
573 -- in pg_proc. Easiest to do this separately for each oprkind.
574 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
575 FROM pg_operator AS p1, pg_proc AS p2
576 WHERE p1.oprcode = p2.oid AND
579 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
580 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
581 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
582 oid | oprname | oid | proname
583 -----+---------+-----+---------
586 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
587 FROM pg_operator AS p1, pg_proc AS p2
588 WHERE p1.oprcode = p2.oid AND
591 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
592 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
594 oid | oprname | oid | proname
595 -----+---------+-----+---------
598 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
599 FROM pg_operator AS p1, pg_proc AS p2
600 WHERE p1.oprcode = p2.oid AND
603 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
604 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
605 OR p1.oprright != 0);
606 oid | oprname | oid | proname
607 -----+---------+-----+---------
610 -- If the operator is mergejoinable or hashjoinable, its underlying function
611 -- should not be volatile.
612 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
613 FROM pg_operator AS p1, pg_proc AS p2
614 WHERE p1.oprcode = p2.oid AND
615 (p1.oprcanmerge OR p1.oprcanhash) AND
616 p2.provolatile = 'v';
617 oid | oprname | oid | proname
618 -----+---------+-----+---------
621 -- If oprrest is set, the operator must return boolean,
622 -- and it must link to a proc with the right signature
623 -- to be a restriction selectivity estimator.
624 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
625 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
626 FROM pg_operator AS p1, pg_proc AS p2
627 WHERE p1.oprrest = p2.oid AND
628 (p1.oprresult != 'bool'::regtype OR
629 p2.prorettype != 'float8'::regtype OR p2.proretset OR
631 p2.proargtypes[0] != 'internal'::regtype OR
632 p2.proargtypes[1] != 'oid'::regtype OR
633 p2.proargtypes[2] != 'internal'::regtype OR
634 p2.proargtypes[3] != 'int4'::regtype);
635 oid | oprname | oid | proname
636 -----+---------+-----+---------
639 -- If oprjoin is set, the operator must be a binary boolean op,
640 -- and it must link to a proc with the right signature
641 -- to be a join selectivity estimator.
642 -- The proc signature we want is: float8 proc(internal, oid, internal, int2, internal)
643 -- (Note: the old signature with only 4 args is still allowed, but no core
644 -- estimator should be using it.)
645 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
646 FROM pg_operator AS p1, pg_proc AS p2
647 WHERE p1.oprjoin = p2.oid AND
648 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
649 p2.prorettype != 'float8'::regtype OR p2.proretset OR
651 p2.proargtypes[0] != 'internal'::regtype OR
652 p2.proargtypes[1] != 'oid'::regtype OR
653 p2.proargtypes[2] != 'internal'::regtype OR
654 p2.proargtypes[3] != 'int2'::regtype OR
655 p2.proargtypes[4] != 'internal'::regtype);
656 oid | oprname | oid | proname
657 -----+---------+-----+---------
660 -- Insist that all built-in pg_operator entries have descriptions
661 SELECT p1.oid, p1.oprname
662 FROM pg_operator as p1 LEFT JOIN pg_description as d
663 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0
664 WHERE d.classoid IS NULL AND p1.oid <= 9999;
669 -- Check that operators' underlying functions have suitable comments,
670 -- namely 'implementation of XXX operator'. In some cases involving legacy
671 -- names for operators, there are multiple operators referencing the same
672 -- pg_proc entry, so ignore operators whose comments say they are deprecated.
673 -- We also have a few functions that are both operator support and meant to
674 -- be called directly; those should have comments matching their operator.
676 SELECT p.oid as p_oid, proname, o.oid as o_oid,
677 obj_description(p.oid, 'pg_proc') as prodesc,
678 'implementation of ' || oprname || ' operator' as expecteddesc,
679 obj_description(o.oid, 'pg_operator') as oprdesc
680 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid
683 SELECT * FROM funcdescs
684 WHERE prodesc IS DISTINCT FROM expecteddesc
685 AND oprdesc NOT LIKE 'deprecated%'
686 AND prodesc IS DISTINCT FROM oprdesc;
687 p_oid | proname | o_oid | prodesc | expecteddesc | oprdesc
688 -------+---------+-------+---------+--------------+---------
691 -- **************** pg_aggregate ****************
692 -- Look for illegal values in pg_aggregate fields.
693 SELECT ctid, aggfnoid::oid
694 FROM pg_aggregate as p1
695 WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
700 -- Make sure the matching pg_proc entry is sensible, too.
701 SELECT a.aggfnoid::oid, p.proname
702 FROM pg_aggregate as a, pg_proc as p
703 WHERE a.aggfnoid = p.oid AND
704 (NOT p.proisagg OR p.proretset);
709 -- Make sure there are no proisagg pg_proc entries without matches.
713 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
718 -- If there is no finalfn then the output type must be the transtype.
719 SELECT a.aggfnoid::oid, p.proname
720 FROM pg_aggregate as a, pg_proc as p
721 WHERE a.aggfnoid = p.oid AND
722 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
727 -- Cross-check transfn against its entry in pg_proc.
728 -- NOTE: use physically_coercible here, not binary_coercible, because
729 -- max and min on abstime are implemented using int4larger/int4smaller.
730 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
731 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
732 WHERE a.aggfnoid = p.oid AND
733 a.aggtransfn = ptr.oid AND
735 OR NOT (ptr.pronargs = p.pronargs + 1)
736 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
737 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
738 OR (p.pronargs > 0 AND
739 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
740 OR (p.pronargs > 1 AND
741 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
742 OR (p.pronargs > 2 AND
743 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
744 -- we could carry the check further, but that's enough for now
746 aggfnoid | proname | oid | proname
747 ----------+---------+-----+---------
750 -- Cross-check finalfn (if present) against its entry in pg_proc.
751 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
752 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
753 WHERE a.aggfnoid = p.oid AND
754 a.aggfinalfn = pfn.oid AND
756 OR NOT binary_coercible(pfn.prorettype, p.prorettype)
758 OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
759 aggfnoid | proname | oid | proname
760 ----------+---------+-----+---------
763 -- If transfn is strict then either initval should be non-NULL, or
764 -- input type should match transtype so that the first non-null input
765 -- can be assigned as the state value.
766 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
767 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
768 WHERE a.aggfnoid = p.oid AND
769 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
770 a.agginitval IS NULL AND
771 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
772 aggfnoid | proname | oid | proname
773 ----------+---------+-----+---------
776 -- Cross-check aggsortop (if present) against pg_operator.
777 -- We expect to find only "<" for "min" and ">" for "max".
778 SELECT DISTINCT proname, oprname
779 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
780 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
788 -- Check datatypes match
789 SELECT a.aggfnoid::oid, o.oid
790 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
791 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
792 (oprkind != 'b' OR oprresult != 'boolean'::regtype
793 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
798 -- Check operator is a suitable btree opfamily member
799 SELECT a.aggfnoid::oid, o.oid
800 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
801 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
802 NOT EXISTS(SELECT 1 FROM pg_amop
803 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
805 AND amoplefttype = o.oprleft
806 AND amoprighttype = o.oprright);
811 -- Check correspondence of btree strategies and names
812 SELECT DISTINCT proname, oprname, amopstrategy
813 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
815 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
817 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
819 proname | oprname | amopstrategy
820 ---------+---------+--------------
825 -- Check that there are not aggregates with the same name and different
826 -- numbers of arguments. While not technically wrong, we have a project policy
827 -- to avoid this because it opens the door for confusion in connection with
828 -- ORDER BY: novices frequently put the ORDER BY in the wrong place.
829 -- See the fate of the single-argument form of string_agg() for history.
830 -- The only aggregates that should show up here are count(x) and count(*).
831 SELECT p1.oid::regprocedure, p2.oid::regprocedure
832 FROM pg_proc AS p1, pg_proc AS p2
833 WHERE p1.oid < p2.oid AND p1.proname = p2.proname AND
834 p1.proisagg AND p2.proisagg AND
835 array_dims(p1.proargtypes) != array_dims(p2.proargtypes)
838 --------------+---------
839 count("any") | count()
842 -- For the same reason, aggregates with default arguments are no good.
845 WHERE proisagg AND proargdefaults IS NOT NULL;
850 -- **************** pg_opfamily ****************
851 -- Look for illegal values in pg_opfamily fields
853 FROM pg_opfamily as p1
854 WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
859 -- **************** pg_opclass ****************
860 -- Look for illegal values in pg_opclass fields
862 FROM pg_opclass AS p1
863 WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
869 -- opcmethod must match owning opfamily's opfmethod
870 SELECT p1.oid, p2.oid
871 FROM pg_opclass AS p1, pg_opfamily AS p2
872 WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
877 -- There should not be multiple entries in pg_opclass with opcdefault true
878 -- and the same opcmethod/opcintype combination.
879 SELECT p1.oid, p2.oid
880 FROM pg_opclass AS p1, pg_opclass AS p2
881 WHERE p1.oid != p2.oid AND
882 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
883 p1.opcdefault AND p2.opcdefault;
888 -- **************** pg_amop ****************
889 -- Look for illegal values in pg_amop fields
890 SELECT p1.amopfamily, p1.amopstrategy
892 WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
893 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
894 amopfamily | amopstrategy
895 ------------+--------------
898 SELECT p1.amopfamily, p1.amopstrategy
900 WHERE NOT ((p1.amoppurpose = 's' AND p1.amopsortfamily = 0) OR
901 (p1.amoppurpose = 'o' AND p1.amopsortfamily <> 0));
902 amopfamily | amopstrategy
903 ------------+--------------
906 -- amoplefttype/amoprighttype must match the operator
907 SELECT p1.oid, p2.oid
908 FROM pg_amop AS p1, pg_operator AS p2
909 WHERE p1.amopopr = p2.oid AND NOT
910 (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);
915 -- amopmethod must match owning opfamily's opfmethod
916 SELECT p1.oid, p2.oid
917 FROM pg_amop AS p1, pg_opfamily AS p2
918 WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
923 -- amopsortfamily, if present, must reference a btree family
924 SELECT p1.amopfamily, p1.amopstrategy
926 WHERE p1.amopsortfamily <> 0 AND NOT EXISTS
927 (SELECT 1 from pg_opfamily op WHERE op.oid = p1.amopsortfamily
928 AND op.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree'));
929 amopfamily | amopstrategy
930 ------------+--------------
933 -- check for ordering operators not supported by parent AM
934 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
935 FROM pg_amop AS p1, pg_am AS p2
936 WHERE p1.amopmethod = p2.oid AND
937 p1.amoppurpose = 'o' AND NOT p2.amcanorderbyop;
938 amopfamily | amopopr | oid | amname
939 ------------+---------+-----+--------
942 -- Cross-check amopstrategy index against parent AM
943 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
944 FROM pg_amop AS p1, pg_am AS p2
945 WHERE p1.amopmethod = p2.oid AND
946 p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
947 amopfamily | amopopr | oid | amname
948 ------------+---------+-----+--------
951 -- Detect missing pg_amop entries: should have as many strategy operators
952 -- as AM expects for each datatype combination supported by the opfamily.
953 -- We can't check this for AMs with variable strategy sets.
954 SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
955 FROM pg_am AS p1, pg_amop AS p2
956 WHERE p2.amopmethod = p1.oid AND
957 p1.amstrategies <> 0 AND
958 p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
959 WHERE p3.amopfamily = p2.amopfamily AND
960 p3.amoplefttype = p2.amoplefttype AND
961 p3.amoprighttype = p2.amoprighttype AND
962 p3.amoppurpose = 's');
963 amname | amoplefttype | amoprighttype
964 --------+--------------+---------------
967 -- Currently, none of the AMs with fixed strategy sets support ordering ops.
968 SELECT p1.amname, p2.amopfamily, p2.amopstrategy
969 FROM pg_am AS p1, pg_amop AS p2
970 WHERE p2.amopmethod = p1.oid AND
971 p1.amstrategies <> 0 AND p2.amoppurpose <> 's';
972 amname | amopfamily | amopstrategy
973 --------+------------+--------------
976 -- Check that amopopr points at a reasonable-looking operator, ie a binary
977 -- operator. If it's a search operator it had better yield boolean,
978 -- otherwise an input type of its sort opfamily.
979 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
980 FROM pg_amop AS p1, pg_operator AS p2
981 WHERE p1.amopopr = p2.oid AND
983 amopfamily | amopopr | oid | oprname
984 ------------+---------+-----+---------
987 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
988 FROM pg_amop AS p1, pg_operator AS p2
989 WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND
990 p2.oprresult != 'bool'::regtype;
991 amopfamily | amopopr | oid | oprname
992 ------------+---------+-----+---------
995 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
996 FROM pg_amop AS p1, pg_operator AS p2
997 WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 'o' AND NOT EXISTS
998 (SELECT 1 FROM pg_opclass op
999 WHERE opcfamily = p1.amopsortfamily AND opcintype = p2.oprresult);
1000 amopfamily | amopopr | oid | oprname
1001 ------------+---------+-----+---------
1004 -- Make a list of all the distinct operator names being used in particular
1005 -- strategy slots. This is a bit hokey, since the list might need to change
1006 -- in future releases, but it's an effective way of spotting mistakes such as
1007 -- swapping two operators within a family.
1008 SELECT DISTINCT amopmethod, amopstrategy, oprname
1009 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
1011 amopmethod | amopstrategy | oprname
1012 ------------+--------------+---------
1058 -- Check that all opclass search operators have selectivity estimators.
1059 -- This is not absolutely required, but it seems a reasonable thing
1060 -- to insist on for all standard datatypes.
1061 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
1062 FROM pg_amop AS p1, pg_operator AS p2
1063 WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND
1064 (p2.oprrest = 0 OR p2.oprjoin = 0);
1065 amopfamily | amopopr | oid | oprname
1066 ------------+---------+-----+---------
1069 -- Check that each opclass in an opfamily has associated operators, that is
1070 -- ones whose oprleft matches opcintype (possibly by coercion).
1071 SELECT p1.opcname, p1.opcfamily
1072 FROM pg_opclass AS p1
1073 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
1074 WHERE p2.amopfamily = p1.opcfamily
1075 AND binary_coercible(p1.opcintype, p2.amoplefttype));
1077 ---------+-----------
1080 -- Operators that are primary members of opclasses must be immutable (else
1081 -- it suggests that the index ordering isn't fixed). Operators that are
1082 -- cross-type members need only be stable, since they are just shorthands
1083 -- for index probe queries.
1084 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
1085 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
1086 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
1087 p1.amoplefttype = p1.amoprighttype AND
1088 p3.provolatile != 'i';
1089 amopfamily | amopopr | oprname | prosrc
1090 ------------+---------+---------+--------
1093 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
1094 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
1095 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
1096 p1.amoplefttype != p1.amoprighttype AND
1097 p3.provolatile = 'v';
1098 amopfamily | amopopr | oprname | prosrc
1099 ------------+---------+---------+--------
1102 -- Multiple-datatype btree opfamilies should provide closed sets of equality
1103 -- operators; that is if you provide int2 = int4 and int4 = int8 then you
1104 -- should also provide int2 = int8 (and commutators of all these). This is
1105 -- important because the planner tries to deduce additional qual clauses from
1106 -- transitivity of mergejoinable operators. If there are clauses
1107 -- int2var = int4var and int4var = int8var, the planner will want to deduce
1108 -- int2var = int8var ... so there should be a way to represent that. While
1109 -- a missing cross-type operator is now only an efficiency loss rather than
1110 -- an error condition, it still seems reasonable to insist that all built-in
1111 -- opfamilies be complete.
1112 -- check commutative closure
1113 SELECT p1.amoplefttype, p1.amoprighttype
1115 WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
1116 p1.amopstrategy = 3 AND
1117 p1.amoplefttype != p1.amoprighttype AND
1118 NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
1119 p2.amopfamily = p1.amopfamily AND
1120 p2.amoplefttype = p1.amoprighttype AND
1121 p2.amoprighttype = p1.amoplefttype AND
1122 p2.amopstrategy = 3);
1123 amoplefttype | amoprighttype
1124 --------------+---------------
1127 -- check transitive closure
1128 SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
1129 FROM pg_amop AS p1, pg_amop AS p2
1130 WHERE p1.amopfamily = p2.amopfamily AND
1131 p1.amoprighttype = p2.amoplefttype AND
1132 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
1133 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
1134 p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
1135 p1.amoplefttype != p1.amoprighttype AND
1136 p2.amoplefttype != p2.amoprighttype AND
1137 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
1138 p3.amopfamily = p1.amopfamily AND
1139 p3.amoplefttype = p1.amoplefttype AND
1140 p3.amoprighttype = p2.amoprighttype AND
1141 p3.amopstrategy = 3);
1142 amoplefttype | amoprighttype | amoprighttype
1143 --------------+---------------+---------------
1146 -- We also expect that built-in multiple-datatype hash opfamilies provide
1147 -- complete sets of cross-type operators. Again, this isn't required, but
1148 -- it is reasonable to expect it for built-in opfamilies.
1149 -- if same family has x=x and y=y, it should have x=y
1150 SELECT p1.amoplefttype, p2.amoplefttype
1151 FROM pg_amop AS p1, pg_amop AS p2
1152 WHERE p1.amopfamily = p2.amopfamily AND
1153 p1.amoplefttype = p1.amoprighttype AND
1154 p2.amoplefttype = p2.amoprighttype AND
1155 p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
1156 p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
1157 p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
1158 p1.amoplefttype != p2.amoplefttype AND
1159 NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
1160 p3.amopfamily = p1.amopfamily AND
1161 p3.amoplefttype = p1.amoplefttype AND
1162 p3.amoprighttype = p2.amoplefttype AND
1163 p3.amopstrategy = 1);
1164 amoplefttype | amoplefttype
1165 --------------+--------------
1168 -- **************** pg_amproc ****************
1169 -- Look for illegal values in pg_amproc fields
1170 SELECT p1.amprocfamily, p1.amprocnum
1171 FROM pg_amproc as p1
1172 WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
1173 OR p1.amprocnum < 1 OR p1.amproc = 0;
1174 amprocfamily | amprocnum
1175 --------------+-----------
1178 -- Cross-check amprocnum index against parent AM
1179 SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
1180 FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
1181 WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
1182 p1.amprocnum > p2.amsupport;
1183 amprocfamily | amprocnum | oid | amname
1184 --------------+-----------+-----+--------
1187 -- Detect missing pg_amproc entries: should have as many support functions
1188 -- as AM expects for each datatype combination supported by the opfamily.
1189 -- GiST/GIN are special cases because each has an optional support function.
1190 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
1191 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
1192 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
1193 p1.amname <> 'gist' AND p1.amname <> 'gin' AND
1194 p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
1195 WHERE p4.amprocfamily = p2.oid AND
1196 p4.amproclefttype = p3.amproclefttype AND
1197 p4.amprocrighttype = p3.amprocrighttype);
1198 amname | opfname | amproclefttype | amprocrighttype
1199 --------+---------+----------------+-----------------
1202 -- Similar check for GiST/GIN, allowing one optional proc
1203 SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
1204 FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
1205 WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
1206 (p1.amname = 'gist' OR p1.amname = 'gin') AND
1207 (SELECT count(*) FROM pg_amproc AS p4
1208 WHERE p4.amprocfamily = p2.oid AND
1209 p4.amproclefttype = p3.amproclefttype AND
1210 p4.amprocrighttype = p3.amprocrighttype)
1211 NOT IN (p1.amsupport, p1.amsupport - 1);
1212 amname | opfname | amproclefttype | amprocrighttype
1213 --------+---------+----------------+-----------------
1216 -- Also, check if there are any pg_opclass entries that don't seem to have
1217 -- pg_amproc support. Again, GiST/GIN have to be checked specially.
1218 SELECT amname, opcname, count(*)
1219 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
1220 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
1221 amproclefttype = amprocrighttype AND amproclefttype = opcintype
1222 WHERE am.amname <> 'gist' AND am.amname <> 'gin'
1223 GROUP BY amname, amsupport, opcname, amprocfamily
1224 HAVING count(*) != amsupport OR amprocfamily IS NULL;
1225 amname | opcname | count
1226 --------+---------+-------
1229 SELECT amname, opcname, count(*)
1230 FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
1231 LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
1232 amproclefttype = amprocrighttype AND amproclefttype = opcintype
1233 WHERE am.amname = 'gist' OR am.amname = 'gin'
1234 GROUP BY amname, amsupport, opcname, amprocfamily
1235 HAVING (count(*) != amsupport AND count(*) != amsupport - 1)
1236 OR amprocfamily IS NULL;
1237 amname | opcname | count
1238 --------+---------+-------
1241 -- Unfortunately, we can't check the amproc link very well because the
1242 -- signature of the function may be different for different support routines
1243 -- or different base data types.
1244 -- We can check that all the referenced instances of the same support
1245 -- routine number take the same number of parameters, but that's about it
1246 -- for a general check...
1247 SELECT p1.amprocfamily, p1.amprocnum,
1250 p4.amprocfamily, p4.amprocnum,
1253 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
1254 pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
1255 WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
1256 p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
1257 p1.amproc = p2.oid AND p4.amproc = p5.oid AND
1258 (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
1259 amprocfamily | amprocnum | oid | proname | opfname | amprocfamily | amprocnum | oid | proname | opfname
1260 --------------+-----------+-----+---------+---------+--------------+-----------+-----+---------+---------
1263 -- For btree, though, we can do better since we know the support routines
1264 -- must be of the form cmp(lefttype, righttype) returns int4.
1265 SELECT p1.amprocfamily, p1.amprocnum,
1268 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
1269 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
1270 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
1273 OR prorettype != 'int4'::regtype
1275 OR proargtypes[0] != amproclefttype
1276 OR proargtypes[1] != amprocrighttype);
1277 amprocfamily | amprocnum | oid | proname | opfname
1278 --------------+-----------+-----+---------+---------
1281 -- For hash we can also do a little better: the support routines must be
1282 -- of the form hash(lefttype) returns int4. There are several cases where
1283 -- we cheat and use a hash function that is physically compatible with the
1284 -- datatype even though there's no cast, so this check does find a small
1285 -- number of entries.
1286 SELECT p1.amprocfamily, p1.amprocnum, p2.proname, p3.opfname
1287 FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
1288 WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
1289 AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
1292 OR prorettype != 'int4'::regtype
1294 OR NOT physically_coercible(amproclefttype, proargtypes[0])
1295 OR amproclefttype != amprocrighttype)
1297 amprocfamily | amprocnum | proname | opfname
1298 --------------+-----------+----------------+-----------------
1299 435 | 1 | hashint4 | date_ops
1300 1999 | 1 | timestamp_hash | timestamptz_ops
1301 2222 | 1 | hashchar | bool_ops
1302 2223 | 1 | hashvarlena | bytea_ops
1303 2225 | 1 | hashint4 | xid_ops
1304 2226 | 1 | hashint4 | cid_ops
1307 -- Support routines that are primary members of opfamilies must be immutable
1308 -- (else it suggests that the index ordering isn't fixed). But cross-type
1309 -- members need only be stable, since they are just shorthands
1310 -- for index probe queries.
1311 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
1312 FROM pg_amproc AS p1, pg_proc AS p2
1313 WHERE p1.amproc = p2.oid AND
1314 p1.amproclefttype = p1.amprocrighttype AND
1315 p2.provolatile != 'i';
1316 amprocfamily | amproc | prosrc
1317 --------------+--------+--------
1320 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
1321 FROM pg_amproc AS p1, pg_proc AS p2
1322 WHERE p1.amproc = p2.oid AND
1323 p1.amproclefttype != p1.amprocrighttype AND
1324 p2.provolatile = 'v';
1325 amprocfamily | amproc | prosrc
1326 --------------+--------+--------