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...
19 -- Helper functions to deal with cases where binary-coercible matches are
21 -- This should match IsBinaryCoercible() in parse_coerce.c.
22 create function binary_coercible(oid, oid) returns bool as $$
24 EXISTS(select 1 from pg_catalog.pg_cast where
25 castsource = $1 and casttarget = $2 and
26 castmethod = 'b' and castcontext = 'i') OR
27 ($2 = 'pg_catalog.any'::pg_catalog.regtype) OR
28 ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
29 EXISTS(select 1 from pg_catalog.pg_type where
30 oid = $1 and typelem != 0 and typlen = -1)) OR
31 ($2 = 'pg_catalog.anyrange'::pg_catalog.regtype AND
32 (select typtype from pg_catalog.pg_type where oid = $1) = 'r')
33 $$ language sql strict stable;
34 -- This one ignores castcontext, so it considers only physical equivalence
35 -- and not whether the coercion can be invoked implicitly.
36 create function physically_coercible(oid, oid) returns bool as $$
38 EXISTS(select 1 from pg_catalog.pg_cast where
39 castsource = $1 and casttarget = $2 and
41 ($2 = 'pg_catalog.any'::pg_catalog.regtype) OR
42 ($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
43 EXISTS(select 1 from pg_catalog.pg_type where
44 oid = $1 and typelem != 0 and typlen = -1)) OR
45 ($2 = 'pg_catalog.anyrange'::pg_catalog.regtype AND
46 (select typtype from pg_catalog.pg_type where oid = $1) = 'r')
47 $$ language sql strict stable;
48 -- **************** pg_proc ****************
49 -- Look for illegal values in pg_proc fields.
50 SELECT p1.oid, p1.proname
52 WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
54 p1.pronargdefaults < 0 OR
55 p1.pronargdefaults > p1.pronargs OR
56 array_lower(p1.proargtypes, 1) != 0 OR
57 array_upper(p1.proargtypes, 1) != p1.pronargs-1 OR
58 0::oid = ANY (p1.proargtypes) OR
60 CASE WHEN proretset THEN prorows <= 0 ELSE prorows != 0 END OR
61 provolatile NOT IN ('i', 's', 'v') OR
62 proparallel NOT IN ('s', 'r', 'u');
67 -- prosrc should never be null or empty
68 SELECT p1.oid, p1.proname
70 WHERE prosrc IS NULL OR prosrc = '' OR prosrc = '-';
75 -- proiswindow shouldn't be set together with proisagg or proretset
76 SELECT p1.oid, p1.proname
78 WHERE proiswindow AND (proisagg OR proretset);
83 -- pronargdefaults should be 0 iff proargdefaults is null
84 SELECT p1.oid, p1.proname
86 WHERE (pronargdefaults <> 0) != (proargdefaults IS NOT NULL);
91 -- probin should be non-empty for C functions, null everywhere else
92 SELECT p1.oid, p1.proname
94 WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-');
99 SELECT p1.oid, p1.proname
101 WHERE prolang != 13 AND probin IS NOT NULL;
106 -- Look for conflicting proc definitions (same names and input datatypes).
107 -- (This test should be dead code now that we have the unique index
108 -- pg_proc_proname_args_nsp_index, but I'll leave it in anyway.)
109 SELECT p1.oid, p1.proname, p2.oid, p2.proname
110 FROM pg_proc AS p1, pg_proc AS p2
111 WHERE p1.oid != p2.oid AND
112 p1.proname = p2.proname AND
113 p1.pronargs = p2.pronargs AND
114 p1.proargtypes = p2.proargtypes;
115 oid | proname | oid | proname
116 -----+---------+-----+---------
119 -- Considering only built-in procs (prolang = 12), look for multiple uses
120 -- of the same internal function (ie, matching prosrc fields). It's OK to
121 -- have several entries with different pronames for the same internal function,
122 -- but conflicts in the number of arguments and other critical items should
123 -- be complained of. (We don't check data types here; see next query.)
124 -- Note: ignore aggregate functions here, since they all point to the same
125 -- dummy built-in function.
126 SELECT p1.oid, p1.proname, p2.oid, p2.proname
127 FROM pg_proc AS p1, pg_proc AS p2
128 WHERE p1.oid < p2.oid AND
129 p1.prosrc = p2.prosrc AND
130 p1.prolang = 12 AND p2.prolang = 12 AND
131 (p1.proisagg = false OR p2.proisagg = false) AND
132 (p1.prolang != p2.prolang OR
133 p1.proisagg != p2.proisagg OR
134 p1.prosecdef != p2.prosecdef OR
135 p1.proleakproof != p2.proleakproof OR
136 p1.proisstrict != p2.proisstrict OR
137 p1.proretset != p2.proretset OR
138 p1.provolatile != p2.provolatile OR
139 p1.pronargs != p2.pronargs);
140 oid | proname | oid | proname
141 -----+---------+-----+---------
144 -- Look for uses of different type OIDs in the argument/result type fields
145 -- for different aliases of the same built-in function.
146 -- This indicates that the types are being presumed to be binary-equivalent,
147 -- or that the built-in function is prepared to deal with different types.
148 -- That's not wrong, necessarily, but we make lists of all the types being
149 -- so treated. Note that the expected output of this part of the test will
150 -- need to be modified whenever new pairs of types are made binary-equivalent,
151 -- or when new polymorphic built-in functions are added!
152 -- Note: ignore aggregate functions here, since they all point to the same
153 -- dummy built-in function. Likewise, ignore range constructor functions.
154 SELECT DISTINCT p1.prorettype, p2.prorettype
155 FROM pg_proc AS p1, pg_proc AS p2
156 WHERE p1.oid != p2.oid AND
157 p1.prosrc = p2.prosrc AND
158 p1.prolang = 12 AND p2.prolang = 12 AND
159 NOT p1.proisagg AND NOT p2.proisagg AND
160 p1.prosrc NOT LIKE E'range\\_constructor_' AND
161 p2.prosrc NOT LIKE E'range\\_constructor_' AND
162 (p1.prorettype < p2.prorettype)
164 prorettype | prorettype
165 ------------+------------
170 SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
171 FROM pg_proc AS p1, pg_proc AS p2
172 WHERE p1.oid != p2.oid AND
173 p1.prosrc = p2.prosrc AND
174 p1.prolang = 12 AND p2.prolang = 12 AND
175 NOT p1.proisagg AND NOT p2.proisagg AND
176 p1.prosrc NOT LIKE E'range\\_constructor_' AND
177 p2.prosrc NOT LIKE E'range\\_constructor_' AND
178 (p1.proargtypes[0] < p2.proargtypes[0])
180 proargtypes | proargtypes
181 -------------+-------------
188 SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
189 FROM pg_proc AS p1, pg_proc AS p2
190 WHERE p1.oid != p2.oid AND
191 p1.prosrc = p2.prosrc AND
192 p1.prolang = 12 AND p2.prolang = 12 AND
193 NOT p1.proisagg AND NOT p2.proisagg AND
194 p1.prosrc NOT LIKE E'range\\_constructor_' AND
195 p2.prosrc NOT LIKE E'range\\_constructor_' AND
196 (p1.proargtypes[1] < p2.proargtypes[1])
198 proargtypes | proargtypes
199 -------------+-------------
205 SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
206 FROM pg_proc AS p1, pg_proc AS p2
207 WHERE p1.oid != p2.oid AND
208 p1.prosrc = p2.prosrc AND
209 p1.prolang = 12 AND p2.prolang = 12 AND
210 NOT p1.proisagg AND NOT p2.proisagg AND
211 (p1.proargtypes[2] < p2.proargtypes[2])
213 proargtypes | proargtypes
214 -------------+-------------
218 SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
219 FROM pg_proc AS p1, pg_proc AS p2
220 WHERE p1.oid != p2.oid AND
221 p1.prosrc = p2.prosrc AND
222 p1.prolang = 12 AND p2.prolang = 12 AND
223 NOT p1.proisagg AND NOT p2.proisagg AND
224 (p1.proargtypes[3] < p2.proargtypes[3])
226 proargtypes | proargtypes
227 -------------+-------------
231 SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
232 FROM pg_proc AS p1, pg_proc AS p2
233 WHERE p1.oid != p2.oid AND
234 p1.prosrc = p2.prosrc AND
235 p1.prolang = 12 AND p2.prolang = 12 AND
236 NOT p1.proisagg AND NOT p2.proisagg AND
237 (p1.proargtypes[4] < p2.proargtypes[4])
239 proargtypes | proargtypes
240 -------------+-------------
243 SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
244 FROM pg_proc AS p1, pg_proc AS p2
245 WHERE p1.oid != p2.oid AND
246 p1.prosrc = p2.prosrc AND
247 p1.prolang = 12 AND p2.prolang = 12 AND
248 NOT p1.proisagg AND NOT p2.proisagg AND
249 (p1.proargtypes[5] < p2.proargtypes[5])
251 proargtypes | proargtypes
252 -------------+-------------
255 SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
256 FROM pg_proc AS p1, pg_proc AS p2
257 WHERE p1.oid != p2.oid AND
258 p1.prosrc = p2.prosrc AND
259 p1.prolang = 12 AND p2.prolang = 12 AND
260 NOT p1.proisagg AND NOT p2.proisagg AND
261 (p1.proargtypes[6] < p2.proargtypes[6])
263 proargtypes | proargtypes
264 -------------+-------------
267 SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
268 FROM pg_proc AS p1, pg_proc AS p2
269 WHERE p1.oid != p2.oid AND
270 p1.prosrc = p2.prosrc AND
271 p1.prolang = 12 AND p2.prolang = 12 AND
272 NOT p1.proisagg AND NOT p2.proisagg AND
273 (p1.proargtypes[7] < p2.proargtypes[7])
275 proargtypes | proargtypes
276 -------------+-------------
279 -- Look for functions that return type "internal" and do not have any
280 -- "internal" argument. Such a function would be a security hole since
281 -- it might be used to call an internal function from an SQL command.
282 -- As of 7.3 this query should find only internal_in.
283 SELECT p1.oid, p1.proname
285 WHERE p1.prorettype = 'internal'::regtype AND NOT
286 'internal'::regtype = ANY (p1.proargtypes);
292 -- Look for functions that return a polymorphic type and do not have any
293 -- polymorphic argument. Calls of such functions would be unresolvable
294 -- at parse time. As of 9.6 this query should find only some input functions
295 -- and GiST support functions associated with these pseudotypes.
296 SELECT p1.oid, p1.proname
298 WHERE p1.prorettype IN
299 ('anyelement'::regtype, 'anyarray'::regtype, 'anynonarray'::regtype,
300 'anyenum'::regtype, 'anyrange'::regtype)
302 ('anyelement'::regtype = ANY (p1.proargtypes) OR
303 'anyarray'::regtype = ANY (p1.proargtypes) OR
304 'anynonarray'::regtype = ANY (p1.proargtypes) OR
305 'anyenum'::regtype = ANY (p1.proargtypes) OR
306 'anyrange'::regtype = ANY (p1.proargtypes))
309 ------+------------------
314 2777 | anynonarray_in
320 3876 | range_gist_union
325 -- Look for functions that accept cstring and are neither datatype input
326 -- functions nor encoding conversion functions. It's almost never a good
327 -- idea to use cstring input for a function meant to be called from SQL;
328 -- text should be used instead, because cstring lacks suitable casts.
329 -- As of 9.6 this query should find only cstring_out and cstring_send.
330 -- However, we must manually exclude shell_in, which might or might not be
331 -- rejected by the EXISTS clause depending on whether there are currently
333 SELECT p1.oid, p1.proname
335 WHERE 'cstring'::regtype = ANY (p1.proargtypes)
336 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typinput = p1.oid)
337 AND NOT EXISTS(SELECT 1 FROM pg_conversion WHERE conproc = p1.oid)
338 AND p1.oid != 'shell_in(cstring)'::regprocedure
341 ------+--------------
346 -- Likewise, look for functions that return cstring and aren't datatype output
347 -- functions nor typmod output functions.
348 -- As of 9.6 this query should find only cstring_in and cstring_recv.
349 -- However, we must manually exclude shell_out.
350 SELECT p1.oid, p1.proname
352 WHERE p1.prorettype = 'cstring'::regtype
353 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typoutput = p1.oid)
354 AND NOT EXISTS(SELECT 1 FROM pg_type WHERE typmodout = p1.oid)
355 AND p1.oid != 'shell_out(opaque)'::regprocedure
358 ------+--------------
363 -- Check for length inconsistencies between the various argument-info arrays.
364 SELECT p1.oid, p1.proname
366 WHERE proallargtypes IS NOT NULL AND
367 array_length(proallargtypes,1) < array_length(proargtypes,1);
372 SELECT p1.oid, p1.proname
374 WHERE proargmodes IS NOT NULL AND
375 array_length(proargmodes,1) < array_length(proargtypes,1);
380 SELECT p1.oid, p1.proname
382 WHERE proargnames IS NOT NULL AND
383 array_length(proargnames,1) < array_length(proargtypes,1);
388 SELECT p1.oid, p1.proname
390 WHERE proallargtypes IS NOT NULL AND proargmodes IS NOT NULL AND
391 array_length(proallargtypes,1) <> array_length(proargmodes,1);
396 SELECT p1.oid, p1.proname
398 WHERE proallargtypes IS NOT NULL AND proargnames IS NOT NULL AND
399 array_length(proallargtypes,1) <> array_length(proargnames,1);
404 SELECT p1.oid, p1.proname
406 WHERE proargmodes IS NOT NULL AND proargnames IS NOT NULL AND
407 array_length(proargmodes,1) <> array_length(proargnames,1);
412 -- Check that proallargtypes matches proargtypes
413 SELECT p1.oid, p1.proname, p1.proargtypes, p1.proallargtypes, p1.proargmodes
415 WHERE proallargtypes IS NOT NULL AND
416 ARRAY(SELECT unnest(proargtypes)) <>
417 ARRAY(SELECT proallargtypes[i]
418 FROM generate_series(1, array_length(proallargtypes, 1)) g(i)
419 WHERE proargmodes IS NULL OR proargmodes[i] IN ('i', 'b', 'v'));
420 oid | proname | proargtypes | proallargtypes | proargmodes
421 -----+---------+-------------+----------------+-------------
424 -- Check for protransform functions with the wrong signature
425 SELECT p1.oid, p1.proname, p2.oid, p2.proname
426 FROM pg_proc AS p1, pg_proc AS p2
427 WHERE p2.oid = p1.protransform AND
428 (p2.prorettype != 'internal'::regtype OR p2.proretset OR p2.pronargs != 1
429 OR p2.proargtypes[0] != 'internal'::regtype);
430 oid | proname | oid | proname
431 -----+---------+-----+---------
434 -- Insist that all built-in pg_proc entries have descriptions
435 SELECT p1.oid, p1.proname
436 FROM pg_proc as p1 LEFT JOIN pg_description as d
437 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0
438 WHERE d.classoid IS NULL AND p1.oid <= 9999;
443 -- List of built-in leakproof functions
445 -- Leakproof functions should only be added after carefully
446 -- scrutinizing all possibly executed codepaths for possible
447 -- information leaks. Don't add functions here unless you know what a
448 -- leakproof function is. If unsure, don't mark it as such.
449 -- temporarily disable fancy output, so catalog changes create less diff noise
451 SELECT p1.oid::regprocedure
452 FROM pg_proc p1 JOIN pg_namespace pn
453 ON pronamespace = pn.oid
454 WHERE nspname = 'pg_catalog' AND proleakproof
456 boollt(boolean,boolean)
457 boolgt(boolean,boolean)
458 booleq(boolean,boolean)
459 chareq("char","char")
461 int2eq(smallint,smallint)
462 int2lt(smallint,smallint)
463 int4eq(integer,integer)
464 int4lt(integer,integer)
468 charne("char","char")
469 charle("char","char")
470 chargt("char","char")
471 charge("char","char")
472 boolne(boolean,boolean)
473 int4ne(integer,integer)
474 int2ne(smallint,smallint)
475 int2gt(smallint,smallint)
476 int4gt(integer,integer)
477 int2le(smallint,smallint)
478 int4le(integer,integer)
479 int4ge(integer,integer)
480 int2ge(smallint,smallint)
482 int24eq(smallint,integer)
483 int42eq(integer,smallint)
484 int24lt(smallint,integer)
485 int42lt(integer,smallint)
486 int24gt(smallint,integer)
487 int42gt(integer,smallint)
488 int24ne(smallint,integer)
489 int42ne(integer,smallint)
490 int24le(smallint,integer)
491 int42le(integer,smallint)
492 int24ge(smallint,integer)
493 int42ge(integer,smallint)
496 abstimeeq(abstime,abstime)
497 abstimene(abstime,abstime)
498 abstimelt(abstime,abstime)
499 abstimegt(abstime,abstime)
500 abstimele(abstime,abstime)
501 abstimege(abstime,abstime)
502 reltimeeq(reltime,reltime)
503 reltimene(reltime,reltime)
504 reltimelt(reltime,reltime)
505 reltimegt(reltime,reltime)
506 reltimele(reltime,reltime)
507 reltimege(reltime,reltime)
508 tintervalleneq(tinterval,reltime)
509 tintervallenne(tinterval,reltime)
510 tintervallenlt(tinterval,reltime)
511 tintervallengt(tinterval,reltime)
512 tintervallenle(tinterval,reltime)
513 tintervallenge(tinterval,reltime)
520 float8eq(double precision,double precision)
521 float8ne(double precision,double precision)
522 float8lt(double precision,double precision)
523 float8le(double precision,double precision)
524 float8gt(double precision,double precision)
525 float8ge(double precision,double precision)
526 float48eq(real,double precision)
527 float48ne(real,double precision)
528 float48lt(real,double precision)
529 float48le(real,double precision)
530 float48gt(real,double precision)
531 float48ge(real,double precision)
532 float84eq(double precision,real)
533 float84ne(double precision,real)
534 float84lt(double precision,real)
535 float84le(double precision,real)
536 float84gt(double precision,real)
537 float84ge(double precision,real)
538 int8eq(bigint,bigint)
539 int8ne(bigint,bigint)
540 int8lt(bigint,bigint)
541 int8gt(bigint,bigint)
542 int8le(bigint,bigint)
543 int8ge(bigint,bigint)
544 int84eq(bigint,integer)
545 int84ne(bigint,integer)
546 int84lt(bigint,integer)
547 int84gt(bigint,integer)
548 int84le(bigint,integer)
549 int84ge(bigint,integer)
557 tintervaleq(tinterval,tinterval)
558 tintervalne(tinterval,tinterval)
559 tintervallt(tinterval,tinterval)
560 tintervalgt(tinterval,tinterval)
561 tintervalle(tinterval,tinterval)
562 tintervalge(tinterval,tinterval)
563 macaddr_eq(macaddr,macaddr)
564 macaddr_lt(macaddr,macaddr)
565 macaddr_le(macaddr,macaddr)
566 macaddr_gt(macaddr,macaddr)
567 macaddr_ge(macaddr,macaddr)
568 macaddr_ne(macaddr,macaddr)
569 int48eq(integer,bigint)
570 int48ne(integer,bigint)
571 int48lt(integer,bigint)
572 int48gt(integer,bigint)
573 int48le(integer,bigint)
574 int48ge(integer,bigint)
581 network_eq(inet,inet)
582 network_lt(inet,inet)
583 network_le(inet,inet)
584 network_gt(inet,inet)
585 network_ge(inet,inet)
586 network_ne(inet,inet)
588 bpchareq(character,character)
589 bpcharne(character,character)
596 time_lt(time without time zone,time without time zone)
597 time_le(time without time zone,time without time zone)
598 time_gt(time without time zone,time without time zone)
599 time_ge(time without time zone,time without time zone)
600 time_ne(time without time zone,time without time zone)
601 time_eq(time without time zone,time without time zone)
602 timestamptz_eq(timestamp with time zone,timestamp with time zone)
603 timestamptz_ne(timestamp with time zone,timestamp with time zone)
604 timestamptz_lt(timestamp with time zone,timestamp with time zone)
605 timestamptz_le(timestamp with time zone,timestamp with time zone)
606 timestamptz_ge(timestamp with time zone,timestamp with time zone)
607 timestamptz_gt(timestamp with time zone,timestamp with time zone)
608 interval_eq(interval,interval)
609 interval_ne(interval,interval)
610 interval_lt(interval,interval)
611 interval_le(interval,interval)
612 interval_ge(interval,interval)
613 interval_gt(interval,interval)
614 charlt("char","char")
617 xideqint4(xid,integer)
618 timetz_eq(time with time zone,time with time zone)
619 timetz_ne(time with time zone,time with time zone)
620 timetz_lt(time with time zone,time with time zone)
621 timetz_le(time with time zone,time with time zone)
622 timetz_ge(time with time zone,time with time zone)
623 timetz_gt(time with time zone,time with time zone)
624 circle_eq(circle,circle)
625 circle_ne(circle,circle)
626 circle_lt(circle,circle)
627 circle_gt(circle,circle)
628 circle_le(circle,circle)
629 circle_ge(circle,circle)
643 varbiteq(bit varying,bit varying)
644 varbitne(bit varying,bit varying)
645 varbitge(bit varying,bit varying)
646 varbitgt(bit varying,bit varying)
647 varbitle(bit varying,bit varying)
648 varbitlt(bit varying,bit varying)
649 boolle(boolean,boolean)
650 boolge(boolean,boolean)
651 int28eq(smallint,bigint)
652 int28ne(smallint,bigint)
653 int28lt(smallint,bigint)
654 int28gt(smallint,bigint)
655 int28le(smallint,bigint)
656 int28ge(smallint,bigint)
657 int82eq(bigint,smallint)
658 int82ne(bigint,smallint)
659 int82lt(bigint,smallint)
660 int82gt(bigint,smallint)
661 int82le(bigint,smallint)
662 int82ge(bigint,smallint)
669 timestamp_eq(timestamp without time zone,timestamp without time zone)
670 timestamp_ne(timestamp without time zone,timestamp without time zone)
671 timestamp_lt(timestamp without time zone,timestamp without time zone)
672 timestamp_le(timestamp without time zone,timestamp without time zone)
673 timestamp_ge(timestamp without time zone,timestamp without time zone)
674 timestamp_gt(timestamp without time zone,timestamp without time zone)
686 xidneqint4(xid,integer)
687 -- restore normal output mode
689 -- List of functions used by libpq's fe-lobj.c
691 -- If the output of this query changes, you probably broke libpq.
692 -- lo_initialize() assumes that there will be at most one match for
694 select proname, oid from pg_catalog.pg_proc
709 and pronamespace = (select oid from pg_catalog.pg_namespace
710 where nspname = 'pg_catalog')
713 ---------------+------
729 -- **************** pg_cast ****************
730 -- Catch bogus values in pg_cast columns (other than cases detected by
734 WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i')
735 OR castmethod NOT IN ('f', 'b' ,'i');
736 castsource | casttarget | castfunc | castcontext | castmethod
737 ------------+------------+----------+-------------+------------
740 -- Check that castfunc is nonzero only for cast methods that need a function,
741 -- and zero otherwise
744 WHERE (castmethod = 'f' AND castfunc = 0)
745 OR (castmethod IN ('b', 'i') AND castfunc <> 0);
746 castsource | casttarget | castfunc | castcontext | castmethod
747 ------------+------------+----------+-------------+------------
750 -- Look for casts to/from the same type that aren't length coercion functions.
751 -- (We assume they are length coercions if they take multiple arguments.)
752 -- Such entries are not necessarily harmful, but they are useless.
755 WHERE castsource = casttarget AND castfunc = 0;
756 castsource | casttarget | castfunc | castcontext | castmethod
757 ------------+------------+----------+-------------+------------
761 FROM pg_cast c, pg_proc p
762 WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
763 castsource | casttarget | castfunc | castcontext | castmethod
764 ------------+------------+----------+-------------+------------
767 -- Look for cast functions that don't have the right signature. The
768 -- argument and result types in pg_proc must be the same as, or binary
769 -- compatible with, what it says in pg_cast.
770 -- As a special case, we allow casts from CHAR(n) that use functions
771 -- declared to take TEXT. This does not pass the binary-coercibility test
772 -- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
773 -- are the same, so long as the function is one that ignores trailing blanks.
775 FROM pg_cast c, pg_proc p
776 WHERE c.castfunc = p.oid AND
777 (p.pronargs < 1 OR p.pronargs > 3
778 OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
779 OR (c.castsource = 'character'::regtype AND
780 p.proargtypes[0] = 'text'::regtype))
781 OR NOT binary_coercible(p.prorettype, c.casttarget));
782 castsource | casttarget | castfunc | castcontext | castmethod
783 ------------+------------+----------+-------------+------------
787 FROM pg_cast c, pg_proc p
788 WHERE c.castfunc = p.oid AND
789 ((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
790 (p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
791 castsource | casttarget | castfunc | castcontext | castmethod
792 ------------+------------+----------+-------------+------------
795 -- Look for binary compatible casts that do not have the reverse
796 -- direction registered as well, or where the reverse direction is not
797 -- also binary compatible. This is legal, but usually not intended.
798 -- As of 7.4, this finds the casts from text and varchar to bpchar, because
799 -- those are binary-compatible while the reverse way goes through rtrim().
800 -- As of 8.2, this finds the cast from cidr to inet, because that is a
801 -- trivial binary coercion while the other way goes through inet_to_cidr().
802 -- As of 8.3, this finds the casts from xml to text, varchar, and bpchar,
803 -- because those are binary-compatible while the reverse goes through
804 -- texttoxml(), which does an XML syntax check.
805 -- As of 9.1, this finds the cast from pg_node_tree to text, which we
806 -- intentionally do not provide a reverse pathway for.
807 SELECT castsource::regtype, casttarget::regtype, castfunc, castcontext
809 WHERE c.castmethod = 'b' AND
810 NOT EXISTS (SELECT 1 FROM pg_cast k
811 WHERE k.castmethod = 'b' AND
812 k.castsource = c.casttarget AND
813 k.casttarget = c.castsource);
814 castsource | casttarget | castfunc | castcontext
815 -------------------+-------------------+----------+-------------
816 text | character | 0 | i
817 character varying | character | 0 | i
818 pg_node_tree | text | 0 | i
821 xml | character varying | 0 | a
822 xml | character | 0 | a
825 -- **************** pg_conversion ****************
826 -- Look for illegal values in pg_conversion fields.
827 SELECT p1.oid, p1.conname
828 FROM pg_conversion as p1
829 WHERE p1.conproc = 0 OR
830 pg_encoding_to_char(conforencoding) = '' OR
831 pg_encoding_to_char(contoencoding) = '';
836 -- Look for conprocs that don't have the expected signature.
837 SELECT p.oid, p.proname, c.oid, c.conname
838 FROM pg_proc p, pg_conversion c
839 WHERE p.oid = c.conproc AND
840 (p.prorettype != 'void'::regtype OR p.proretset OR
842 p.proargtypes[0] != 'int4'::regtype OR
843 p.proargtypes[1] != 'int4'::regtype OR
844 p.proargtypes[2] != 'cstring'::regtype OR
845 p.proargtypes[3] != 'internal'::regtype OR
846 p.proargtypes[4] != 'int4'::regtype);
847 oid | proname | oid | conname
848 -----+---------+-----+---------
851 -- Check for conprocs that don't perform the specific conversion that
852 -- pg_conversion alleges they do, by trying to invoke each conversion
853 -- on some simple ASCII data. (The conproc should throw an error if
854 -- it doesn't accept the encodings that are passed to it.)
855 -- Unfortunately, we can't test non-default conprocs this way, because
856 -- there is no way to ask convert() to invoke them, and we cannot call
857 -- them directly from SQL. But there are no non-default built-in
858 -- conversions anyway.
859 -- (Similarly, this doesn't cope with any search path issues.)
860 SELECT p1.oid, p1.conname
861 FROM pg_conversion as p1
863 convert('ABC'::bytea, pg_encoding_to_char(conforencoding),
864 pg_encoding_to_char(contoencoding)) != 'ABC';
869 -- **************** pg_operator ****************
870 -- Look for illegal values in pg_operator fields.
871 SELECT p1.oid, p1.oprname
872 FROM pg_operator as p1
873 WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
874 p1.oprresult = 0 OR p1.oprcode = 0;
879 -- Look for missing or unwanted operand types
880 SELECT p1.oid, p1.oprname
881 FROM pg_operator as p1
882 WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
883 (p1.oprleft != 0 and p1.oprkind = 'l') OR
884 (p1.oprright = 0 and p1.oprkind != 'r') OR
885 (p1.oprright != 0 and p1.oprkind = 'r');
890 -- Look for conflicting operator definitions (same names and input datatypes).
891 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
892 FROM pg_operator AS p1, pg_operator AS p2
893 WHERE p1.oid != p2.oid AND
894 p1.oprname = p2.oprname AND
895 p1.oprkind = p2.oprkind AND
896 p1.oprleft = p2.oprleft AND
897 p1.oprright = p2.oprright;
898 oid | oprcode | oid | oprcode
899 -----+---------+-----+---------
902 -- Look for commutative operators that don't commute.
903 -- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
904 -- We expect that B will always say that B.oprcom = A as well; that's not
905 -- inherently essential, but it would be inefficient not to mark it so.
906 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
907 FROM pg_operator AS p1, pg_operator AS p2
908 WHERE p1.oprcom = p2.oid AND
909 (p1.oprkind != 'b' OR
910 p1.oprleft != p2.oprright OR
911 p1.oprright != p2.oprleft OR
912 p1.oprresult != p2.oprresult OR
913 p1.oid != p2.oprcom);
914 oid | oprcode | oid | oprcode
915 -----+---------+-----+---------
918 -- Look for negatory operators that don't agree.
919 -- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
920 -- boolean results, and (x A y) == ! (x B y), or the equivalent for
921 -- single-operand operators.
922 -- We expect that B will always say that B.oprnegate = A as well; that's not
923 -- inherently essential, but it would be inefficient not to mark it so.
924 -- Also, A and B had better not be the same operator.
925 SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
926 FROM pg_operator AS p1, pg_operator AS p2
927 WHERE p1.oprnegate = p2.oid AND
928 (p1.oprkind != p2.oprkind OR
929 p1.oprleft != p2.oprleft OR
930 p1.oprright != p2.oprright OR
931 p1.oprresult != 'bool'::regtype OR
932 p2.oprresult != 'bool'::regtype OR
933 p1.oid != p2.oprnegate OR
935 oid | oprcode | oid | oprcode
936 -----+---------+-----+---------
939 -- Make a list of the names of operators that are claimed to be commutator
940 -- pairs. This list will grow over time, but before accepting a new entry
941 -- make sure you didn't link the wrong operators.
942 SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
943 FROM pg_operator o1, pg_operator o2
944 WHERE o1.oprcom = o2.oid AND o1.oprname <= o2.oprname
980 -- Likewise for negator pairs.
981 SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
982 FROM pg_operator o1, pg_operator o2
983 WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname
1005 -- A mergejoinable or hashjoinable operator must be binary, must return
1006 -- boolean, and must have a commutator (itself, unless it's a cross-type
1008 SELECT p1.oid, p1.oprname FROM pg_operator AS p1
1009 WHERE (p1.oprcanmerge OR p1.oprcanhash) AND NOT
1010 (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND p1.oprcom != 0);
1015 -- What's more, the commutator had better be mergejoinable/hashjoinable too.
1016 SELECT p1.oid, p1.oprname, p2.oid, p2.oprname
1017 FROM pg_operator AS p1, pg_operator AS p2
1018 WHERE p1.oprcom = p2.oid AND
1019 (p1.oprcanmerge != p2.oprcanmerge OR
1020 p1.oprcanhash != p2.oprcanhash);
1021 oid | oprname | oid | oprname
1022 -----+---------+-----+---------
1025 -- Mergejoinable operators should appear as equality members of btree index
1027 SELECT p1.oid, p1.oprname
1028 FROM pg_operator AS p1
1029 WHERE p1.oprcanmerge AND NOT EXISTS
1030 (SELECT 1 FROM pg_amop
1031 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
1032 amopopr = p1.oid AND amopstrategy = 3);
1037 -- And the converse.
1038 SELECT p1.oid, p1.oprname, p.amopfamily
1039 FROM pg_operator AS p1, pg_amop p
1040 WHERE amopopr = p1.oid
1041 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
1042 AND amopstrategy = 3
1043 AND NOT p1.oprcanmerge;
1044 oid | oprname | amopfamily
1045 -----+---------+------------
1048 -- Hashable operators should appear as members of hash index opfamilies.
1049 SELECT p1.oid, p1.oprname
1050 FROM pg_operator AS p1
1051 WHERE p1.oprcanhash AND NOT EXISTS
1052 (SELECT 1 FROM pg_amop
1053 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
1054 amopopr = p1.oid AND amopstrategy = 1);
1059 -- And the converse.
1060 SELECT p1.oid, p1.oprname, p.amopfamily
1061 FROM pg_operator AS p1, pg_amop p
1062 WHERE amopopr = p1.oid
1063 AND amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
1064 AND NOT p1.oprcanhash;
1065 oid | oprname | amopfamily
1066 -----+---------+------------
1069 -- Check that each operator defined in pg_operator matches its oprcode entry
1070 -- in pg_proc. Easiest to do this separately for each oprkind.
1071 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
1072 FROM pg_operator AS p1, pg_proc AS p2
1073 WHERE p1.oprcode = p2.oid AND
1074 p1.oprkind = 'b' AND
1076 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
1077 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
1078 OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
1079 oid | oprname | oid | proname
1080 -----+---------+-----+---------
1083 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
1084 FROM pg_operator AS p1, pg_proc AS p2
1085 WHERE p1.oprcode = p2.oid AND
1086 p1.oprkind = 'l' AND
1088 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
1089 OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
1090 OR p1.oprleft != 0);
1091 oid | oprname | oid | proname
1092 -----+---------+-----+---------
1095 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
1096 FROM pg_operator AS p1, pg_proc AS p2
1097 WHERE p1.oprcode = p2.oid AND
1098 p1.oprkind = 'r' AND
1100 OR NOT binary_coercible(p2.prorettype, p1.oprresult)
1101 OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
1102 OR p1.oprright != 0);
1103 oid | oprname | oid | proname
1104 -----+---------+-----+---------
1107 -- If the operator is mergejoinable or hashjoinable, its underlying function
1108 -- should not be volatile.
1109 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
1110 FROM pg_operator AS p1, pg_proc AS p2
1111 WHERE p1.oprcode = p2.oid AND
1112 (p1.oprcanmerge OR p1.oprcanhash) AND
1113 p2.provolatile = 'v';
1114 oid | oprname | oid | proname
1115 -----+---------+-----+---------
1118 -- If oprrest is set, the operator must return boolean,
1119 -- and it must link to a proc with the right signature
1120 -- to be a restriction selectivity estimator.
1121 -- The proc signature we want is: float8 proc(internal, oid, internal, int4)
1122 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
1123 FROM pg_operator AS p1, pg_proc AS p2
1124 WHERE p1.oprrest = p2.oid AND
1125 (p1.oprresult != 'bool'::regtype OR
1126 p2.prorettype != 'float8'::regtype OR p2.proretset OR
1128 p2.proargtypes[0] != 'internal'::regtype OR
1129 p2.proargtypes[1] != 'oid'::regtype OR
1130 p2.proargtypes[2] != 'internal'::regtype OR
1131 p2.proargtypes[3] != 'int4'::regtype);
1132 oid | oprname | oid | proname
1133 -----+---------+-----+---------
1136 -- If oprjoin is set, the operator must be a binary boolean op,
1137 -- and it must link to a proc with the right signature
1138 -- to be a join selectivity estimator.
1139 -- The proc signature we want is: float8 proc(internal, oid, internal, int2, internal)
1140 -- (Note: the old signature with only 4 args is still allowed, but no core
1141 -- estimator should be using it.)
1142 SELECT p1.oid, p1.oprname, p2.oid, p2.proname
1143 FROM pg_operator AS p1, pg_proc AS p2
1144 WHERE p1.oprjoin = p2.oid AND
1145 (p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
1146 p2.prorettype != 'float8'::regtype OR p2.proretset OR
1148 p2.proargtypes[0] != 'internal'::regtype OR
1149 p2.proargtypes[1] != 'oid'::regtype OR
1150 p2.proargtypes[2] != 'internal'::regtype OR
1151 p2.proargtypes[3] != 'int2'::regtype OR
1152 p2.proargtypes[4] != 'internal'::regtype);
1153 oid | oprname | oid | proname
1154 -----+---------+-----+---------
1157 -- Insist that all built-in pg_operator entries have descriptions
1158 SELECT p1.oid, p1.oprname
1159 FROM pg_operator as p1 LEFT JOIN pg_description as d
1160 ON p1.tableoid = d.classoid and p1.oid = d.objoid and d.objsubid = 0
1161 WHERE d.classoid IS NULL AND p1.oid <= 9999;
1166 -- Check that operators' underlying functions have suitable comments,
1167 -- namely 'implementation of XXX operator'. (Note: it's not necessary to
1168 -- put such comments into pg_proc.h; initdb will generate them as needed.)
1169 -- In some cases involving legacy names for operators, there are multiple
1170 -- operators referencing the same pg_proc entry, so ignore operators whose
1171 -- comments say they are deprecated.
1172 -- We also have a few functions that are both operator support and meant to
1173 -- be called directly; those should have comments matching their operator.
1175 SELECT p.oid as p_oid, proname, o.oid as o_oid,
1176 obj_description(p.oid, 'pg_proc') as prodesc,
1177 'implementation of ' || oprname || ' operator' as expecteddesc,
1178 obj_description(o.oid, 'pg_operator') as oprdesc
1179 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid
1182 SELECT * FROM funcdescs
1183 WHERE prodesc IS DISTINCT FROM expecteddesc
1184 AND oprdesc NOT LIKE 'deprecated%'
1185 AND prodesc IS DISTINCT FROM oprdesc;
1186 p_oid | proname | o_oid | prodesc | expecteddesc | oprdesc
1187 -------+---------+-------+---------+--------------+---------
1190 -- Show all the operator-implementation functions that have their own
1191 -- comments. This should happen only in cases where the function and
1192 -- operator syntaxes are both documented at the user level.
1193 -- This should be a pretty short list; it's mostly legacy cases.
1195 SELECT p.oid as p_oid, proname, o.oid as o_oid,
1196 obj_description(p.oid, 'pg_proc') as prodesc,
1197 'implementation of ' || oprname || ' operator' as expecteddesc,
1198 obj_description(o.oid, 'pg_operator') as oprdesc
1199 FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid
1202 SELECT p_oid, proname, prodesc FROM funcdescs
1203 WHERE prodesc IS DISTINCT FROM expecteddesc
1204 AND oprdesc NOT LIKE 'deprecated%'
1206 p_oid | proname | prodesc
1207 -------+-------------------------+-------------------------------------------------
1208 378 | array_append | append element onto end of array
1209 379 | array_prepend | prepend element onto front of array
1210 1035 | aclinsert | add/update ACL item
1211 1036 | aclremove | remove ACL item
1212 1037 | aclcontains | contains
1213 3217 | jsonb_extract_path | get value from jsonb with path elements
1214 3940 | jsonb_extract_path_text | get value from jsonb as text with path elements
1215 3951 | json_extract_path | get value from json with path elements
1216 3953 | json_extract_path_text | get value from json as text with path elements
1219 -- **************** pg_aggregate ****************
1220 -- Look for illegal values in pg_aggregate fields.
1221 SELECT ctid, aggfnoid::oid
1222 FROM pg_aggregate as p1
1223 WHERE aggfnoid = 0 OR aggtransfn = 0 OR
1224 aggkind NOT IN ('n', 'o', 'h') OR
1225 aggnumdirectargs < 0 OR
1226 (aggkind = 'n' AND aggnumdirectargs > 0) OR
1227 aggtranstype = 0 OR aggtransspace < 0 OR aggmtransspace < 0;
1232 -- Make sure the matching pg_proc entry is sensible, too.
1233 SELECT a.aggfnoid::oid, p.proname
1234 FROM pg_aggregate as a, pg_proc as p
1235 WHERE a.aggfnoid = p.oid AND
1236 (NOT p.proisagg OR p.proretset OR p.pronargs < a.aggnumdirectargs);
1238 ----------+---------
1241 -- Make sure there are no proisagg pg_proc entries without matches.
1244 WHERE p.proisagg AND
1245 NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
1250 -- If there is no finalfn then the output type must be the transtype.
1251 SELECT a.aggfnoid::oid, p.proname
1252 FROM pg_aggregate as a, pg_proc as p
1253 WHERE a.aggfnoid = p.oid AND
1254 a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
1256 ----------+---------
1259 -- Cross-check transfn against its entry in pg_proc.
1260 -- NOTE: use physically_coercible here, not binary_coercible, because
1261 -- max and min on abstime are implemented using int4larger/int4smaller.
1262 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
1263 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
1264 WHERE a.aggfnoid = p.oid AND
1265 a.aggtransfn = ptr.oid AND
1267 OR NOT (ptr.pronargs =
1268 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1
1269 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)
1270 OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
1271 OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
1272 OR (p.pronargs > 0 AND
1273 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
1274 OR (p.pronargs > 1 AND
1275 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
1276 OR (p.pronargs > 2 AND
1277 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
1278 -- we could carry the check further, but 3 args is enough for now
1280 aggfnoid | proname | oid | proname
1281 ----------+---------+-----+---------
1284 -- Cross-check finalfn (if present) against its entry in pg_proc.
1285 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
1286 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
1287 WHERE a.aggfnoid = p.oid AND
1288 a.aggfinalfn = pfn.oid AND
1290 NOT binary_coercible(pfn.prorettype, p.prorettype) OR
1291 NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]) OR
1292 CASE WHEN a.aggfinalextra THEN pfn.pronargs != p.pronargs + 1
1293 ELSE pfn.pronargs != a.aggnumdirectargs + 1 END
1294 OR (pfn.pronargs > 1 AND
1295 NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1]))
1296 OR (pfn.pronargs > 2 AND
1297 NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2]))
1298 OR (pfn.pronargs > 3 AND
1299 NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3]))
1300 -- we could carry the check further, but 3 args is enough for now
1302 aggfnoid | proname | oid | proname
1303 ----------+---------+-----+---------
1306 -- If transfn is strict then either initval should be non-NULL, or
1307 -- input type should match transtype so that the first non-null input
1308 -- can be assigned as the state value.
1309 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
1310 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
1311 WHERE a.aggfnoid = p.oid AND
1312 a.aggtransfn = ptr.oid AND ptr.proisstrict AND
1313 a.agginitval IS NULL AND
1314 NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
1315 aggfnoid | proname | oid | proname
1316 ----------+---------+-----+---------
1319 -- Check for inconsistent specifications of moving-aggregate columns.
1320 SELECT ctid, aggfnoid::oid
1321 FROM pg_aggregate as p1
1322 WHERE aggmtranstype != 0 AND
1323 (aggmtransfn = 0 OR aggminvtransfn = 0);
1328 SELECT ctid, aggfnoid::oid
1329 FROM pg_aggregate as p1
1330 WHERE aggmtranstype = 0 AND
1331 (aggmtransfn != 0 OR aggminvtransfn != 0 OR aggmfinalfn != 0 OR
1332 aggmtransspace != 0 OR aggminitval IS NOT NULL);
1337 -- If there is no mfinalfn then the output type must be the mtranstype.
1338 SELECT a.aggfnoid::oid, p.proname
1339 FROM pg_aggregate as a, pg_proc as p
1340 WHERE a.aggfnoid = p.oid AND
1341 a.aggmtransfn != 0 AND
1342 a.aggmfinalfn = 0 AND p.prorettype != a.aggmtranstype;
1344 ----------+---------
1347 -- Cross-check mtransfn (if present) against its entry in pg_proc.
1348 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
1349 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
1350 WHERE a.aggfnoid = p.oid AND
1351 a.aggmtransfn = ptr.oid AND
1353 OR NOT (ptr.pronargs =
1354 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1
1355 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)
1356 OR NOT physically_coercible(ptr.prorettype, a.aggmtranstype)
1357 OR NOT physically_coercible(a.aggmtranstype, ptr.proargtypes[0])
1358 OR (p.pronargs > 0 AND
1359 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
1360 OR (p.pronargs > 1 AND
1361 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
1362 OR (p.pronargs > 2 AND
1363 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
1364 -- we could carry the check further, but 3 args is enough for now
1366 aggfnoid | proname | oid | proname
1367 ----------+---------+-----+---------
1370 -- Cross-check minvtransfn (if present) against its entry in pg_proc.
1371 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
1372 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
1373 WHERE a.aggfnoid = p.oid AND
1374 a.aggminvtransfn = ptr.oid AND
1376 OR NOT (ptr.pronargs =
1377 CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1
1378 ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)
1379 OR NOT physically_coercible(ptr.prorettype, a.aggmtranstype)
1380 OR NOT physically_coercible(a.aggmtranstype, ptr.proargtypes[0])
1381 OR (p.pronargs > 0 AND
1382 NOT physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
1383 OR (p.pronargs > 1 AND
1384 NOT physically_coercible(p.proargtypes[1], ptr.proargtypes[2]))
1385 OR (p.pronargs > 2 AND
1386 NOT physically_coercible(p.proargtypes[2], ptr.proargtypes[3]))
1387 -- we could carry the check further, but 3 args is enough for now
1389 aggfnoid | proname | oid | proname
1390 ----------+---------+-----+---------
1393 -- Cross-check mfinalfn (if present) against its entry in pg_proc.
1394 SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
1395 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
1396 WHERE a.aggfnoid = p.oid AND
1397 a.aggmfinalfn = pfn.oid AND
1399 NOT binary_coercible(pfn.prorettype, p.prorettype) OR
1400 NOT binary_coercible(a.aggmtranstype, pfn.proargtypes[0]) OR
1401 CASE WHEN a.aggmfinalextra THEN pfn.pronargs != p.pronargs + 1
1402 ELSE pfn.pronargs != a.aggnumdirectargs + 1 END
1403 OR (pfn.pronargs > 1 AND
1404 NOT binary_coercible(p.proargtypes[0], pfn.proargtypes[1]))
1405 OR (pfn.pronargs > 2 AND
1406 NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2]))
1407 OR (pfn.pronargs > 3 AND
1408 NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3]))
1409 -- we could carry the check further, but 3 args is enough for now
1411 aggfnoid | proname | oid | proname
1412 ----------+---------+-----+---------
1415 -- If mtransfn is strict then either minitval should be non-NULL, or
1416 -- input type should match mtranstype so that the first non-null input
1417 -- can be assigned as the state value.
1418 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
1419 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
1420 WHERE a.aggfnoid = p.oid AND
1421 a.aggmtransfn = ptr.oid AND ptr.proisstrict AND
1422 a.aggminitval IS NULL AND
1423 NOT binary_coercible(p.proargtypes[0], a.aggmtranstype);
1424 aggfnoid | proname | oid | proname
1425 ----------+---------+-----+---------
1428 -- mtransfn and minvtransfn should have same strictness setting.
1429 SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname, iptr.oid, iptr.proname
1430 FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr, pg_proc AS iptr
1431 WHERE a.aggfnoid = p.oid AND
1432 a.aggmtransfn = ptr.oid AND
1433 a.aggminvtransfn = iptr.oid AND
1434 ptr.proisstrict != iptr.proisstrict;
1435 aggfnoid | proname | oid | proname | oid | proname
1436 ----------+---------+-----+---------+-----+---------
1439 -- Cross-check aggsortop (if present) against pg_operator.
1440 -- We expect to find entries for bool_and, bool_or, every, max, and min.
1441 SELECT DISTINCT proname, oprname
1442 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
1443 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
1446 ----------+---------
1454 -- Check datatypes match
1455 SELECT a.aggfnoid::oid, o.oid
1456 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
1457 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
1458 (oprkind != 'b' OR oprresult != 'boolean'::regtype
1459 OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
1464 -- Check operator is a suitable btree opfamily member
1465 SELECT a.aggfnoid::oid, o.oid
1466 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
1467 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
1468 NOT EXISTS(SELECT 1 FROM pg_amop
1469 WHERE amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
1471 AND amoplefttype = o.oprleft
1472 AND amoprighttype = o.oprright);
1477 -- Check correspondence of btree strategies and names
1478 SELECT DISTINCT proname, oprname, amopstrategy
1479 FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
1481 WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
1483 amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
1485 proname | oprname | amopstrategy
1486 ----------+---------+--------------
1494 -- Check that there are not aggregates with the same name and different
1495 -- numbers of arguments. While not technically wrong, we have a project policy
1496 -- to avoid this because it opens the door for confusion in connection with
1497 -- ORDER BY: novices frequently put the ORDER BY in the wrong place.
1498 -- See the fate of the single-argument form of string_agg() for history.
1499 -- (Note: we don't forbid users from creating such aggregates; the policy is
1500 -- just to think twice before creating built-in aggregates like this.)
1501 -- The only aggregates that should show up here are count(x) and count(*).
1502 SELECT p1.oid::regprocedure, p2.oid::regprocedure
1503 FROM pg_proc AS p1, pg_proc AS p2
1504 WHERE p1.oid < p2.oid AND p1.proname = p2.proname AND
1505 p1.proisagg AND p2.proisagg AND
1506 array_dims(p1.proargtypes) != array_dims(p2.proargtypes)
1509 --------------+---------
1510 count("any") | count()
1513 -- For the same reason, built-in aggregates with default arguments are no good.
1516 WHERE proisagg AND proargdefaults IS NOT NULL;
1521 -- For the same reason, we avoid creating built-in variadic aggregates, except
1522 -- that variadic ordered-set aggregates are OK (since they have special syntax
1523 -- that is not subject to the misplaced ORDER BY issue).
1524 SELECT p.oid, proname
1525 FROM pg_proc AS p JOIN pg_aggregate AS a ON a.aggfnoid = p.oid
1526 WHERE proisagg AND provariadic != 0 AND a.aggkind = 'n';
1531 -- **************** pg_opfamily ****************
1532 -- Look for illegal values in pg_opfamily fields
1534 FROM pg_opfamily as p1
1535 WHERE p1.opfmethod = 0 OR p1.opfnamespace = 0;
1540 -- **************** pg_opclass ****************
1541 -- Look for illegal values in pg_opclass fields
1543 FROM pg_opclass AS p1
1544 WHERE p1.opcmethod = 0 OR p1.opcnamespace = 0 OR p1.opcfamily = 0
1545 OR p1.opcintype = 0;
1550 -- opcmethod must match owning opfamily's opfmethod
1551 SELECT p1.oid, p2.oid
1552 FROM pg_opclass AS p1, pg_opfamily AS p2
1553 WHERE p1.opcfamily = p2.oid AND p1.opcmethod != p2.opfmethod;
1558 -- There should not be multiple entries in pg_opclass with opcdefault true
1559 -- and the same opcmethod/opcintype combination.
1560 SELECT p1.oid, p2.oid
1561 FROM pg_opclass AS p1, pg_opclass AS p2
1562 WHERE p1.oid != p2.oid AND
1563 p1.opcmethod = p2.opcmethod AND p1.opcintype = p2.opcintype AND
1564 p1.opcdefault AND p2.opcdefault;
1569 -- Ask access methods to validate opclasses
1570 -- (this replaces a lot of SQL-level checks that used to be done in this file)
1571 SELECT oid, opcname FROM pg_opclass WHERE NOT amvalidate(oid);
1576 -- **************** pg_am ****************
1577 -- Look for illegal values in pg_am fields
1578 SELECT p1.oid, p1.amname
1580 WHERE p1.amhandler = 0;
1585 -- Check for amhandler functions with the wrong signature
1586 SELECT p1.oid, p1.amname, p2.oid, p2.proname
1587 FROM pg_am AS p1, pg_proc AS p2
1588 WHERE p2.oid = p1.amhandler AND
1589 (p2.prorettype != 'index_am_handler'::regtype OR p2.proretset
1591 OR p2.proargtypes[0] != 'internal'::regtype);
1592 oid | amname | oid | proname
1593 -----+--------+-----+---------
1596 -- **************** pg_amop ****************
1597 -- Look for illegal values in pg_amop fields
1598 SELECT p1.amopfamily, p1.amopstrategy
1600 WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
1601 OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
1602 amopfamily | amopstrategy
1603 ------------+--------------
1606 SELECT p1.amopfamily, p1.amopstrategy
1608 WHERE NOT ((p1.amoppurpose = 's' AND p1.amopsortfamily = 0) OR
1609 (p1.amoppurpose = 'o' AND p1.amopsortfamily <> 0));
1610 amopfamily | amopstrategy
1611 ------------+--------------
1614 -- amopmethod must match owning opfamily's opfmethod
1615 SELECT p1.oid, p2.oid
1616 FROM pg_amop AS p1, pg_opfamily AS p2
1617 WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
1622 -- Make a list of all the distinct operator names being used in particular
1623 -- strategy slots. This is a bit hokey, since the list might need to change
1624 -- in future releases, but it's an effective way of spotting mistakes such as
1625 -- swapping two operators within a family.
1626 SELECT DISTINCT amopmethod, amopstrategy, oprname
1627 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
1629 amopmethod | amopstrategy | oprname
1630 ------------+--------------+---------
1745 -- Check that all opclass search operators have selectivity estimators.
1746 -- This is not absolutely required, but it seems a reasonable thing
1747 -- to insist on for all standard datatypes.
1748 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
1749 FROM pg_amop AS p1, pg_operator AS p2
1750 WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND
1751 (p2.oprrest = 0 OR p2.oprjoin = 0);
1752 amopfamily | amopopr | oid | oprname
1753 ------------+---------+-----+---------
1756 -- Check that each opclass in an opfamily has associated operators, that is
1757 -- ones whose oprleft matches opcintype (possibly by coercion).
1758 SELECT p1.opcname, p1.opcfamily
1759 FROM pg_opclass AS p1
1760 WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
1761 WHERE p2.amopfamily = p1.opcfamily
1762 AND binary_coercible(p1.opcintype, p2.amoplefttype));
1764 ---------+-----------
1767 -- Check that each operator listed in pg_amop has an associated opclass,
1768 -- that is one whose opcintype matches oprleft (possibly by coercion).
1769 -- Otherwise the operator is useless because it cannot be matched to an index.
1770 -- (In principle it could be useful to list such operators in multiple-datatype
1771 -- btree opfamilies, but in practice you'd expect there to be an opclass for
1772 -- every datatype the family knows about.)
1773 SELECT p1.amopfamily, p1.amopstrategy, p1.amopopr
1775 WHERE NOT EXISTS(SELECT 1 FROM pg_opclass AS p2
1776 WHERE p2.opcfamily = p1.amopfamily
1777 AND binary_coercible(p2.opcintype, p1.amoplefttype));
1778 amopfamily | amopstrategy | amopopr
1779 ------------+--------------+---------
1782 -- Operators that are primary members of opclasses must be immutable (else
1783 -- it suggests that the index ordering isn't fixed). Operators that are
1784 -- cross-type members need only be stable, since they are just shorthands
1785 -- for index probe queries.
1786 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
1787 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
1788 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
1789 p1.amoplefttype = p1.amoprighttype AND
1790 p3.provolatile != 'i';
1791 amopfamily | amopopr | oprname | prosrc
1792 ------------+---------+---------+--------
1795 SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
1796 FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
1797 WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
1798 p1.amoplefttype != p1.amoprighttype AND
1799 p3.provolatile = 'v';
1800 amopfamily | amopopr | oprname | prosrc
1801 ------------+---------+---------+--------
1804 -- **************** pg_amproc ****************
1805 -- Look for illegal values in pg_amproc fields
1806 SELECT p1.amprocfamily, p1.amprocnum
1807 FROM pg_amproc as p1
1808 WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
1809 OR p1.amprocnum < 1 OR p1.amproc = 0;
1810 amprocfamily | amprocnum
1811 --------------+-----------
1814 -- Support routines that are primary members of opfamilies must be immutable
1815 -- (else it suggests that the index ordering isn't fixed). But cross-type
1816 -- members need only be stable, since they are just shorthands
1817 -- for index probe queries.
1818 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
1819 FROM pg_amproc AS p1, pg_proc AS p2
1820 WHERE p1.amproc = p2.oid AND
1821 p1.amproclefttype = p1.amprocrighttype AND
1822 p2.provolatile != 'i';
1823 amprocfamily | amproc | prosrc
1824 --------------+--------+--------
1827 SELECT p1.amprocfamily, p1.amproc, p2.prosrc
1828 FROM pg_amproc AS p1, pg_proc AS p2
1829 WHERE p1.amproc = p2.oid AND
1830 p1.amproclefttype != p1.amprocrighttype AND
1831 p2.provolatile = 'v';
1832 amprocfamily | amproc | prosrc
1833 --------------+--------+--------
1836 -- **************** pg_index ****************
1837 -- Look for illegal values in pg_index fields.
1838 SELECT p1.indexrelid, p1.indrelid
1840 WHERE p1.indexrelid = 0 OR p1.indrelid = 0 OR
1841 p1.indnatts <= 0 OR p1.indnatts > 32;
1842 indexrelid | indrelid
1843 ------------+----------
1846 -- oidvector and int2vector fields should be of length indnatts.
1847 SELECT p1.indexrelid, p1.indrelid
1849 WHERE array_lower(indkey, 1) != 0 OR array_upper(indkey, 1) != indnatts-1 OR
1850 array_lower(indclass, 1) != 0 OR array_upper(indclass, 1) != indnatts-1 OR
1851 array_lower(indcollation, 1) != 0 OR array_upper(indcollation, 1) != indnatts-1 OR
1852 array_lower(indoption, 1) != 0 OR array_upper(indoption, 1) != indnatts-1;
1853 indexrelid | indrelid
1854 ------------+----------
1857 -- Check that opclasses and collations match the underlying columns.
1858 -- (As written, this test ignores expression indexes.)
1859 SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname
1860 FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey,
1861 unnest(indclass) as iclass, unnest(indcollation) as icoll
1865 WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND
1866 (NOT binary_coercible(atttypid, opcintype) OR icoll != attcollation);
1867 indexrelid | indrelid | attname | atttypid | opcname
1868 ------------+----------+---------+----------+---------
1871 -- For system catalogs, be even tighter: nearly all indexes should be
1872 -- exact type matches not binary-coercible matches. At this writing
1873 -- the only exception is an OID index on a regproc column.
1874 SELECT indexrelid::regclass, indrelid::regclass, attname, atttypid::regtype, opcname
1875 FROM (SELECT indexrelid, indrelid, unnest(indkey) as ikey,
1876 unnest(indclass) as iclass, unnest(indcollation) as icoll
1878 WHERE indrelid < 16384) ss,
1881 WHERE a.attrelid = indrelid AND a.attnum = ikey AND opc.oid = iclass AND
1882 (opcintype != atttypid OR icoll != attcollation)
1884 indexrelid | indrelid | attname | atttypid | opcname
1885 --------------------------+--------------+----------+----------+---------
1886 pg_aggregate_fnoid_index | pg_aggregate | aggfnoid | regproc | oid_ops
1889 -- Check for system catalogs with collation-sensitive ordering. This is not
1890 -- a representational error in pg_index, but simply wrong catalog design.
1891 -- It's bad because we expect to be able to clone template0 and assign the
1892 -- copy a different database collation. It would especially not work for
1893 -- shared catalogs. Note that although text columns will show a collation
1894 -- in indcollation, they're still okay to index with text_pattern_ops,
1895 -- so allow that case.
1896 SELECT indexrelid::regclass, indrelid::regclass, iclass, icoll
1897 FROM (SELECT indexrelid, indrelid,
1898 unnest(indclass) as iclass, unnest(indcollation) as icoll
1900 WHERE indrelid < 16384) ss
1901 WHERE icoll != 0 AND iclass !=
1902 (SELECT oid FROM pg_opclass
1903 WHERE opcname = 'text_pattern_ops' AND opcmethod =
1904 (SELECT oid FROM pg_am WHERE amname = 'btree'));
1905 indexrelid | indrelid | iclass | icoll
1906 ------------+----------+--------+-------