plpgsql: pure parser and reentrant scanner
[pgsql.git] / src / test / regress / sql / opr_sanity.sql
blob2fb3a85287816f00fca60038150426083598fa38
1 --
2 -- OPR_SANITY
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.
6 --
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
26 FROM pg_proc as p1
27 WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
28        p1.pronargs < 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
34        procost <= 0 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
42 FROM pg_proc as p1
43 WHERE prosrc IS NULL;
44 SELECT p1.oid, p1.proname
45 FROM pg_proc as p1
46 WHERE (prosrc = '' OR prosrc = '-') AND prosqlbody IS NULL;
48 -- proretset should only be set for normal functions
49 SELECT p1.oid, p1.proname
50 FROM pg_proc AS p1
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
56 FROM pg_proc AS p1
57 WHERE prosecdef
58 ORDER BY 1;
60 -- pronargdefaults should be 0 iff proargdefaults is null
61 SELECT p1.oid, p1.proname
62 FROM pg_proc AS p1
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
67 FROM pg_proc as p1
68 WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-');
70 SELECT p1.oid, p1.proname
71 FROM pg_proc as p1
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
118 -- functions.
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)
131 ORDER BY 1, 2;
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])
144 ORDER BY 1, 2;
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])
157 ORDER BY 1, 2;
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])
166 ORDER BY 1, 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])
175 ORDER BY 1, 2;
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])
184 ORDER BY 1, 2;
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])
193 ORDER BY 1, 2;
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])
202 ORDER BY 1, 2;
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])
211 ORDER BY 1, 2;
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
220 FROM pg_proc as p1
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
230 FROM pg_proc as p1
231 WHERE p1.prorettype IN
232     ('anyelement'::regtype, 'anyarray'::regtype, 'anynonarray'::regtype,
233      'anyenum'::regtype)
234   AND NOT
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))
241 ORDER BY 2;
243 -- anyrange and anymultirange are tighter than the rest, can only resolve
244 -- from each other
246 SELECT p1.oid, p1.proname
247 FROM pg_proc as p1
248 WHERE p1.prorettype IN ('anyrange'::regtype, 'anymultirange'::regtype)
249   AND NOT
250     ('anyrange'::regtype = ANY (p1.proargtypes) OR
251       'anymultirange'::regtype = ANY (p1.proargtypes))
252 ORDER BY 2;
254 -- similarly for the anycompatible family
256 SELECT p1.oid, p1.proname
257 FROM pg_proc as p1
258 WHERE p1.prorettype IN
259     ('anycompatible'::regtype, 'anycompatiblearray'::regtype,
260      'anycompatiblenonarray'::regtype)
261   AND NOT
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))
266 ORDER BY 2;
268 SELECT p1.oid, p1.proname
269 FROM pg_proc as p1
270 WHERE p1.prorettype = 'anycompatiblerange'::regtype
271   AND NOT
272      'anycompatiblerange'::regtype = ANY (p1.proargtypes)
273 ORDER BY 2;
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
283 -- any shell types.
285 SELECT p1.oid, p1.proname
286 FROM pg_proc as p1
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
291 ORDER BY 1;
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
299 FROM pg_proc as p1
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
304 ORDER BY 1;
306 -- Check for length inconsistencies between the various argument-info arrays.
308 SELECT p1.oid, p1.proname
309 FROM pg_proc as p1
310 WHERE proallargtypes IS NOT NULL AND
311     array_length(proallargtypes,1) < array_length(proargtypes,1);
313 SELECT p1.oid, p1.proname
314 FROM pg_proc as p1
315 WHERE proargmodes IS NOT NULL AND
316     array_length(proargmodes,1) < array_length(proargtypes,1);
318 SELECT p1.oid, p1.proname
319 FROM pg_proc as p1
320 WHERE proargnames IS NOT NULL AND
321     array_length(proargnames,1) < array_length(proargtypes,1);
323 SELECT p1.oid, p1.proname
324 FROM pg_proc as p1
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
329 FROM pg_proc as p1
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
334 FROM pg_proc as p1
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
340 FROM pg_proc as p1
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[]
354 FROM pg_proc
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
360         ELSE (SELECT t.oid
361                   FROM pg_type t
362                   WHERE t.typarray = proargtypes[array_length(proargtypes, 1)-1])
363         END  != provariadic;
365 -- Check that all and only those functions with a variadic type have
366 -- a variadic argument.
367 SELECT oid::regprocedure, proargmodes, provariadic
368 FROM pg_proc
369 WHERE (proargmodes IS NOT NULL AND 'v' = any(proargmodes))
370     IS DISTINCT FROM
371     (provariadic != 0);
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
394 \a\t
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
400 ORDER BY 1;
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
407 ORDER BY 1;
409 -- restore normal output mode
410 \a\t
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
416 -- each listed name.
417 select proname, oid from pg_catalog.pg_proc
418 where proname in (
419   'lo_open',
420   'lo_close',
421   'lo_creat',
422   'lo_create',
423   'lo_unlink',
424   'lo_lseek',
425   'lo_lseek64',
426   'lo_tell',
427   'lo_tell64',
428   'lo_truncate',
429   'lo_truncate64',
430   'loread',
431   'lowrite')
432 and pronamespace = (select oid from pg_catalog.pg_namespace
433                     where nspname = 'pg_catalog')
434 order by 1;
436 -- Check that all immutable functions are marked parallel safe
437 SELECT p1.oid, p1.proname
438 FROM pg_proc AS p1
439 WHERE provolatile = 'i' AND proparallel = 'u';
442 -- **************** pg_cast ****************
444 -- Catch bogus values in pg_cast columns (other than cases detected by
445 -- oidjoins test).
447 SELECT *
448 FROM pg_cast c
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
455 SELECT *
456 FROM pg_cast c
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.
464 SELECT *
465 FROM pg_cast c
466 WHERE castsource = casttarget AND castfunc = 0;
468 SELECT c.*
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.
480 SELECT c.*
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));
489 SELECT c.*
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
513 FROM pg_cast c
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
537      p.pronargs != 6 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
557 WHERE condefault AND
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
577     o1.oprright = 0;
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
620      o1.oid = o2.oid);
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
629 ORDER BY 1, 2;
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
636 ORDER BY 1, 2;
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
640 -- operator).
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
655 -- opfamilies.
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);
664 -- And the converse.
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')
670   AND amopstrategy = 3
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);
682 -- And the converse.
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
696     o1.oprkind = 'b' AND
697     (p1.pronargs != 2
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
705     o1.oprkind = 'l' AND
706     (p1.pronargs != 1
707      OR NOT binary_coercible(p1.prorettype, o1.oprresult)
708      OR NOT binary_coercible(o1.oprright, p1.proargtypes[0])
709      OR o1.oprleft != 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
730      p2.pronargs != 4 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
748      p2.pronargs != 5 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.
769 WITH funcdescs AS (
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)
779   WHERE o.oid <= 9999
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.
790 WITH funcdescs AS (
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)
800   WHERE o.oid <= 9999
802 SELECT p_oid, proname, prodesc FROM funcdescs
803   WHERE prodesc IS DISTINCT FROM expecteddesc
804     AND oprdesc NOT LIKE 'deprecated%'
805 ORDER BY 1;
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
832     ap.amprocnum = 1 AND
833     (pp.provolatile != po.provolatile OR
834      pp.proleakproof != po.proleakproof)
835 ORDER BY 1;
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.
861 SELECT oid, proname
862 FROM pg_proc as p
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
878     (ptr.proretset
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
893      OR (p.pronargs > 4)
894     );
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
902     (pfn.proretset OR
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)
915     );
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
954     (ptr.proretset
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
967      OR (p.pronargs > 3)
968     );
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
975     (ptr.proretset
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
988      OR (p.pronargs > 3)
989     );
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
997     (pfn.proretset OR
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)
1010     );
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
1038     (p.pronargs != 2 OR
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
1055 FROM pg_aggregate
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
1069      NOT p.proisstrict);
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
1081      NOT p.proisstrict);
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
1089 FROM
1090     pg_aggregate a, pg_aggregate b
1091 WHERE
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
1102 ORDER BY 1, 2;
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')
1119                      AND amopopr = o.oid
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,
1127      pg_amop as ao
1128 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
1129     amopopr = o.oid AND
1130     amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
1131 ORDER BY 1, 2;
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)
1147 ORDER BY 1;
1149 -- For the same reason, built-in aggregates with default arguments are no good.
1151 SELECT oid, proname
1152 FROM pg_proc AS p
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
1168 SELECT f.oid
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
1175 -- get noticed.
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
1185 SELECT c1.oid
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
1216 FROM pg_am AS a1
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
1225      OR p1.proretset
1226      OR p1.pronargs != 1
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
1235      OR p1.proretset
1236      OR p1.pronargs != 1
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
1244 FROM pg_amop as a1
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
1249 FROM pg_amop as a1
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
1266 ORDER BY 1, 2, 3;
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
1294 FROM pg_amop AS a1
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
1353 FROM pg_am AS am
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
1360 ORDER BY 1, 2, 3;
1362 -- **************** pg_index ****************
1364 -- Look for illegal values in pg_index fields.
1366 SELECT indexrelid, indrelid
1367 FROM pg_index
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
1374 FROM pg_index
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
1386       FROM pg_index) ss,
1387       pg_attribute a,
1388       pg_opclass opc
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
1399       FROM pg_index
1400       WHERE indrelid < 16384) ss,
1401       pg_attribute a,
1402       pg_opclass opc
1403 WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND
1404       (opcintype != atttypid OR icoll != attcollation)
1405 ORDER BY 1;
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
1411 -- shared catalogs.
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
1425       FROM pg_index
1426       WHERE indrelid < 16384) ss
1427 WHERE icoll != 0 AND
1428     icoll != (SELECT oid FROM pg_collation WHERE collname = 'C');