3 -- Sanity checks for common errors in making operator/procedure system tables:
4 -- pg_operator, pg_proc, pg_cast, pg_conversion, pg_aggregate, pg_am,
5 -- pg_amop, pg_amproc, pg_opclass, pg_opfamily, pg_index.
7 -- Every test failure in this file should be closely inspected.
8 -- The description of the failing test should be read carefully before
9 -- adjusting the expected output. In most cases, the queries should
10 -- not find *any* matching entries.
12 -- NB: we assume the oidjoins test will have caught any dangling links,
13 -- that is OID or REGPROC fields that are not zero and do not match some
14 -- row in the linked-to table. However, if we want to enforce that a link
15 -- field can't be 0, we have to check it here.
17 -- NB: run this test earlier than the create_operator test, because
18 -- that test creates some bogus operators...
21 -- **************** pg_proc ****************
23 -- Look for illegal values in pg_proc fields.
25 SELECT p1.oid, p1.proname
27 WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
29 p1.pronargdefaults < 0 OR
30 p1.pronargdefaults > p1.pronargs OR
31 array_lower(p1.proargtypes, 1) != 0 OR
32 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR
33 0::oid = ANY (p1.proargtypes) OR
35 CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END OR
36 prokind NOT IN ('f', 'a', 'w', 'p') OR
37 provolatile NOT IN ('i', 's', 'v') OR
38 proparallel NOT IN ('s', 'r', 'u');
40 -- prosrc should never be null; it can be empty only if prosqlbody isn't null
41 SELECT p1.oid, p1.proname
44 SELECT p1.oid, p1.proname
46 WHERE (prosrc = '' OR prosrc = '-') AND prosqlbody IS NULL;
48 -- proretset should only be set for normal functions
49 SELECT p1.oid, p1.proname
51 WHERE proretset AND prokind != 'f';
53 -- currently, no built-in functions should be SECURITY DEFINER;
54 -- this might change in future, but there will probably never be many.
55 SELECT p1.oid, p1.proname
60 -- pronargdefaults should be 0 iff proargdefaults is null
61 SELECT p1.oid, p1.proname
63 WHERE (pronargdefaults <> 0) != (proargdefaults IS NOT NULL);
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.prokind != 'a' OR p2.prokind != 'a') AND
99 (p1.prolang != p2.prolang OR
100 p1.prokind != p2.prokind OR
101 p1.prosecdef != p2.prosecdef OR
102 p1.proleakproof != p2.proleakproof OR
103 p1.proisstrict != p2.proisstrict OR
104 p1.proretset != p2.proretset OR
105 p1.provolatile != p2.provolatile OR
106 p1.pronargs != p2.pronargs);
108 -- Look for uses of different type OIDs in the argument/result type fields
109 -- for different aliases of the same built-in function.
110 -- This indicates that the types are being presumed to be binary-equivalent,
111 -- or that the built-in function is prepared to deal with different types.
112 -- That's not wrong, necessarily, but we make lists of all the types being
113 -- so treated. Note that the expected output of this part of the test will
114 -- need to be modified whenever new pairs of types are made binary-equivalent,
115 -- or when new polymorphic built-in functions are added!
116 -- Note: ignore aggregate functions here, since they all point to the same
117 -- dummy built-in function. Likewise, ignore range and multirange constructor
120 SELECT DISTINCT p1.prorettype::regtype, p2.prorettype::regtype
121 FROM pg_proc AS p1, pg_proc AS p2
122 WHERE p1.oid != p2.oid AND
123 p1.prosrc = p2.prosrc AND
124 p1.prolang = 12 AND p2.prolang = 12 AND
125 p1.prokind != 'a' AND p2.prokind != 'a' AND
126 p1.prosrc NOT LIKE E'range\\_constructor_' AND
127 p2.prosrc NOT LIKE E'range\\_constructor_' AND
128 p1.prosrc NOT LIKE E'multirange\\_constructor_' AND
129 p2.prosrc NOT LIKE E'multirange\\_constructor_' AND
130 (p1.prorettype < p2.prorettype)
133 SELECT DISTINCT p1.proargtypes[0]::regtype, p2.proargtypes[0]::regtype
134 FROM pg_proc AS p1, pg_proc AS p2
135 WHERE p1.oid != p2.oid AND
136 p1.prosrc = p2.prosrc AND
137 p1.prolang = 12 AND p2.prolang = 12 AND
138 p1.prokind != 'a' AND p2.prokind != 'a' AND
139 p1.prosrc NOT LIKE E'range\\_constructor_' AND
140 p2.prosrc NOT LIKE E'range\\_constructor_' AND
141 p1.prosrc NOT LIKE E'multirange\\_constructor_' AND
142 p2.prosrc NOT LIKE E'multirange\\_constructor_' AND
143 (p1.proargtypes[0] < p2.proargtypes[0])
146 SELECT DISTINCT p1.proargtypes[1]::regtype, p2.proargtypes[1]::regtype
147 FROM pg_proc AS p1, pg_proc AS p2
148 WHERE p1.oid != p2.oid AND
149 p1.prosrc = p2.prosrc AND
150 p1.prolang = 12 AND p2.prolang = 12 AND
151 p1.prokind != 'a' AND p2.prokind != 'a' AND
152 p1.prosrc NOT LIKE E'range\\_constructor_' AND
153 p2.prosrc NOT LIKE E'range\\_constructor_' AND
154 p1.prosrc NOT LIKE E'multirange\\_constructor_' AND
155 p2.prosrc NOT LIKE E'multirange\\_constructor_' AND
156 (p1.proargtypes[1] < p2.proargtypes[1])
159 SELECT DISTINCT p1.proargtypes[2]::regtype, p2.proargtypes[2]::regtype
160 FROM pg_proc AS p1, pg_proc AS p2
161 WHERE p1.oid != p2.oid AND
162 p1.prosrc = p2.prosrc AND
163 p1.prolang = 12 AND p2.prolang = 12 AND
164 p1.prokind != 'a' AND p2.prokind != 'a' AND
165 (p1.proargtypes[2] < p2.proargtypes[2])
168 SELECT DISTINCT p1.proargtypes[3]::regtype, p2.proargtypes[3]::regtype
169 FROM pg_proc AS p1, pg_proc AS p2
170 WHERE p1.oid != p2.oid AND
171 p1.prosrc = p2.prosrc AND
172 p1.prolang = 12 AND p2.prolang = 12 AND
173 p1.prokind != 'a' AND p2.prokind != 'a' AND
174 (p1.proargtypes[3] < p2.proargtypes[3])
177 SELECT DISTINCT p1.proargtypes[4]::regtype, p2.proargtypes[4]::regtype
178 FROM pg_proc AS p1, pg_proc AS p2
179 WHERE p1.oid != p2.oid AND
180 p1.prosrc = p2.prosrc AND
181 p1.prolang = 12 AND p2.prolang = 12 AND
182 p1.prokind != 'a' AND p2.prokind != 'a' AND
183 (p1.proargtypes[4] < p2.proargtypes[4])
186 SELECT DISTINCT p1.proargtypes[5]::regtype, p2.proargtypes[5]::regtype
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 p1.prokind != 'a' AND p2.prokind != 'a' AND
192 (p1.proargtypes[5] < p2.proargtypes[5])
195 SELECT DISTINCT p1.proargtypes[6]::regtype, p2.proargtypes[6]::regtype
196 FROM pg_proc AS p1, pg_proc AS p2
197 WHERE p1.oid != p2.oid AND
198 p1.prosrc = p2.prosrc AND
199 p1.prolang = 12 AND p2.prolang = 12 AND
200 p1.prokind != 'a' AND p2.prokind != 'a' AND
201 (p1.proargtypes[6] < p2.proargtypes[6])
204 SELECT DISTINCT p1.proargtypes[7]::regtype, p2.proargtypes[7]::regtype
205 FROM pg_proc AS p1, pg_proc AS p2
206 WHERE p1.oid != p2.oid AND
207 p1.prosrc = p2.prosrc AND
208 p1.prolang = 12 AND p2.prolang = 12 AND
209 p1.prokind != 'a' AND p2.prokind != 'a' AND
210 (p1.proargtypes[7] < p2.proargtypes[7])
213 -- Look for functions that return type "internal" and do not have any
214 -- "internal" argument. Such a function would be a security hole since
215 -- it might be used to call an internal function from an SQL command.
216 -- As of 7.3 this query should find only internal_in, which is safe because
217 -- it always throws an error when called.
219 SELECT p1.oid, p1.proname
221 WHERE p1.prorettype = 'internal'::regtype AND NOT
222 'internal'::regtype = ANY (p1.proargtypes);
224 -- Look for functions that return a polymorphic type and do not have any
225 -- polymorphic argument. Calls of such functions would be unresolvable
226 -- at parse time. As of 9.6 this query should find only some input functions
227 -- and GiST support functions associated with these pseudotypes.
229 SELECT p1.oid, p1.proname
231 WHERE p1.prorettype IN
232 ('anyelement'::regtype, 'anyarray'::regtype, 'anynonarray'::regtype,
235 ('anyelement'::regtype = ANY (p1.proargtypes) OR
236 'anyarray'::regtype = ANY (p1.proargtypes) OR
237 'anynonarray'::regtype = ANY (p1.proargtypes) OR
238 'anyenum'::regtype = ANY (p1.proargtypes) OR
239 'anyrange'::regtype = ANY (p1.proargtypes) OR
240 'anymultirange'::regtype = ANY (p1.proargtypes))
243 -- anyrange and anymultirange are tighter than the rest, can only resolve
246 SELECT p1.oid, p1.proname
248 WHERE p1.prorettype IN ('anyrange'::regtype, 'anymultirange'::regtype)
250 ('anyrange'::regtype = ANY (p1.proargtypes) OR
251 'anymultirange'::regtype = ANY (p1.proargtypes))
254 -- similarly for the anycompatible family
256 SELECT p1.oid, p1.proname
258 WHERE p1.prorettype IN
259 ('anycompatible'::regtype, 'anycompatiblearray'::regtype,
260 'anycompatiblenonarray'::regtype)
262 ('anycompatible'::regtype = ANY (p1.proargtypes) OR
263 'anycompatiblearray'::regtype = ANY (p1.proargtypes) OR
264 'anycompatiblenonarray'::regtype = ANY (p1.proargtypes) OR
265 'anycompatiblerange'::regtype = ANY (p1.proargtypes))
268 SELECT p1.oid, p1.proname
270 WHERE p1.prorettype = 'anycompatiblerange'::regtype
272 'anycompatiblerange'::regtype = ANY (p1.proargtypes)
276 -- Look for functions that accept cstring and are neither datatype input
277 -- functions nor encoding conversion functions. It's almost never a good
278 -- idea to use cstring input for a function meant to be called from SQL;
279 -- text should be used instead, because cstring lacks suitable casts.
280 -- As of 9.6 this query should find only cstring_out and cstring_send.
281 -- However, we must manually exclude shell_in, which might or might not be
282 -- rejected by the EXISTS clause depending on whether there are currently
285 SELECT p1.oid, p1.proname
287 WHERE 'cstring'::regtype = ANY (p1.proargtypes)
288 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typinput = p1.oid)
289 AND NOT EXISTS(SELECT 1 FROM pg_conversion WHERE conproc = p1.oid)
290 AND p1.oid != 'shell_in(cstring)'::regprocedure
293 -- Likewise, look for functions that return cstring and aren't datatype output
294 -- functions nor typmod output functions.
295 -- As of 9.6 this query should find only cstring_in and cstring_recv.
296 -- However, we must manually exclude shell_out.
298 SELECT p1.oid, p1.proname
300 WHERE p1.prorettype = 'cstring'::regtype
301 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typoutput = p1.oid)
302 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typmodout = p1.oid)
303 AND p1.oid != 'shell_out(void)'::regprocedure
306 -- Check for length inconsistencies between the various argument-info arrays.
308 SELECT p1.oid, p1.proname
310 WHERE proallargtypes IS NOT NULL AND
311 array_length(proallargtypes,1) < array_length(proargtypes,1);
313 SELECT p1.oid, p1.proname
315 WHERE proargmodes IS NOT NULL AND
316 array_length(proargmodes,1) < array_length(proargtypes,1);
318 SELECT p1.oid, p1.proname
320 WHERE proargnames IS NOT NULL AND
321 array_length(proargnames,1) < array_length(proargtypes,1);
323 SELECT p1.oid, p1.proname
325 WHERE proallargtypes IS NOT NULL AND proargmodes IS NOT NULL AND
326 array_length(proallargtypes,1) <> array_length(proargmodes,1);
328 SELECT p1.oid, p1.proname
330 WHERE proallargtypes IS NOT NULL AND proargnames IS NOT NULL AND
331 array_length(proallargtypes,1) <> array_length(proargnames,1);
333 SELECT p1.oid, p1.proname
335 WHERE proargmodes IS NOT NULL AND proargnames IS NOT NULL AND
336 array_length(proargmodes,1) <> array_length(proargnames,1);
338 -- Check that proallargtypes matches proargtypes
339 SELECT p1.oid, p1.proname, p1.proargtypes, p1.proallargtypes, p1.proargmodes
341 WHERE proallargtypes IS NOT NULL AND
342 ARRAY(SELECT unnest(proargtypes)) <>
343 ARRAY(SELECT proallargtypes[i]
344 FROM generate_series(1, array_length(proallargtypes, 1)) g(i)
345 WHERE proargmodes IS NULL OR proargmodes[i] IN ('i', 'b', 'v'));
347 -- Check for type of the variadic array parameter's elements.
348 -- provariadic should be ANYOID if the type of the last element is ANYOID,
349 -- ANYELEMENTOID if the type of the last element is ANYARRAYOID,
350 -- ANYCOMPATIBLEOID if the type of the last element is ANYCOMPATIBLEARRAYOID,
351 -- and otherwise the element type corresponding to the array type.
353 SELECT oid::regprocedure, provariadic::regtype, proargtypes::regtype[]
355 WHERE provariadic != 0
356 AND case proargtypes[array_length(proargtypes, 1)-1]
357 WHEN '"any"'::regtype THEN '"any"'::regtype
358 WHEN 'anyarray'::regtype THEN 'anyelement'::regtype
359 WHEN 'anycompatiblearray'::regtype THEN 'anycompatible'::regtype
362 WHERE t.typarray = proargtypes[array_length(proargtypes, 1)-1])
365 -- Check that all and only those functions with a variadic type have
366 -- a variadic argument.
367 SELECT oid::regprocedure, proargmodes, provariadic
369 WHERE (proargmodes IS NOT NULL AND 'v' = any(proargmodes))
373 -- Check for prosupport functions with the wrong signature
374 SELECT p1.oid, p1.proname, p2.oid, p2.proname
375 FROM pg_proc AS p1, pg_proc AS p2
376 WHERE p2.oid = p1.prosupport AND
377 (p2.prorettype != 'internal'::regtype OR p2.proretset OR p2.pronargs != 1
378 OR p2.proargtypes[0] != 'internal'::regtype);
380 -- Insist that all built-in pg_proc entries have descriptions
381 SELECT p1.oid, p1.proname
382 FROM pg_proc as p1 LEFT JOIN pg_description as d
383 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0
384 WHERE d.classoid IS NULL AND p1.oid <= 9999;
386 -- List of built-in leakproof functions
388 -- Leakproof functions should only be added after carefully
389 -- scrutinizing all possibly executed codepaths for possible
390 -- information leaks. Don't add functions here unless you know what a
391 -- leakproof function is. If unsure, don't mark it as such.
393 -- temporarily disable fancy output, so catalog changes create less diff noise
396 SELECT p1.oid::regprocedure
397 FROM pg_proc p1 JOIN pg_namespace pn
398 ON pronamespace = pn.oid
399 WHERE nspname = 'pg_catalog' AND proleakproof
402 -- Check that functions without argument are not marked as leakproof.
403 SELECT p1.oid::regprocedure
404 FROM pg_proc p1 JOIN pg_namespace pn
405 ON pronamespace = pn.oid
406 WHERE nspname = 'pg_catalog' AND proleakproof AND pronargs = 0
409 -- restore normal output mode
412 -- List of functions used by libpq's fe-lobj.c
414 -- If the output of this query changes, you probably broke libpq.
415 -- lo_initialize() assumes that there will be at most one match for
417 select proname, oid from pg_catalog.pg_proc
432 and pronamespace = (select oid from pg_catalog.pg_namespace
433 where nspname = 'pg_catalog')
436 -- Check that all immutable functions are marked parallel safe
437 SELECT p1.oid, p1.proname
439 WHERE provolatile = 'i' AND proparallel = 'u';
442 -- **************** pg_cast ****************
444 -- Catch bogus values in pg_cast columns (other than cases detected by
449 WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i')
450 OR castmethod NOT IN ('f', 'b' ,'i');
452 -- Check that castfunc is nonzero only for cast methods that need a function,
453 -- and zero otherwise
457 WHERE (castmethod = 'f' AND castfunc = 0)
458 OR (castmethod IN ('b', 'i') AND castfunc <> 0);
460 -- Look for casts to/from the same type that aren't length coercion functions.
461 -- (We assume they are length coercions if they take multiple arguments.)
462 -- Such entries are not necessarily harmful, but they are useless.
466 WHERE castsource = casttarget AND castfunc = 0;
469 FROM pg_cast c, pg_proc p
470 WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
472 -- Look for cast functions that don't have the right signature. The
473 -- argument and result types in pg_proc must be the same as, or binary
474 -- compatible with, what it says in pg_cast.
475 -- As a special case, we allow casts from CHAR(n) that use functions
476 -- declared to take TEXT. This does not pass the binary-coercibility test
477 -- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
478 -- are the same, so long as the function is one that ignores trailing blanks.
481 FROM pg_cast c, pg_proc p
482 WHERE c.castfunc = p.oid AND
483 (p.pronargs < 1 OR p.pronargs > 3
484 OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
485 OR (c.castsource = 'character'::regtype AND
486 p.proargtypes[0] = 'text'::regtype))
487 OR NOT binary_coercible(p.prorettype, c.casttarget));
490 FROM pg_cast c, pg_proc p
491 WHERE c.castfunc = p.oid AND
492 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
493 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
495 -- Look for binary compatible casts that do not have the reverse
496 -- direction registered as well, or where the reverse direction is not
497 -- also binary compatible. This is legal, but usually not intended.
499 -- As of 7.4, this finds the casts from text and varchar to bpchar, because
500 -- those are binary-compatible while the reverse way goes through rtrim().
502 -- As of 8.2, this finds the cast from cidr to inet, because that is a
503 -- trivial binary coercion while the other way goes through inet_to_cidr().
505 -- As of 8.3, this finds the casts from xml to text, varchar, and bpchar,
506 -- because those are binary-compatible while the reverse goes through
507 -- texttoxml(), which does an XML syntax check.
509 -- As of 9.1, this finds the cast from pg_node_tree to text, which we
510 -- intentionally do not provide a reverse pathway for.
512 SELECT castsource::regtype, casttarget::regtype, castfunc, castcontext
514 WHERE c.castmethod = 'b' AND
515 NOT EXISTS (SELECT 1 FROM pg_cast k
516 WHERE k.castmethod = 'b' AND
517 k.castsource = c.casttarget AND
518 k.casttarget = c.castsource);
521 -- **************** pg_conversion ****************
523 -- Look for illegal values in pg_conversion fields.
525 SELECT c.oid, c.conname
526 FROM pg_conversion as c
527 WHERE c.conproc = 0 OR
528 pg_encoding_to_char(conforencoding) = '' OR
529 pg_encoding_to_char(contoencoding) = '';
531 -- Look for conprocs that don't have the expected signature.
533 SELECT p.oid, p.proname, c.oid, c.conname
534 FROM pg_proc p, pg_conversion c
535 WHERE p.oid = c.conproc AND
536 (p.prorettype != 'int4'::regtype OR p.proretset OR
538 p.proargtypes[0] != 'int4'::regtype OR
539 p.proargtypes[1] != 'int4'::regtype OR
540 p.proargtypes[2] != 'cstring'::regtype OR
541 p.proargtypes[3] != 'internal'::regtype OR
542 p.proargtypes[4] != 'int4'::regtype OR
543 p.proargtypes[5] != 'bool'::regtype);
545 -- Check for conprocs that don't perform the specific conversion that
546 -- pg_conversion alleges they do, by trying to invoke each conversion
547 -- on some simple ASCII data. (The conproc should throw an error if
548 -- it doesn't accept the encodings that are passed to it.)
549 -- Unfortunately, we can't test non-default conprocs this way, because
550 -- there is no way to ask convert() to invoke them, and we cannot call
551 -- them directly from SQL. But there are no non-default built-in
552 -- conversions anyway.
553 -- (Similarly, this doesn't cope with any search path issues.)
555 SELECT c.oid, c.conname
556 FROM pg_conversion as c
558 convert('ABC'::bytea, pg_encoding_to_char(conforencoding),
559 pg_encoding_to_char(contoencoding)) != 'ABC';
562 -- **************** pg_operator ****************
564 -- Look for illegal values in pg_operator fields.
566 SELECT o1.oid, o1.oprname
567 FROM pg_operator as o1
568 WHERE (o1.oprkind != 'b' AND o1.oprkind != 'l') OR
569 o1.oprresult = 0 OR o1.oprcode = 0;
571 -- Look for missing or unwanted operand types
573 SELECT o1.oid, o1.oprname
574 FROM pg_operator as o1
575 WHERE (o1.oprleft = 0 and o1.oprkind != 'l') OR
576 (o1.oprleft != 0 and o1.oprkind = 'l') OR
579 -- Look for conflicting operator definitions (same names and input datatypes).
581 SELECT o1.oid, o1.oprcode, o2.oid, o2.oprcode
582 FROM pg_operator AS o1, pg_operator AS o2
583 WHERE o1.oid != o2.oid AND
584 o1.oprname = o2.oprname AND
585 o1.oprkind = o2.oprkind AND
586 o1.oprleft = o2.oprleft AND
587 o1.oprright = o2.oprright;
589 -- Look for commutative operators that don't commute.
590 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
591 -- We expect that B will always say that B.oprcom = A as well; that's not
592 -- inherently essential, but it would be inefficient not to mark it so.
594 SELECT o1.oid, o1.oprcode, o2.oid, o2.oprcode
595 FROM pg_operator AS o1, pg_operator AS o2
596 WHERE o1.oprcom = o2.oid AND
597 (o1.oprkind != 'b' OR
598 o1.oprleft != o2.oprright OR
599 o1.oprright != o2.oprleft OR
600 o1.oprresult != o2.oprresult OR
601 o1.oid != o2.oprcom);
603 -- Look for negatory operators that don't agree.
604 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
605 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
606 -- single-operand operators.
607 -- We expect that B will always say that B.oprnegate = A as well; that's not
608 -- inherently essential, but it would be inefficient not to mark it so.
609 -- Also, A and B had better not be the same operator.
611 SELECT o1.oid, o1.oprcode, o2.oid, o2.oprcode
612 FROM pg_operator AS o1, pg_operator AS o2
613 WHERE o1.oprnegate = o2.oid AND
614 (o1.oprkind != o2.oprkind OR
615 o1.oprleft != o2.oprleft OR
616 o1.oprright != o2.oprright OR
617 o1.oprresult != 'bool'::regtype OR
618 o2.oprresult != 'bool'::regtype OR
619 o1.oid != o2.oprnegate OR
622 -- Make a list of the names of operators that are claimed to be commutator
623 -- pairs. This list will grow over time, but before accepting a new entry
624 -- make sure you didn't link the wrong operators.
626 SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
627 FROM pg_operator o1, pg_operator o2
628 WHERE o1.oprcom = o2.oid AND o1.oprname <= o2.oprname
631 -- Likewise for negator pairs.
633 SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
634 FROM pg_operator o1, pg_operator o2
635 WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname
638 -- A mergejoinable or hashjoinable operator must be binary, must return
639 -- boolean, and must have a commutator (itself, unless it's a cross-type
642 SELECT o1.oid, o1.oprname FROM pg_operator AS o1
643 WHERE (o1.oprcanmerge OR o1.oprcanhash) AND NOT
644 (o1.oprkind = 'b' AND o1.oprresult = 'bool'::regtype AND o1.oprcom != 0);
646 -- What's more, the commutator had better be mergejoinable/hashjoinable too.
648 SELECT o1.oid, o1.oprname, o2.oid, o2.oprname
649 FROM pg_operator AS o1, pg_operator AS o2
650 WHERE o1.oprcom = o2.oid AND
651 (o1.oprcanmerge != o2.oprcanmerge OR
652 o1.oprcanhash != o2.oprcanhash);
654 -- Mergejoinable operators should appear as equality members of btree index
657 SELECT o1.oid, o1.oprname
658 FROM pg_operator AS o1
659 WHERE o1.oprcanmerge AND NOT EXISTS
660 (SELECT 1 FROM pg_amop
661 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
662 amopopr = o1.oid AND amopstrategy = 3);
666 SELECT o1.oid, o1.oprname, p.amopfamily
667 FROM pg_operator AS o1, pg_amop p
668 WHERE amopopr = o1.oid
669 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
671 AND NOT o1.oprcanmerge;
673 -- Hashable operators should appear as members of hash index opfamilies.
675 SELECT o1.oid, o1.oprname
676 FROM pg_operator AS o1
677 WHERE o1.oprcanhash AND NOT EXISTS
678 (SELECT 1 FROM pg_amop
679 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
680 amopopr = o1.oid AND amopstrategy = 1);
684 SELECT o1.oid, o1.oprname, p.amopfamily
685 FROM pg_operator AS o1, pg_amop p
686 WHERE amopopr = o1.oid
687 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
688 AND NOT o1.oprcanhash;
690 -- Check that each operator defined in pg_operator matches its oprcode entry
691 -- in pg_proc. Easiest to do this separately for each oprkind.
693 SELECT o1.oid, o1.oprname, p1.oid, p1.proname
694 FROM pg_operator AS o1, pg_proc AS p1
695 WHERE o1.oprcode = p1.oid AND
698 OR NOT binary_coercible(p1.prorettype, o1.oprresult)
699 OR NOT binary_coercible(o1.oprleft, p1.proargtypes[0])
700 OR NOT binary_coercible(o1.oprright, p1.proargtypes[1]));
702 SELECT o1.oid, o1.oprname, p1.oid, p1.proname
703 FROM pg_operator AS o1, pg_proc AS p1
704 WHERE o1.oprcode = p1.oid AND
707 OR NOT binary_coercible(p1.prorettype, o1.oprresult)
708 OR NOT binary_coercible(o1.oprright, p1.proargtypes[0])
711 -- If the operator is mergejoinable or hashjoinable, its underlying function
712 -- should not be volatile.
714 SELECT o1.oid, o1.oprname, p1.oid, p1.proname
715 FROM pg_operator AS o1, pg_proc AS p1
716 WHERE o1.oprcode = p1.oid AND
717 (o1.oprcanmerge OR o1.oprcanhash) AND
718 p1.provolatile = 'v';
720 -- If oprrest is set, the operator must return boolean,
721 -- and it must link to a proc with the right signature
722 -- to be a restriction selectivity estimator.
723 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
725 SELECT o1.oid, o1.oprname, p2.oid, p2.proname
726 FROM pg_operator AS o1, pg_proc AS p2
727 WHERE o1.oprrest = p2.oid AND
728 (o1.oprresult != 'bool'::regtype OR
729 p2.prorettype != 'float8'::regtype OR p2.proretset OR
731 p2.proargtypes[0] != 'internal'::regtype OR
732 p2.proargtypes[1] != 'oid'::regtype OR
733 p2.proargtypes[2] != 'internal'::regtype OR
734 p2.proargtypes[3] != 'int4'::regtype);
736 -- If oprjoin is set, the operator must be a binary boolean op,
737 -- and it must link to a proc with the right signature
738 -- to be a join selectivity estimator.
739 -- The proc signature we want is: float8 proc(internal, oid, internal, int2, internal)
740 -- (Note: the old signature with only 4 args is still allowed, but no core
741 -- estimator should be using it.)
743 SELECT o1.oid, o1.oprname, p2.oid, p2.proname
744 FROM pg_operator AS o1, pg_proc AS p2
745 WHERE o1.oprjoin = p2.oid AND
746 (o1.oprkind != 'b' OR o1.oprresult != 'bool'::regtype OR
747 p2.prorettype != 'float8'::regtype OR p2.proretset OR
749 p2.proargtypes[0] != 'internal'::regtype OR
750 p2.proargtypes[1] != 'oid'::regtype OR
751 p2.proargtypes[2] != 'internal'::regtype OR
752 p2.proargtypes[3] != 'int2'::regtype OR
753 p2.proargtypes[4] != 'internal'::regtype);
755 -- Insist that all built-in pg_operator entries have descriptions
756 SELECT o1.oid, o1.oprname
757 FROM pg_operator as o1 LEFT JOIN pg_description as d
758 ON o1.tableoid = d.classoid and o1.oid = d.objoid and d.objsubid = 0
759 WHERE d.classoid IS NULL AND o1.oid <= 9999;
761 -- Check that operators' underlying functions have suitable comments,
762 -- namely 'implementation of XXX operator'. (Note: it's not necessary to
763 -- put such comments into pg_proc.dat; initdb will generate them as needed.)
764 -- In some cases involving legacy names for operators, there are multiple
765 -- operators referencing the same pg_proc entry, so ignore operators whose
766 -- comments say they are deprecated.
767 -- We also have a few functions that are both operator support and meant to
768 -- be called directly; those should have comments matching their operator.
770 SELECT p.oid as p_oid, proname, o.oid as o_oid,
771 pd.description as prodesc,
772 'implementation of ' || oprname || ' operator' as expecteddesc,
773 od.description as oprdesc
774 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid
775 LEFT JOIN pg_description pd ON
776 (pd.objoid = p.oid and pd.classoid = p.tableoid and pd.objsubid = 0)
777 LEFT JOIN pg_description od ON
778 (od.objoid = o.oid and od.classoid = o.tableoid and od.objsubid = 0)
781 SELECT * FROM funcdescs
782 WHERE prodesc IS DISTINCT FROM expecteddesc
783 AND oprdesc NOT LIKE 'deprecated%'
784 AND prodesc IS DISTINCT FROM oprdesc;
786 -- Show all the operator-implementation functions that have their own
787 -- comments. This should happen only in cases where the function and
788 -- operator syntaxes are both documented at the user level.
789 -- This should be a pretty short list; it's mostly legacy cases.
791 SELECT p.oid as p_oid, proname, o.oid as o_oid,
792 pd.description as prodesc,
793 'implementation of ' || oprname || ' operator' as expecteddesc,
794 od.description as oprdesc
795 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid
796 LEFT JOIN pg_description pd ON
797 (pd.objoid = p.oid and pd.classoid = p.tableoid and pd.objsubid = 0)
798 LEFT JOIN pg_description od ON
799 (od.objoid = o.oid and od.classoid = o.tableoid and od.objsubid = 0)
802 SELECT p_oid, proname, prodesc FROM funcdescs
803 WHERE prodesc IS DISTINCT FROM expecteddesc
804 AND oprdesc NOT LIKE 'deprecated%'
807 -- Operators that are commutator pairs should have identical volatility
808 -- and leakproofness markings on their implementation functions.
809 SELECT o1.oid, o1.oprcode, o2.oid, o2.oprcode
810 FROM pg_operator AS o1, pg_operator AS o2, pg_proc AS p1, pg_proc AS p2
811 WHERE o1.oprcom = o2.oid AND p1.oid = o1.oprcode AND p2.oid = o2.oprcode AND
812 (p1.provolatile != p2.provolatile OR
813 p1.proleakproof != p2.proleakproof);
815 -- Likewise for negator pairs.
816 SELECT o1.oid, o1.oprcode, o2.oid, o2.oprcode
817 FROM pg_operator AS o1, pg_operator AS o2, pg_proc AS p1, pg_proc AS p2
818 WHERE o1.oprnegate = o2.oid AND p1.oid = o1.oprcode AND p2.oid = o2.oprcode AND
819 (p1.provolatile != p2.provolatile OR
820 p1.proleakproof != p2.proleakproof);
822 -- Btree comparison operators' functions should have the same volatility
823 -- and leakproofness markings as the associated comparison support function.
824 SELECT pp.oid::regprocedure as proc, pp.provolatile as vp, pp.proleakproof as lp,
825 po.oid::regprocedure as opr, po.provolatile as vo, po.proleakproof as lo
826 FROM pg_proc pp, pg_proc po, pg_operator o, pg_amproc ap, pg_amop ao
827 WHERE pp.oid = ap.amproc AND po.oid = o.oprcode AND o.oid = ao.amopopr AND
828 ao.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
829 ao.amopfamily = ap.amprocfamily AND
830 ao.amoplefttype = ap.amproclefttype AND
831 ao.amoprighttype = ap.amprocrighttype AND
833 (pp.provolatile != po.provolatile OR
834 pp.proleakproof != po.proleakproof)
838 -- **************** pg_aggregate ****************
840 -- Look for illegal values in pg_aggregate fields.
842 SELECT ctid, aggfnoid::oid
843 FROM pg_aggregate as a
844 WHERE aggfnoid = 0 OR aggtransfn = 0 OR
845 aggkind NOT IN ('n', 'o', 'h') OR
846 aggnumdirectargs < 0 OR
847 (aggkind = 'n' AND aggnumdirectargs > 0) OR
848 aggfinalmodify NOT IN ('r', 's', 'w') OR
849 aggmfinalmodify NOT IN ('r', 's', 'w') OR
850 aggtranstype = 0 OR aggtransspace < 0 OR aggmtransspace < 0;
852 -- Make sure the matching pg_proc entry is sensible, too.
854 SELECT a.aggfnoid::oid, p.proname
855 FROM pg_aggregate as a, pg_proc as p
856 WHERE a.aggfnoid = p.oid AND
857 (p.prokind != 'a' OR p.proretset OR p.pronargs < a.aggnumdirectargs);
859 -- Make sure there are no prokind = PROKIND_AGGREGATE pg_proc entries without matches.
863 WHERE p.prokind = 'a' AND
864 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
866 -- If there is no finalfn then the output type must be the transtype.
868 SELECT a.aggfnoid::oid, p.proname
869 FROM pg_aggregate as a, pg_proc as p
870 WHERE a.aggfnoid = p.oid AND
871 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
873 -- Cross-check transfn against its entry in pg_proc.
874 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
875 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
876 WHERE a.aggfnoid = p.oid AND
877 a.aggtransfn = ptr.oid AND
879 OR NOT (ptr.pronargs =
880 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1
881 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)
882 OR NOT binary_coercible(ptr.prorettype, a.aggtranstype)
883 OR NOT binary_coercible(a.aggtranstype, ptr.proargtypes[0])
884 OR (p.pronargs > 0 AND
885 NOT binary_coercible(p.proargtypes[0], ptr.proargtypes[1]))
886 OR (p.pronargs > 1 AND
887 NOT binary_coercible(p.proargtypes[1], ptr.proargtypes[2]))
888 OR (p.pronargs > 2 AND
889 NOT binary_coercible(p.proargtypes[2], ptr.proargtypes[3]))
890 OR (p.pronargs > 3 AND
891 NOT binary_coercible(p.proargtypes[3], ptr.proargtypes[4]))
892 -- we could carry the check further, but 4 args is enough for now
896 -- Cross-check finalfn (if present) against its entry in pg_proc.
898 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
899 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
900 WHERE a.aggfnoid = p.oid AND
901 a.aggfinalfn = pfn.oid AND
903 NOT binary_coercible(pfn.prorettype, p.prorettype) OR
904 NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]) OR
905 CASE WHEN a.aggfinalextra THEN pfn.pronargs != p.pronargs + 1
906 ELSE pfn.pronargs != a.aggnumdirectargs + 1 END
907 OR (pfn.pronargs > 1 AND
908 NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1]))
909 OR (pfn.pronargs > 2 AND
910 NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2]))
911 OR (pfn.pronargs > 3 AND
912 NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3]))
913 -- we could carry the check further, but 4 args is enough for now
914 OR (pfn.pronargs > 4)
917 -- If transfn is strict then either initval should be non-NULL, or
918 -- input type should match transtype so that the first non-null input
919 -- can be assigned as the state value.
921 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
922 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
923 WHERE a.aggfnoid = p.oid AND
924 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
925 a.agginitval IS NULL AND
926 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
928 -- Check for inconsistent specifications of moving-aggregate columns.
930 SELECT ctid, aggfnoid::oid
931 FROM pg_aggregate as a
932 WHERE aggmtranstype != 0 AND
933 (aggmtransfn = 0 OR aggminvtransfn = 0);
935 SELECT ctid, aggfnoid::oid
936 FROM pg_aggregate as a
937 WHERE aggmtranstype = 0 AND
938 (aggmtransfn != 0 OR aggminvtransfn != 0 OR aggmfinalfn != 0 OR
939 aggmtransspace != 0 OR aggminitval IS NOT NULL);
941 -- If there is no mfinalfn then the output type must be the mtranstype.
943 SELECT a.aggfnoid::oid, p.proname
944 FROM pg_aggregate as a, pg_proc as p
945 WHERE a.aggfnoid = p.oid AND
946 a.aggmtransfn != 0 AND
947 a.aggmfinalfn = 0 AND p.prorettype != a.aggmtranstype;
949 -- Cross-check mtransfn (if present) against its entry in pg_proc.
950 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
951 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
952 WHERE a.aggfnoid = p.oid AND
953 a.aggmtransfn = ptr.oid AND
955 OR NOT (ptr.pronargs =
956 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1
957 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)
958 OR NOT binary_coercible(ptr.prorettype, a.aggmtranstype)
959 OR NOT binary_coercible(a.aggmtranstype, ptr.proargtypes[0])
960 OR (p.pronargs > 0 AND
961 NOT binary_coercible(p.proargtypes[0], ptr.proargtypes[1]))
962 OR (p.pronargs > 1 AND
963 NOT binary_coercible(p.proargtypes[1], ptr.proargtypes[2]))
964 OR (p.pronargs > 2 AND
965 NOT binary_coercible(p.proargtypes[2], ptr.proargtypes[3]))
966 -- we could carry the check further, but 3 args is enough for now
970 -- Cross-check minvtransfn (if present) against its entry in pg_proc.
971 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
972 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
973 WHERE a.aggfnoid = p.oid AND
974 a.aggminvtransfn = ptr.oid AND
976 OR NOT (ptr.pronargs =
977 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1
978 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)
979 OR NOT binary_coercible(ptr.prorettype, a.aggmtranstype)
980 OR NOT binary_coercible(a.aggmtranstype, ptr.proargtypes[0])
981 OR (p.pronargs > 0 AND
982 NOT binary_coercible(p.proargtypes[0], ptr.proargtypes[1]))
983 OR (p.pronargs > 1 AND
984 NOT binary_coercible(p.proargtypes[1], ptr.proargtypes[2]))
985 OR (p.pronargs > 2 AND
986 NOT binary_coercible(p.proargtypes[2], ptr.proargtypes[3]))
987 -- we could carry the check further, but 3 args is enough for now
991 -- Cross-check mfinalfn (if present) against its entry in pg_proc.
993 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
994 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
995 WHERE a.aggfnoid = p.oid AND
996 a.aggmfinalfn = pfn.oid AND
998 NOT binary_coercible(pfn.prorettype, p.prorettype) OR
999 NOT binary_coercible(a.aggmtranstype, pfn.proargtypes[0]) OR
1000 CASE WHEN a.aggmfinalextra THEN pfn.pronargs != p.pronargs + 1
1001 ELSE pfn.pronargs != a.aggnumdirectargs + 1 END
1002 OR (pfn.pronargs > 1 AND
1003 NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1]))
1004 OR (pfn.pronargs > 2 AND
1005 NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2]))
1006 OR (pfn.pronargs > 3 AND
1007 NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3]))
1008 -- we could carry the check further, but 4 args is enough for now
1009 OR (pfn.pronargs > 4)
1012 -- If mtransfn is strict then either minitval should be non-NULL, or
1013 -- input type should match mtranstype so that the first non-null input
1014 -- can be assigned as the state value.
1016 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
1017 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
1018 WHERE a.aggfnoid = p.oid AND
1019 a.aggmtransfn = ptr.oid AND ptr.proisstrict AND
1020 a.aggminitval IS NULL AND
1021 NOT binary_coercible(p.proargtypes[0], a.aggmtranstype);
1023 -- mtransfn and minvtransfn should have same strictness setting.
1025 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname, iptr.oid, iptr.proname
1026 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr, pg_proc AS iptr
1027 WHERE a.aggfnoid = p.oid AND
1028 a.aggmtransfn = ptr.oid AND
1029 a.aggminvtransfn = iptr.oid AND
1030 ptr.proisstrict != iptr.proisstrict;
1032 -- Check that all combine functions have signature
1033 -- combine(transtype, transtype) returns transtype
1035 SELECT a.aggfnoid, p.proname
1036 FROM pg_aggregate as a, pg_proc as p
1037 WHERE a.aggcombinefn = p.oid AND
1039 p.prorettype != p.proargtypes[0] OR
1040 p.prorettype != p.proargtypes[1] OR
1041 NOT binary_coercible(a.aggtranstype, p.proargtypes[0]));
1043 -- Check that no combine function for an INTERNAL transtype is strict.
1045 SELECT a.aggfnoid, p.proname
1046 FROM pg_aggregate as a, pg_proc as p
1047 WHERE a.aggcombinefn = p.oid AND
1048 a.aggtranstype = 'internal'::regtype AND p.proisstrict;
1050 -- serialize/deserialize functions should be specified only for aggregates
1051 -- with transtype internal and a combine function, and we should have both
1052 -- or neither of them.
1054 SELECT aggfnoid, aggtranstype, aggserialfn, aggdeserialfn
1056 WHERE (aggserialfn != 0 OR aggdeserialfn != 0)
1057 AND (aggtranstype != 'internal'::regtype OR aggcombinefn = 0 OR
1058 aggserialfn = 0 OR aggdeserialfn = 0);
1060 -- Check that all serialization functions have signature
1061 -- serialize(internal) returns bytea
1062 -- Also insist that they be strict; it's wasteful to run them on NULLs.
1064 SELECT a.aggfnoid, p.proname
1065 FROM pg_aggregate as a, pg_proc as p
1066 WHERE a.aggserialfn = p.oid AND
1067 (p.prorettype != 'bytea'::regtype OR p.pronargs != 1 OR
1068 p.proargtypes[0] != 'internal'::regtype OR
1071 -- Check that all deserialization functions have signature
1072 -- deserialize(bytea, internal) returns internal
1073 -- Also insist that they be strict; it's wasteful to run them on NULLs.
1075 SELECT a.aggfnoid, p.proname
1076 FROM pg_aggregate as a, pg_proc as p
1077 WHERE a.aggdeserialfn = p.oid AND
1078 (p.prorettype != 'internal'::regtype OR p.pronargs != 2 OR
1079 p.proargtypes[0] != 'bytea'::regtype OR
1080 p.proargtypes[1] != 'internal'::regtype OR
1083 -- Check that aggregates which have the same transition function also have
1084 -- the same combine, serialization, and deserialization functions.
1085 -- While that isn't strictly necessary, it's fishy if they don't.
1087 SELECT a.aggfnoid, a.aggcombinefn, a.aggserialfn, a.aggdeserialfn,
1088 b.aggfnoid, b.aggcombinefn, b.aggserialfn, b.aggdeserialfn
1090 pg_aggregate a, pg_aggregate b
1092 a.aggfnoid < b.aggfnoid AND a.aggtransfn = b.aggtransfn AND
1093 (a.aggcombinefn != b.aggcombinefn OR a.aggserialfn != b.aggserialfn
1094 OR a.aggdeserialfn != b.aggdeserialfn);
1096 -- Cross-check aggsortop (if present) against pg_operator.
1097 -- We expect to find entries for bool_and, bool_or, every, max, and min.
1099 SELECT DISTINCT proname, oprname
1100 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
1101 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
1104 -- Check datatypes match
1106 SELECT a.aggfnoid::oid, o.oid
1107 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
1108 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
1109 (oprkind != 'b' OR oprresult != 'boolean'::regtype
1110 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
1112 -- Check operator is a suitable btree opfamily member
1114 SELECT a.aggfnoid::oid, o.oid
1115 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
1116 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
1117 NOT EXISTS(SELECT 1 FROM pg_amop
1118 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
1120 AND amoplefttype = o.oprleft
1121 AND amoprighttype = o.oprright);
1123 -- Check correspondence of btree strategies and names
1125 SELECT DISTINCT proname, oprname, amopstrategy
1126 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
1128 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
1130 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
1133 -- Check that there are not aggregates with the same name and different
1134 -- numbers of arguments. While not technically wrong, we have a project policy
1135 -- to avoid this because it opens the door for confusion in connection with
1136 -- ORDER BY: novices frequently put the ORDER BY in the wrong place.
1137 -- See the fate of the single-argument form of string_agg() for history.
1138 -- (Note: we don't forbid users from creating such aggregates; the policy is
1139 -- just to think twice before creating built-in aggregates like this.)
1140 -- The only aggregates that should show up here are count(x) and count(*).
1142 SELECT p1.oid::regprocedure, p2.oid::regprocedure
1143 FROM pg_proc AS p1, pg_proc AS p2
1144 WHERE p1.oid < p2.oid AND p1.proname = p2.proname AND
1145 p1.prokind = 'a' AND p2.prokind = 'a' AND
1146 array_dims(p1.proargtypes) != array_dims(p2.proargtypes)
1149 -- For the same reason, built-in aggregates with default arguments are no good.
1153 WHERE prokind = 'a' AND proargdefaults IS NOT NULL;
1155 -- For the same reason, we avoid creating built-in variadic aggregates, except
1156 -- that variadic ordered-set aggregates are OK (since they have special syntax
1157 -- that is not subject to the misplaced ORDER BY issue).
1159 SELECT p.oid, proname
1160 FROM pg_proc AS p JOIN pg_aggregate AS a ON a.aggfnoid = p.oid
1161 WHERE prokind = 'a' AND provariadic != 0 AND a.aggkind = 'n';
1164 -- **************** pg_opfamily ****************
1166 -- Look for illegal values in pg_opfamily fields
1169 FROM pg_opfamily as f
1170 WHERE f.opfmethod = 0 OR f.opfnamespace = 0;
1172 -- Look for opfamilies having no opclasses. While most validation of
1173 -- opfamilies is now handled by AM-specific amvalidate functions, that's
1174 -- driven from pg_opclass entries below, so an empty opfamily would not
1177 SELECT oid, opfname FROM pg_opfamily f
1178 WHERE NOT EXISTS (SELECT 1 FROM pg_opclass WHERE opcfamily = f.oid);
1181 -- **************** pg_opclass ****************
1183 -- Look for illegal values in pg_opclass fields
1186 FROM pg_opclass AS c1
1187 WHERE c1.opcmethod = 0 OR c1.opcnamespace = 0 OR c1.opcfamily = 0
1188 OR c1.opcintype = 0;
1190 -- opcmethod must match owning opfamily's opfmethod
1192 SELECT c1.oid, f1.oid
1193 FROM pg_opclass AS c1, pg_opfamily AS f1
1194 WHERE c1.opcfamily = f1.oid AND c1.opcmethod != f1.opfmethod;
1196 -- There should not be multiple entries in pg_opclass with opcdefault true
1197 -- and the same opcmethod/opcintype combination.
1199 SELECT c1.oid, c2.oid
1200 FROM pg_opclass AS c1, pg_opclass AS c2
1201 WHERE c1.oid != c2.oid AND
1202 c1.opcmethod = c2.opcmethod AND c1.opcintype = c2.opcintype AND
1203 c1.opcdefault AND c2.opcdefault;
1205 -- Ask access methods to validate opclasses
1206 -- (this replaces a lot of SQL-level checks that used to be done in this file)
1208 SELECT oid, opcname FROM pg_opclass WHERE NOT amvalidate(oid);
1211 -- **************** pg_am ****************
1213 -- Look for illegal values in pg_am fields
1215 SELECT a1.oid, a1.amname
1217 WHERE a1.amhandler = 0;
1219 -- Check for index amhandler functions with the wrong signature
1221 SELECT a1.oid, a1.amname, p1.oid, p1.proname
1222 FROM pg_am AS a1, pg_proc AS p1
1223 WHERE p1.oid = a1.amhandler AND a1.amtype = 'i' AND
1224 (p1.prorettype != 'index_am_handler'::regtype
1227 OR p1.proargtypes[0] != 'internal'::regtype);
1229 -- Check for table amhandler functions with the wrong signature
1231 SELECT a1.oid, a1.amname, p1.oid, p1.proname
1232 FROM pg_am AS a1, pg_proc AS p1
1233 WHERE p1.oid = a1.amhandler AND a1.amtype = 't' AND
1234 (p1.prorettype != 'table_am_handler'::regtype
1237 OR p1.proargtypes[0] != 'internal'::regtype);
1239 -- **************** pg_amop ****************
1241 -- Look for illegal values in pg_amop fields
1243 SELECT a1.amopfamily, a1.amopstrategy
1245 WHERE a1.amopfamily = 0 OR a1.amoplefttype = 0 OR a1.amoprighttype = 0
1246 OR a1.amopopr = 0 OR a1.amopmethod = 0 OR a1.amopstrategy < 1;
1248 SELECT a1.amopfamily, a1.amopstrategy
1250 WHERE NOT ((a1.amoppurpose = 's' AND a1.amopsortfamily = 0) OR
1251 (a1.amoppurpose = 'o' AND a1.amopsortfamily <> 0));
1253 -- amopmethod must match owning opfamily's opfmethod
1255 SELECT a1.oid, f1.oid
1256 FROM pg_amop AS a1, pg_opfamily AS f1
1257 WHERE a1.amopfamily = f1.oid AND a1.amopmethod != f1.opfmethod;
1259 -- Make a list of all the distinct operator names being used in particular
1260 -- strategy slots. This is a bit hokey, since the list might need to change
1261 -- in future releases, but it's an effective way of spotting mistakes such as
1262 -- swapping two operators within a family.
1264 SELECT DISTINCT amopmethod, amopstrategy, oprname
1265 FROM pg_amop a1 LEFT JOIN pg_operator o1 ON amopopr = o1.oid
1268 -- Check that all opclass search operators have selectivity estimators.
1269 -- This is not absolutely required, but it seems a reasonable thing
1270 -- to insist on for all standard datatypes.
1272 SELECT a1.amopfamily, a1.amopopr, o1.oid, o1.oprname
1273 FROM pg_amop AS a1, pg_operator AS o1
1274 WHERE a1.amopopr = o1.oid AND a1.amoppurpose = 's' AND
1275 (o1.oprrest = 0 OR o1.oprjoin = 0);
1277 -- Check that each opclass in an opfamily has associated operators, that is
1278 -- ones whose oprleft matches opcintype (possibly by coercion).
1280 SELECT c1.opcname, c1.opcfamily
1281 FROM pg_opclass AS c1
1282 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS a1
1283 WHERE a1.amopfamily = c1.opcfamily
1284 AND binary_coercible(c1.opcintype, a1.amoplefttype));
1286 -- Check that each operator listed in pg_amop has an associated opclass,
1287 -- that is one whose opcintype matches oprleft (possibly by coercion).
1288 -- Otherwise the operator is useless because it cannot be matched to an index.
1289 -- (In principle it could be useful to list such operators in multiple-datatype
1290 -- btree opfamilies, but in practice you'd expect there to be an opclass for
1291 -- every datatype the family knows about.)
1293 SELECT a1.amopfamily, a1.amopstrategy, a1.amopopr
1295 WHERE NOT EXISTS(SELECT 1 FROM pg_opclass AS c1
1296 WHERE c1.opcfamily = a1.amopfamily
1297 AND binary_coercible(c1.opcintype, a1.amoplefttype));
1299 -- Operators that are primary members of opclasses must be immutable (else
1300 -- it suggests that the index ordering isn't fixed). Operators that are
1301 -- cross-type members need only be stable, since they are just shorthands
1302 -- for index probe queries.
1304 SELECT a1.amopfamily, a1.amopopr, o1.oprname, p1.prosrc
1305 FROM pg_amop AS a1, pg_operator AS o1, pg_proc AS p1
1306 WHERE a1.amopopr = o1.oid AND o1.oprcode = p1.oid AND
1307 a1.amoplefttype = a1.amoprighttype AND
1308 p1.provolatile != 'i';
1310 SELECT a1.amopfamily, a1.amopopr, o1.oprname, p1.prosrc
1311 FROM pg_amop AS a1, pg_operator AS o1, pg_proc AS p1
1312 WHERE a1.amopopr = o1.oid AND o1.oprcode = p1.oid AND
1313 a1.amoplefttype != a1.amoprighttype AND
1314 p1.provolatile = 'v';
1317 -- **************** pg_amproc ****************
1319 -- Look for illegal values in pg_amproc fields
1321 SELECT a1.amprocfamily, a1.amprocnum
1322 FROM pg_amproc as a1
1323 WHERE a1.amprocfamily = 0 OR a1.amproclefttype = 0 OR a1.amprocrighttype = 0
1324 OR a1.amprocnum < 0 OR a1.amproc = 0;
1326 -- Support routines that are primary members of opfamilies must be immutable
1327 -- (else it suggests that the index ordering isn't fixed). But cross-type
1328 -- members need only be stable, since they are just shorthands
1329 -- for index probe queries.
1331 SELECT a1.amprocfamily, a1.amproc, p1.prosrc
1332 FROM pg_amproc AS a1, pg_proc AS p1
1333 WHERE a1.amproc = p1.oid AND
1334 a1.amproclefttype = a1.amprocrighttype AND
1335 p1.provolatile != 'i';
1337 SELECT a1.amprocfamily, a1.amproc, p1.prosrc
1338 FROM pg_amproc AS a1, pg_proc AS p1
1339 WHERE a1.amproc = p1.oid AND
1340 a1.amproclefttype != a1.amprocrighttype AND
1341 p1.provolatile = 'v';
1343 -- Almost all of the core distribution's Btree opclasses can use one of the
1344 -- two generic "equalimage" functions as their support function 4. Look for
1345 -- opclasses that don't allow deduplication unconditionally here.
1347 -- Newly added Btree opclasses don't have to support deduplication. It will
1348 -- usually be trivial to add support, though. Note that the expected output
1349 -- of this part of the test will need to be updated when a new opclass cannot
1350 -- support deduplication (by using btequalimage).
1351 SELECT amp.amproc::regproc AS proc, opf.opfname AS opfamily_name,
1352 opc.opcname AS opclass_name, opc.opcintype::regtype AS opcintype
1354 JOIN pg_opclass AS opc ON opc.opcmethod = am.oid
1355 JOIN pg_opfamily AS opf ON opc.opcfamily = opf.oid
1356 LEFT JOIN pg_amproc AS amp ON amp.amprocfamily = opf.oid AND
1357 amp.amproclefttype = opc.opcintype AND amp.amprocnum = 4
1358 WHERE am.amname = 'btree' AND
1359 amp.amproc IS DISTINCT FROM 'btequalimage'::regproc
1362 -- **************** pg_index ****************
1364 -- Look for illegal values in pg_index fields.
1366 SELECT indexrelid, indrelid
1368 WHERE indexrelid = 0 OR indrelid = 0 OR
1369 indnatts <= 0 OR indnatts > 32;
1371 -- oidvector and int2vector fields should be of length indnatts.
1373 SELECT indexrelid, indrelid
1375 WHERE array_lower(indkey, 1) != 0 OR array_upper(indkey, 1) != indnatts-1 OR
1376 array_lower(indclass, 1) != 0 OR array_upper(indclass, 1) != indnatts-1 OR
1377 array_lower(indcollation, 1) != 0 OR array_upper(indcollation, 1) != indnatts-1 OR
1378 array_lower(indoption, 1) != 0 OR array_upper(indoption, 1) != indnatts-1;
1380 -- Check that opclasses and collations match the underlying columns.
1381 -- (As written, this test ignores expression indexes.)
1383 SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname
1384 FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey,
1385 unnest(indclass) as iclass, unnest(indcollation) as icoll
1389 WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND
1390 (NOT binary_coercible(atttypid, opcintype) OR icoll != attcollation);
1392 -- For system catalogs, be even tighter: nearly all indexes should be
1393 -- exact type matches not binary-coercible matches. At this writing
1394 -- the only exception is an OID index on a regproc column.
1396 SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname
1397 FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey,
1398 unnest(indclass) as iclass, unnest(indcollation) as icoll
1400 WHERE indrelid < 16384) ss,
1403 WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND
1404 (opcintype != atttypid OR icoll != attcollation)
1407 -- Check for system catalogs with collation-sensitive ordering. This is not
1408 -- a representational error in pg_index, but simply wrong catalog design.
1409 -- It's bad because we expect to be able to clone template0 and assign the
1410 -- copy a different database collation. It would especially not work for
1413 SELECT relname, attname, attcollation
1414 FROM pg_class c, pg_attribute a
1415 WHERE c.oid = attrelid AND c.oid < 16384 AND
1416 c.relkind != 'v' AND -- we don't care about columns in views
1417 attcollation != 0 AND
1418 attcollation != (SELECT oid FROM pg_collation WHERE collname = 'C');
1420 -- Double-check that collation-sensitive indexes have "C" collation, too.
1422 SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll
1423 FROM (SELECT indexrelid, indrelid,
1424 unnest(indclass) as iclass, unnest(indcollation) as icoll
1426 WHERE indrelid < 16384) ss
1427 WHERE icoll != 0 AND
1428 icoll != (SELECT oid FROM pg_collation WHERE collname = 'C');