3
-- Sanity checks for common errors in making operator/procedure system tables:
4
-- pg_operator, pg_proc, pg_cast, pg_aggregate, pg_am, pg_amop, pg_amproc, pg_opclass.
6
-- None of the SELECTs here should ever find any matching entries,
7
-- so the expected output is easy to maintain ;-).
8
-- A test failure indicates someone messed up an entry in the system tables.
10
-- NB: we assume the oidjoins test will have caught any dangling links,
11
-- that is OID or REGPROC fields that are not zero and do not match some
12
-- row in the linked-to table. However, if we want to enforce that a link
13
-- field can't be 0, we have to check it here.
15
-- NB: run this test earlier than the create_operator test, because
16
-- that test creates some bogus operators...
17
-- Helper functions to deal with cases where binary-coercible matches are
19
-- This should match IsBinaryCoercible() in parse_coerce.c.
20
create function binary_coercible(oid, oid) returns bool as
22
EXISTS(select 1 from pg_cast where
23
castsource = $1 and casttarget = $2 and
24
castfunc = 0 and castcontext = ''i'')'
26
-- This one ignores castcontext, so it considers only physical equivalence
27
-- and not whether the coercion can be invoked implicitly.
28
create function physically_coercible(oid, oid) returns bool as
30
EXISTS(select 1 from pg_cast where
31
castsource = $1 and casttarget = $2 and
34
-- **************** pg_proc ****************
35
-- Look for illegal values in pg_proc fields.
36
-- NOTE: in reality pronargs could be more than 10, but I'm too lazy to put
37
-- a larger number of proargtypes check clauses in here. If we ever have
38
-- more-than-10-arg functions in the standard catalogs, extend this query.
39
SELECT p1.oid, p1.proname
41
WHERE p1.prolang = 0 OR p1.prorettype = 0 OR
42
p1.pronargs < 0 OR p1.pronargs > 10 OR
43
(p1.proargtypes[0] = 0 AND p1.pronargs > 0) OR
44
(p1.proargtypes[1] = 0 AND p1.pronargs > 1) OR
45
(p1.proargtypes[2] = 0 AND p1.pronargs > 2) OR
46
(p1.proargtypes[3] = 0 AND p1.pronargs > 3) OR
47
(p1.proargtypes[4] = 0 AND p1.pronargs > 4) OR
48
(p1.proargtypes[5] = 0 AND p1.pronargs > 5) OR
49
(p1.proargtypes[6] = 0 AND p1.pronargs > 6) OR
50
(p1.proargtypes[7] = 0 AND p1.pronargs > 7) OR
51
(p1.proargtypes[8] = 0 AND p1.pronargs > 8) OR
52
(p1.proargtypes[9] = 0 AND p1.pronargs > 9);
57
-- Look for conflicting proc definitions (same names and input datatypes).
58
-- (This test should be dead code now that we have the unique index
59
-- pg_proc_proname_narg_type_index, but I'll leave it in anyway.)
60
SELECT p1.oid, p1.proname, p2.oid, p2.proname
61
FROM pg_proc AS p1, pg_proc AS p2
62
WHERE p1.oid != p2.oid AND
63
p1.proname = p2.proname AND
64
p1.pronargs = p2.pronargs AND
65
p1.proargtypes = p2.proargtypes;
66
oid | proname | oid | proname
67
-----+---------+-----+---------
70
-- Considering only built-in procs (prolang = 12), look for multiple uses
71
-- of the same internal function (ie, matching prosrc fields). It's OK to
72
-- have several entries with different pronames for the same internal function,
73
-- but conflicts in the number of arguments and other critical items should
75
SELECT p1.oid, p1.proname, p2.oid, p2.proname
76
FROM pg_proc AS p1, pg_proc AS p2
77
WHERE p1.oid != p2.oid AND
78
p1.prosrc = p2.prosrc AND
79
p1.prolang = 12 AND p2.prolang = 12 AND
80
(p1.prolang != p2.prolang OR
81
p1.proisagg != p2.proisagg OR
82
p1.prosecdef != p2.prosecdef OR
83
p1.proisstrict != p2.proisstrict OR
84
p1.proretset != p2.proretset OR
85
p1.provolatile != p2.provolatile OR
86
p1.pronargs != p2.pronargs);
87
oid | proname | oid | proname
88
-----+---------+-----+---------
91
-- Look for uses of different type OIDs in the argument/result type fields
92
-- for different aliases of the same built-in function.
93
-- This indicates that the types are being presumed to be binary-equivalent,
94
-- or that the built-in function is prepared to deal with different types.
95
-- That's not wrong, necessarily, but we make lists of all the types being
96
-- so treated. Note that the expected output of this part of the test will
97
-- need to be modified whenever new pairs of types are made binary-equivalent,
98
-- or when new polymorphic built-in functions are added!
99
-- Note: ignore aggregate functions here, since they all point to the same
100
-- dummy built-in function.
101
SELECT DISTINCT p1.prorettype, p2.prorettype
102
FROM pg_proc AS p1, pg_proc AS p2
103
WHERE p1.oid != p2.oid AND
104
p1.prosrc = p2.prosrc AND
105
p1.prolang = 12 AND p2.prolang = 12 AND
106
NOT p1.proisagg AND NOT p2.proisagg AND
107
(p1.prorettype < p2.prorettype);
108
prorettype | prorettype
109
------------+------------
114
SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0]
115
FROM pg_proc AS p1, pg_proc AS p2
116
WHERE p1.oid != p2.oid AND
117
p1.prosrc = p2.prosrc AND
118
p1.prolang = 12 AND p2.prolang = 12 AND
119
NOT p1.proisagg AND NOT p2.proisagg AND
120
(p1.proargtypes[0] < p2.proargtypes[0]);
121
proargtypes | proargtypes
122
-------------+-------------
130
SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1]
131
FROM pg_proc AS p1, pg_proc AS p2
132
WHERE p1.oid != p2.oid AND
133
p1.prosrc = p2.prosrc AND
134
p1.prolang = 12 AND p2.prolang = 12 AND
135
NOT p1.proisagg AND NOT p2.proisagg AND
136
(p1.proargtypes[1] < p2.proargtypes[1]);
137
proargtypes | proargtypes
138
-------------+-------------
146
SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2]
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
NOT p1.proisagg AND NOT p2.proisagg AND
152
(p1.proargtypes[2] < p2.proargtypes[2]);
153
proargtypes | proargtypes
154
-------------+-------------
158
SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3]
159
FROM pg_proc AS p1, pg_proc AS p2
160
WHERE p1.oid != p2.oid AND
161
p1.prosrc = p2.prosrc AND
162
p1.prolang = 12 AND p2.prolang = 12 AND
163
NOT p1.proisagg AND NOT p2.proisagg AND
164
(p1.proargtypes[3] < p2.proargtypes[3]);
165
proargtypes | proargtypes
166
-------------+-------------
170
SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4]
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.proargtypes[4] < p2.proargtypes[4]);
177
proargtypes | proargtypes
178
-------------+-------------
181
SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5]
182
FROM pg_proc AS p1, pg_proc AS p2
183
WHERE p1.oid != p2.oid AND
184
p1.prosrc = p2.prosrc AND
185
p1.prolang = 12 AND p2.prolang = 12 AND
186
NOT p1.proisagg AND NOT p2.proisagg AND
187
(p1.proargtypes[5] < p2.proargtypes[5]);
188
proargtypes | proargtypes
189
-------------+-------------
192
SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6]
193
FROM pg_proc AS p1, pg_proc AS p2
194
WHERE p1.oid != p2.oid AND
195
p1.prosrc = p2.prosrc AND
196
p1.prolang = 12 AND p2.prolang = 12 AND
197
NOT p1.proisagg AND NOT p2.proisagg AND
198
(p1.proargtypes[6] < p2.proargtypes[6]);
199
proargtypes | proargtypes
200
-------------+-------------
203
SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7]
204
FROM pg_proc AS p1, pg_proc AS p2
205
WHERE p1.oid != p2.oid AND
206
p1.prosrc = p2.prosrc AND
207
p1.prolang = 12 AND p2.prolang = 12 AND
208
NOT p1.proisagg AND NOT p2.proisagg AND
209
(p1.proargtypes[7] < p2.proargtypes[7]);
210
proargtypes | proargtypes
211
-------------+-------------
214
-- Look for functions that return type "internal" and do not have any
215
-- "internal" argument. Such a function would be a security hole since
216
-- it might be used to call an internal function from an SQL command.
217
-- As of 7.3 this query should find only internal_in.
218
SELECT p1.oid, p1.proname
220
WHERE p1.prorettype = 'internal'::regtype AND NOT
221
('(' || oidvectortypes(p1.proargtypes) || ')') ~ '[^a-z0-9_]internal[^a-z0-9_]';
227
-- **************** pg_cast ****************
228
-- Catch bogus values in pg_cast columns (other than cases detected by
232
WHERE castsource = 0 OR casttarget = 0 OR castcontext NOT IN ('e', 'a', 'i');
233
castsource | casttarget | castfunc | castcontext
234
------------+------------+----------+-------------
237
-- Look for casts to/from the same type that aren't length coercion functions.
238
-- (We assume they are length coercions if they take multiple arguments.)
239
-- Such entries are not necessarily harmful, but they are useless.
242
WHERE castsource = casttarget AND castfunc = 0;
243
castsource | casttarget | castfunc | castcontext
244
------------+------------+----------+-------------
248
FROM pg_cast c, pg_proc p
249
WHERE c.castfunc = p.oid AND p.pronargs < 2 AND castsource = casttarget;
250
castsource | casttarget | castfunc | castcontext
251
------------+------------+----------+-------------
254
-- Look for cast functions that don't have the right signature. The
255
-- argument and result types in pg_proc must be the same as, or binary
256
-- compatible with, what it says in pg_cast.
257
-- As a special case, we allow casts from CHAR(n) that use functions
258
-- declared to take TEXT. This does not pass the binary-coercibility test
259
-- because CHAR(n)-to-TEXT normally invokes rtrim(). However, the results
260
-- are the same, so long as the function is one that ignores trailing blanks.
262
FROM pg_cast c, pg_proc p
263
WHERE c.castfunc = p.oid AND
264
(p.pronargs < 1 OR p.pronargs > 3
265
OR NOT (binary_coercible(c.castsource, p.proargtypes[0])
266
OR (c.castsource = 'character'::regtype AND
267
p.proargtypes[0] = 'text'::regtype))
268
OR NOT binary_coercible(p.prorettype, c.casttarget));
269
castsource | casttarget | castfunc | castcontext
270
------------+------------+----------+-------------
274
FROM pg_cast c, pg_proc p
275
WHERE c.castfunc = p.oid AND
276
((p.pronargs > 1 AND p.proargtypes[1] != 'int4'::regtype) OR
277
(p.pronargs > 2 AND p.proargtypes[2] != 'bool'::regtype));
278
castsource | casttarget | castfunc | castcontext
279
------------+------------+----------+-------------
282
-- Look for binary compatible casts that do not have the reverse
283
-- direction registered as well, or where the reverse direction is not
284
-- also binary compatible. This is legal, but usually not intended.
285
-- As of 7.4, this finds the casts from text and varchar to bpchar, because
286
-- those are binary-compatible while the reverse way goes through rtrim().
289
WHERE c.castfunc = 0 AND
290
NOT EXISTS (SELECT 1 FROM pg_cast k
291
WHERE k.castfunc = 0 AND
292
k.castsource = c.casttarget AND
293
k.casttarget = c.castsource);
294
castsource | casttarget | castfunc | castcontext
295
------------+------------+----------+-------------
300
-- **************** pg_operator ****************
301
-- Look for illegal values in pg_operator fields.
302
SELECT p1.oid, p1.oprname
303
FROM pg_operator as p1
304
WHERE (p1.oprkind != 'b' AND p1.oprkind != 'l' AND p1.oprkind != 'r') OR
305
p1.oprresult = 0 OR p1.oprcode = 0;
310
-- Look for missing or unwanted operand types
311
SELECT p1.oid, p1.oprname
312
FROM pg_operator as p1
313
WHERE (p1.oprleft = 0 and p1.oprkind != 'l') OR
314
(p1.oprleft != 0 and p1.oprkind = 'l') OR
315
(p1.oprright = 0 and p1.oprkind != 'r') OR
316
(p1.oprright != 0 and p1.oprkind = 'r');
321
-- Look for conflicting operator definitions (same names and input datatypes).
322
SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
323
FROM pg_operator AS p1, pg_operator AS p2
324
WHERE p1.oid != p2.oid AND
325
p1.oprname = p2.oprname AND
326
p1.oprkind = p2.oprkind AND
327
p1.oprleft = p2.oprleft AND
328
p1.oprright = p2.oprright;
329
oid | oprcode | oid | oprcode
330
-----+---------+-----+---------
333
-- Look for commutative operators that don't commute.
334
-- DEFINITIONAL NOTE: If A.oprcom = B, then x A y has the same result as y B x.
335
-- We expect that B will always say that B.oprcom = A as well; that's not
336
-- inherently essential, but it would be inefficient not to mark it so.
337
SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
338
FROM pg_operator AS p1, pg_operator AS p2
339
WHERE p1.oprcom = p2.oid AND
340
(p1.oprkind != 'b' OR
341
p1.oprleft != p2.oprright OR
342
p1.oprright != p2.oprleft OR
343
p1.oprresult != p2.oprresult OR
344
p1.oid != p2.oprcom);
345
oid | oprcode | oid | oprcode
346
-----+---------+-----+---------
349
-- Look for negatory operators that don't agree.
350
-- DEFINITIONAL NOTE: If A.oprnegate = B, then both A and B must yield
351
-- boolean results, and (x A y) == ! (x B y), or the equivalent for
352
-- single-operand operators.
353
-- We expect that B will always say that B.oprnegate = A as well; that's not
354
-- inherently essential, but it would be inefficient not to mark it so.
355
-- Also, A and B had better not be the same operator.
356
SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
357
FROM pg_operator AS p1, pg_operator AS p2
358
WHERE p1.oprnegate = p2.oid AND
359
(p1.oprkind != p2.oprkind OR
360
p1.oprleft != p2.oprleft OR
361
p1.oprright != p2.oprright OR
362
p1.oprresult != 'bool'::regtype OR
363
p2.oprresult != 'bool'::regtype OR
364
p1.oid != p2.oprnegate OR
366
oid | oprcode | oid | oprcode
367
-----+---------+-----+---------
370
-- Look for mergejoin operators that don't match their links.
371
-- An lsortop/rsortop link leads from an '=' operator to the
372
-- sort operator ('<' operator) that's appropriate for
373
-- its left-side or right-side data type.
374
-- An ltcmpop/gtcmpop link leads from an '=' operator to the
375
-- '<' or '>' operator of the same input datatypes.
376
-- (If the '=' operator has identical L and R input datatypes,
377
-- then lsortop, rsortop, and ltcmpop are all the same operator.)
378
SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
379
FROM pg_operator AS p1, pg_operator AS p2
380
WHERE p1.oprlsortop = p2.oid AND
381
(p1.oprname NOT IN ('=', '~=~') OR p2.oprname NOT IN ('<', '~<~') OR
382
p1.oprkind != 'b' OR p2.oprkind != 'b' OR
383
p1.oprleft != p2.oprleft OR
384
p1.oprleft != p2.oprright OR
385
p1.oprresult != 'bool'::regtype OR
386
p2.oprresult != 'bool'::regtype);
387
oid | oprcode | oid | oprcode
388
-----+---------+-----+---------
391
SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
392
FROM pg_operator AS p1, pg_operator AS p2
393
WHERE p1.oprrsortop = p2.oid AND
394
(p1.oprname NOT IN ('=', '~=~') OR p2.oprname NOT IN ('<', '~<~') OR
395
p1.oprkind != 'b' OR p2.oprkind != 'b' OR
396
p1.oprright != p2.oprleft OR
397
p1.oprright != p2.oprright OR
398
p1.oprresult != 'bool'::regtype OR
399
p2.oprresult != 'bool'::regtype);
400
oid | oprcode | oid | oprcode
401
-----+---------+-----+---------
404
SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
405
FROM pg_operator AS p1, pg_operator AS p2
406
WHERE p1.oprltcmpop = p2.oid AND
407
(p1.oprname NOT IN ('=', '~=~') OR p2.oprname NOT IN ('<', '~<~') OR
408
p1.oprkind != 'b' OR p2.oprkind != 'b' OR
409
p1.oprleft != p2.oprleft OR
410
p1.oprright != p2.oprright OR
411
p1.oprresult != 'bool'::regtype OR
412
p2.oprresult != 'bool'::regtype);
413
oid | oprcode | oid | oprcode
414
-----+---------+-----+---------
417
SELECT p1.oid, p1.oprcode, p2.oid, p2.oprcode
418
FROM pg_operator AS p1, pg_operator AS p2
419
WHERE p1.oprgtcmpop = p2.oid AND
420
(p1.oprname NOT IN ('=', '~=~') OR p2.oprname NOT IN ('>', '~>~') OR
421
p1.oprkind != 'b' OR p2.oprkind != 'b' OR
422
p1.oprleft != p2.oprleft OR
423
p1.oprright != p2.oprright OR
424
p1.oprresult != 'bool'::regtype OR
425
p2.oprresult != 'bool'::regtype);
426
oid | oprcode | oid | oprcode
427
-----+---------+-----+---------
430
-- Make sure all four links are specified if any are.
431
SELECT p1.oid, p1.oprcode
432
FROM pg_operator AS p1
433
WHERE NOT ((oprlsortop = 0 AND oprrsortop = 0 AND
434
oprltcmpop = 0 AND oprgtcmpop = 0) OR
435
(oprlsortop != 0 AND oprrsortop != 0 AND
436
oprltcmpop != 0 AND oprgtcmpop != 0));
441
-- A mergejoinable = operator must have a commutator (usually itself).
442
SELECT p1.oid, p1.oprname FROM pg_operator AS p1
443
WHERE p1.oprlsortop != 0 AND
449
-- Mergejoinable operators across datatypes must come in closed sets, that
450
-- is if you provide int2 = int4 and int4 = int8 then you must also provide
451
-- int2 = int8 (and commutators of all these). This is necessary because
452
-- the planner tries to deduce additional qual clauses from transitivity
453
-- of mergejoinable operators. If there are clauses int2var = int4var and
454
-- int4var = int8var, the planner will deduce int2var = int8var ... and it
455
-- had better have a way to represent it.
456
SELECT p1.oid, p2.oid FROM pg_operator AS p1, pg_operator AS p2
457
WHERE p1.oprlsortop != p1.oprrsortop AND
458
p1.oprrsortop = p2.oprlsortop AND
459
p2.oprlsortop != p2.oprrsortop AND
460
NOT EXISTS (SELECT 1 FROM pg_operator p3 WHERE
461
p3.oprlsortop = p1.oprlsortop AND p3.oprrsortop = p2.oprrsortop);
466
-- Hashing only works on simple equality operators "type = sametype",
467
-- since the hash itself depends on the bitwise representation of the type.
468
-- Check that allegedly hashable operators look like they might be "=".
469
SELECT p1.oid, p1.oprname
470
FROM pg_operator AS p1
471
WHERE p1.oprcanhash AND NOT
472
(p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND
473
p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND
479
-- In 6.5 we accepted hashable array equality operators when the array element
480
-- type is hashable. However, what we actually need to make hashjoin work on
481
-- an array is a hashable element type *and* no padding between elements in
482
-- the array storage (or, perhaps, guaranteed-zero padding). Currently,
483
-- since the padding code in arrayfuncs.c is pretty bogus, it seems safest
484
-- to just forbid hashjoin on array equality ops.
485
-- This should be reconsidered someday.
486
-- -- Look for array equality operators that are hashable when the underlying
487
-- -- type is not, or vice versa. This is presumably bogus.
489
-- SELECT p1.oid, p1.oprcanhash, p2.oid, p2.oprcanhash, t1.typname, t2.typname
490
-- FROM pg_operator AS p1, pg_operator AS p2, pg_type AS t1, pg_type AS t2
491
-- WHERE p1.oprname = '=' AND p1.oprleft = p1.oprright AND
492
-- p2.oprname = '=' AND p2.oprleft = p2.oprright AND
493
-- p1.oprleft = t1.oid AND p2.oprleft = t2.oid AND t1.typelem = t2.oid AND
494
-- p1.oprcanhash != p2.oprcanhash;
495
-- Substitute check: forbid hashable array ops, period.
496
SELECT p1.oid, p1.oprname
497
FROM pg_operator AS p1, pg_proc AS p2
498
WHERE p1.oprcanhash AND p1.oprcode = p2.oid AND p2.proname = 'array_eq';
503
-- Hashable operators should appear as members of hash index opclasses.
504
SELECT p1.oid, p1.oprname
505
FROM pg_operator AS p1
506
WHERE p1.oprcanhash AND NOT EXISTS
507
(SELECT 1 FROM pg_opclass op JOIN pg_amop p ON op.oid = amopclaid
508
WHERE opcamid = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
515
SELECT p1.oid, p1.oprname, op.opcname
516
FROM pg_operator AS p1, pg_opclass op, pg_amop p
517
WHERE amopopr = p1.oid AND amopclaid = op.oid
518
AND opcamid = (SELECT oid FROM pg_am WHERE amname = 'hash')
519
AND NOT p1.oprcanhash;
520
oid | oprname | opcname
521
-----+---------+---------
524
-- Check that each operator defined in pg_operator matches its oprcode entry
525
-- in pg_proc. Easiest to do this separately for each oprkind.
526
SELECT p1.oid, p1.oprname, p2.oid, p2.proname
527
FROM pg_operator AS p1, pg_proc AS p2
528
WHERE p1.oprcode = p2.oid AND
531
OR NOT binary_coercible(p2.prorettype, p1.oprresult)
532
OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
533
OR NOT binary_coercible(p1.oprright, p2.proargtypes[1]));
534
oid | oprname | oid | proname
535
-----+---------+-----+---------
538
SELECT p1.oid, p1.oprname, p2.oid, p2.proname
539
FROM pg_operator AS p1, pg_proc AS p2
540
WHERE p1.oprcode = p2.oid AND
543
OR NOT binary_coercible(p2.prorettype, p1.oprresult)
544
OR NOT binary_coercible(p1.oprright, p2.proargtypes[0])
546
oid | oprname | oid | proname
547
-----+---------+-----+---------
550
SELECT p1.oid, p1.oprname, p2.oid, p2.proname
551
FROM pg_operator AS p1, pg_proc AS p2
552
WHERE p1.oprcode = p2.oid AND
555
OR NOT binary_coercible(p2.prorettype, p1.oprresult)
556
OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0])
557
OR p1.oprright != 0);
558
oid | oprname | oid | proname
559
-----+---------+-----+---------
562
-- If the operator is mergejoinable or hashjoinable, its underlying function
563
-- should not be volatile.
564
SELECT p1.oid, p1.oprname, p2.oid, p2.proname
565
FROM pg_operator AS p1, pg_proc AS p2
566
WHERE p1.oprcode = p2.oid AND
567
(p1.oprlsortop != 0 OR p1.oprcanhash) AND
568
p2.provolatile = 'v';
569
oid | oprname | oid | proname
570
-----+---------+-----+---------
573
-- If oprrest is set, the operator must return boolean,
574
-- and it must link to a proc with the right signature
575
-- to be a restriction selectivity estimator.
576
-- The proc signature we want is: float8 proc(internal, oid, internal, int4)
577
SELECT p1.oid, p1.oprname, p2.oid, p2.proname
578
FROM pg_operator AS p1, pg_proc AS p2
579
WHERE p1.oprrest = p2.oid AND
580
(p1.oprresult != 'bool'::regtype OR
581
p2.prorettype != 'float8'::regtype OR p2.proretset OR
583
p2.proargtypes[0] != 'internal'::regtype OR
584
p2.proargtypes[1] != 'oid'::regtype OR
585
p2.proargtypes[2] != 'internal'::regtype OR
586
p2.proargtypes[3] != 'int4'::regtype);
587
oid | oprname | oid | proname
588
-----+---------+-----+---------
591
-- If oprjoin is set, the operator must be a binary boolean op,
592
-- and it must link to a proc with the right signature
593
-- to be a join selectivity estimator.
594
-- The proc signature we want is: float8 proc(internal, oid, internal, int2)
595
SELECT p1.oid, p1.oprname, p2.oid, p2.proname
596
FROM pg_operator AS p1, pg_proc AS p2
597
WHERE p1.oprjoin = p2.oid AND
598
(p1.oprkind != 'b' OR p1.oprresult != 'bool'::regtype OR
599
p2.prorettype != 'float8'::regtype OR p2.proretset OR
601
p2.proargtypes[0] != 'internal'::regtype OR
602
p2.proargtypes[1] != 'oid'::regtype OR
603
p2.proargtypes[2] != 'internal'::regtype OR
604
p2.proargtypes[3] != 'int2'::regtype);
605
oid | oprname | oid | proname
606
-----+---------+-----+---------
609
-- **************** pg_aggregate ****************
610
-- Look for illegal values in pg_aggregate fields.
611
SELECT ctid, aggfnoid::oid
612
FROM pg_aggregate as p1
613
WHERE aggfnoid = 0 OR aggtransfn = 0 OR aggtranstype = 0;
618
-- Make sure the matching pg_proc entry is sensible, too.
619
SELECT a.aggfnoid::oid, p.proname
620
FROM pg_aggregate as a, pg_proc as p
621
WHERE a.aggfnoid = p.oid AND
622
(NOT p.proisagg OR p.pronargs != 1 OR p.proretset);
627
-- Make sure there are no proisagg pg_proc entries without matches.
631
NOT EXISTS (SELECT 1 FROM pg_aggregate a WHERE a.aggfnoid = p.oid);
636
-- If there is no finalfn then the output type must be the transtype.
637
SELECT a.aggfnoid::oid, p.proname
638
FROM pg_aggregate as a, pg_proc as p
639
WHERE a.aggfnoid = p.oid AND
640
a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype;
645
-- Cross-check transfn against its entry in pg_proc.
646
-- NOTE: use physically_coercible here, not binary_coercible, because
647
-- max and min on abstime are implemented using int4larger/int4smaller.
648
SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
649
FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
650
WHERE a.aggfnoid = p.oid AND
651
a.aggtransfn = ptr.oid AND
653
OR NOT physically_coercible(ptr.prorettype, a.aggtranstype)
654
OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0])
655
OR NOT ((ptr.pronargs = 2 AND
656
physically_coercible(p.proargtypes[0], ptr.proargtypes[1]))
658
(ptr.pronargs = 1 AND
659
p.proargtypes[0] = '"any"'::regtype)));
660
aggfnoid | proname | oid | proname
661
----------+---------+-----+---------
664
-- Cross-check finalfn (if present) against its entry in pg_proc.
665
SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname
666
FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn
667
WHERE a.aggfnoid = p.oid AND
668
a.aggfinalfn = pfn.oid AND
670
OR NOT binary_coercible(pfn.prorettype, p.prorettype)
672
OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0]));
673
aggfnoid | proname | oid | proname
674
----------+---------+-----+---------
677
-- If transfn is strict then either initval should be non-NULL, or
678
-- input type should match transtype so that the first non-null input
679
-- can be assigned as the state value.
680
SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname
681
FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr
682
WHERE a.aggfnoid = p.oid AND
683
a.aggtransfn = ptr.oid AND ptr.proisstrict AND
684
a.agginitval IS NULL AND
685
NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
686
aggfnoid | proname | oid | proname
687
----------+---------+-----+---------
690
-- **************** pg_opclass ****************
691
-- Look for illegal values in pg_opclass fields
693
FROM pg_opclass as p1
694
WHERE p1.opcamid = 0 OR p1.opcintype = 0;
699
-- There should not be multiple entries in pg_opclass with opcdefault true
700
-- and the same opcamid/opcintype combination.
701
SELECT p1.oid, p2.oid
702
FROM pg_opclass AS p1, pg_opclass AS p2
703
WHERE p1.oid != p2.oid AND
704
p1.opcamid = p2.opcamid AND p1.opcintype = p2.opcintype AND
705
p1.opcdefault AND p2.opcdefault;
710
-- **************** pg_amop ****************
711
-- Look for illegal values in pg_amop fields
712
SELECT p1.amopclaid, p1.amopstrategy
714
WHERE p1.amopclaid = 0 OR p1.amopstrategy <= 0 OR p1.amopopr = 0;
715
amopclaid | amopstrategy
716
-----------+--------------
719
-- Cross-check amopstrategy index against parent AM
720
SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.amname
721
FROM pg_amop AS p1, pg_am AS p2, pg_opclass AS p3
722
WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND
723
p1.amopstrategy > p2.amstrategies;
724
amopclaid | amopopr | oid | amname
725
-----------+---------+-----+--------
728
-- Detect missing pg_amop entries: should have as many strategy operators
729
-- as AM expects for each opclass for the AM. When nondefault subtypes are
730
-- present, enforce condition separately for each subtype.
731
SELECT p1.oid, p1.amname, p2.oid, p2.opcname, p3.amopsubtype
732
FROM pg_am AS p1, pg_opclass AS p2, pg_amop AS p3
733
WHERE p2.opcamid = p1.oid AND p3.amopclaid = p2.oid AND
734
p1.amstrategies != (SELECT count(*) FROM pg_amop AS p4
735
WHERE p4.amopclaid = p2.oid AND
736
p4.amopsubtype = p3.amopsubtype);
737
oid | amname | oid | opcname | amopsubtype
738
-----+--------+-----+---------+-------------
741
-- Check that amopopr points at a reasonable-looking operator, ie a binary
742
-- operator yielding boolean.
743
SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname
744
FROM pg_amop AS p1, pg_operator AS p2
745
WHERE p1.amopopr = p2.oid AND
746
(p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
747
amopclaid | amopopr | oid | oprname
748
-----------+---------+-----+---------
751
-- Make a list of all the distinct operator names being used in particular
752
-- strategy slots. This is a bit hokey, since the list might need to change
753
-- in future releases, but it's an effective way of spotting mistakes such as
754
-- swapping two operators within a class.
755
SELECT DISTINCT opcamid, amopstrategy, oprname
756
FROM pg_amop p1 LEFT JOIN pg_opclass p2 ON amopclaid = p2.oid
757
LEFT JOIN pg_operator p3 ON amopopr = p3.oid
759
opcamid | amopstrategy | oprname
760
---------+--------------+---------
783
-- Check that all operators linked to by opclass entries have selectivity
784
-- estimators. This is not absolutely required, but it seems a reasonable
785
-- thing to insist on for all standard datatypes.
786
SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname
787
FROM pg_amop AS p1, pg_operator AS p2
788
WHERE p1.amopopr = p2.oid AND
789
(p2.oprrest = 0 OR p2.oprjoin = 0);
790
amopclaid | amopopr | oid | oprname
791
-----------+---------+-----+---------
794
-- Check that operator input types match the opclass
795
-- For 8.0, we require that oprleft match opcintype (possibly by coercion).
796
-- When amopsubtype is zero (default), oprright must equal oprleft;
797
-- when amopsubtype is not zero, oprright must equal amopsubtype.
798
SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
799
FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
800
WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
801
NOT binary_coercible(p3.opcintype, p2.oprleft);
802
amopclaid | amopopr | oid | oprname | opcname
803
-----------+---------+-----+---------+---------
806
SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
807
FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
808
WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
809
p1.amopsubtype = 0 AND
810
p2.oprleft != p2.oprright;
811
amopclaid | amopopr | oid | oprname | opcname
812
-----------+---------+-----+---------+---------
815
SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
816
FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
817
WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
818
p1.amopsubtype != 0 AND
819
p1.amopsubtype != p2.oprright;
820
amopclaid | amopopr | oid | oprname | opcname
821
-----------+---------+-----+---------+---------
824
-- Operators that are primary members of opclasses must be immutable (else
825
-- it suggests that the index ordering isn't fixed). Operators that are
826
-- cross-type members need only be stable, since they are just shorthands
827
-- for index probe queries.
828
SELECT p1.amopclaid, p1.amopopr, p2.oprname, p3.prosrc
829
FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
830
WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
831
p1.amopsubtype = 0 AND
832
p3.provolatile != 'i';
833
amopclaid | amopopr | oprname | prosrc
834
-----------+---------+---------+--------
837
SELECT p1.amopclaid, p1.amopopr, p2.oprname, p3.prosrc
838
FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
839
WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
840
p1.amopsubtype != 0 AND
841
p3.provolatile = 'v';
842
amopclaid | amopopr | oprname | prosrc
843
-----------+---------+---------+--------
846
-- **************** pg_amproc ****************
847
-- Look for illegal values in pg_amproc fields
848
SELECT p1.amopclaid, p1.amprocnum
850
WHERE p1.amopclaid = 0 OR p1.amprocnum <= 0 OR p1.amproc = 0;
851
amopclaid | amprocnum
852
-----------+-----------
855
-- Cross-check amprocnum index against parent AM
856
SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.amname
857
FROM pg_amproc AS p1, pg_am AS p2, pg_opclass AS p3
858
WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND
859
p1.amprocnum > p2.amsupport;
860
amopclaid | amprocnum | oid | amname
861
-----------+-----------+-----+--------
864
-- Detect missing pg_amproc entries: should have as many support functions
865
-- as AM expects for each opclass for the AM. When nondefault subtypes are
866
-- present, enforce condition separately for each subtype.
867
SELECT p1.oid, p1.amname, p2.oid, p2.opcname, p3.amprocsubtype
868
FROM pg_am AS p1, pg_opclass AS p2, pg_amproc AS p3
869
WHERE p2.opcamid = p1.oid AND p3.amopclaid = p2.oid AND
870
p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
871
WHERE p4.amopclaid = p2.oid AND
872
p4.amprocsubtype = p3.amprocsubtype);
873
oid | amname | oid | opcname | amprocsubtype
874
-----+--------+-----+---------+---------------
877
-- Unfortunately, we can't check the amproc link very well because the
878
-- signature of the function may be different for different support routines
879
-- or different base data types.
880
-- We can check that all the referenced instances of the same support
881
-- routine number take the same number of parameters, but that's about it
882
-- for a general check...
883
SELECT p1.amopclaid, p1.amprocnum,
886
p4.amopclaid, p4.amprocnum,
889
FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3,
890
pg_amproc AS p4, pg_proc AS p5, pg_opclass AS p6
891
WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND
892
p3.opcamid = p6.opcamid AND p1.amprocnum = p4.amprocnum AND
893
p1.amproc = p2.oid AND p4.amproc = p5.oid AND
894
(p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
895
amopclaid | amprocnum | oid | proname | opcname | amopclaid | amprocnum | oid | proname | opcname
896
-----------+-----------+-----+---------+---------+-----------+-----------+-----+---------+---------
899
-- For btree, though, we can do better since we know the support routines
900
-- must be of the form cmp(input, input) returns int4 in the default case
901
-- (subtype = 0), and cmp(input, subtype) returns int4 when subtype != 0.
902
SELECT p1.amopclaid, p1.amprocnum,
905
FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3
906
WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
907
AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND
908
amprocsubtype = 0 AND
914
OR NOT binary_coercible(opcintype, proargtypes[0])
915
OR proargtypes[0] != proargtypes[1]);
916
amopclaid | amprocnum | oid | proname | opcname
917
-----------+-----------+-----+---------+---------
920
SELECT p1.amopclaid, p1.amprocnum,
923
FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3
924
WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
925
AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND
926
amprocsubtype != 0 AND
932
OR NOT binary_coercible(opcintype, proargtypes[0])
933
OR proargtypes[1] != amprocsubtype);
934
amopclaid | amprocnum | oid | proname | opcname
935
-----------+-----------+-----+---------+---------
938
-- For hash we can also do a little better: the support routines must be
939
-- of the form hash(something) returns int4. Ideally we'd check that the
940
-- opcintype is binary-coercible to the function's input, but there are
941
-- enough cases where that fails that I'll just leave out the check for now.
942
SELECT p1.amopclaid, p1.amprocnum,
945
FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3
946
WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'hash')
947
AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND
953
-- OR NOT physically_coercible(opcintype, proargtypes[0])
955
amopclaid | amprocnum | oid | proname | opcname
956
-----------+-----------+-----+---------+---------
959
-- Support routines that are primary members of opclasses must be immutable
960
-- (else it suggests that the index ordering isn't fixed). But cross-type
961
-- members need only be stable, since they are just shorthands
962
-- for index probe queries.
963
SELECT p1.amopclaid, p1.amproc, p2.prosrc
964
FROM pg_amproc AS p1, pg_proc AS p2
965
WHERE p1.amproc = p2.oid AND
966
p1.amprocsubtype = 0 AND
967
p2.provolatile != 'i';
968
amopclaid | amproc | prosrc
969
-----------+--------+--------
972
SELECT p1.amopclaid, p1.amproc, p2.prosrc
973
FROM pg_amproc AS p1, pg_proc AS p2
974
WHERE p1.amproc = p2.oid AND
975
p1.amprocsubtype != 0 AND
976
p2.provolatile = 'v';
977
amopclaid | amproc | prosrc
978
-----------+--------+--------
added
removed
src/test/regress/expected/opr_sanity.out
