opr_sanity.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 1,051 行 · 第 1/3 页

-- Look for illegal values in pg_amop fields
SELECT p1.amopfamily, p1.amopstrategy
FROM pg_amop as p1
WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
    OR p1.amopopr = 0 OR p1.amopmethod = 0 OR p1.amopstrategy < 1;
 amopfamily | amopstrategy 
------------+--------------
(0 rows)

-- amoplefttype/amoprighttype must match the operator
SELECT p1.oid, p2.oid
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND NOT
    (p1.amoplefttype = p2.oprleft AND p1.amoprighttype = p2.oprright);
 oid | oid 
-----+-----
(0 rows)

-- amopmethod must match owning opfamily's opfmethod
SELECT p1.oid, p2.oid
FROM pg_amop AS p1, pg_opfamily AS p2
WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod;
 oid | oid 
-----+-----
(0 rows)

-- Cross-check amopstrategy index against parent AM
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
FROM pg_amop AS p1, pg_am AS p2
WHERE p1.amopmethod = p2.oid AND
    p1.amopstrategy > p2.amstrategies AND p2.amstrategies <> 0;
 amopfamily | amopopr | oid | amname 
------------+---------+-----+--------
(0 rows)

-- Detect missing pg_amop entries: should have as many strategy operators
-- as AM expects for each datatype combination supported by the opfamily.
-- We can't check this for AMs with variable strategy sets.
SELECT p1.amname, p2.amoplefttype, p2.amoprighttype
FROM pg_am AS p1, pg_amop AS p2
WHERE p2.amopmethod = p1.oid AND
    p1.amstrategies <> 0 AND
    p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
                        WHERE p3.amopfamily = p2.amopfamily AND
                              p3.amoplefttype = p2.amoplefttype AND
                              p3.amoprighttype = p2.amoprighttype);
 amname | amoplefttype | amoprighttype 
--------+--------------+---------------
(0 rows)

-- Check that amopopr points at a reasonable-looking operator, ie a binary
-- operator yielding boolean.
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND
    (p2.oprkind != 'b' OR p2.oprresult != 'bool'::regtype);
 amopfamily | amopopr | oid | oprname 
------------+---------+-----+---------
(0 rows)

-- Make a list of all the distinct operator names being used in particular
-- strategy slots.  This is a bit hokey, since the list might need to change
-- in future releases, but it's an effective way of spotting mistakes such as
-- swapping two operators within a family.
SELECT DISTINCT amopmethod, amopstrategy, oprname
FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
ORDER BY 1, 2, 3;
 amopmethod | amopstrategy | oprname 
------------+--------------+---------
        403 |            1 | <
        403 |            1 | ~<~
        403 |            2 | <=
        403 |            2 | ~<=~
        403 |            3 | =
        403 |            3 | ~=~
        403 |            4 | >=
        403 |            4 | ~>=~
        403 |            5 | >
        403 |            5 | ~>~
        405 |            1 | =
        405 |            1 | ~=~
        783 |            1 | <<
        783 |            1 | @@
        783 |            2 | &<
        783 |            3 | &&
        783 |            4 | &>
        783 |            5 | >>
        783 |            6 | ~=
        783 |            7 | @>
        783 |            8 | <@
        783 |            9 | &<|
        783 |           10 | <<|
        783 |           11 | |>>
        783 |           12 | |&>
        783 |           13 | ~
        783 |           14 | @
       2742 |            1 | &&
       2742 |            1 | @@
       2742 |            2 | @>
       2742 |            2 | @@@
       2742 |            3 | <@
       2742 |            4 | =
(33 rows)

-- Check that all operators linked to by opclass entries have selectivity
-- estimators.  This is not absolutely required, but it seems a reasonable
-- thing to insist on for all standard datatypes.
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND
    (p2.oprrest = 0 OR p2.oprjoin = 0);
 amopfamily | amopopr | oid | oprname 
------------+---------+-----+---------
(0 rows)

-- Check that each opclass in an opfamily has associated operators, that is
-- ones whose oprleft matches opcintype (possibly by coercion).
SELECT p1.opcname, p1.opcfamily
FROM pg_opclass AS p1
WHERE NOT EXISTS(SELECT 1 FROM pg_amop AS p2
                 WHERE p2.amopfamily = p1.opcfamily
                   AND binary_coercible(p1.opcintype, p2.amoplefttype));
 opcname | opcfamily 
---------+-----------
(0 rows)

-- Operators that are primary members of opclasses must be immutable (else
-- it suggests that the index ordering isn't fixed).  Operators that are
-- cross-type members need only be stable, since they are just shorthands
-- for index probe queries.
SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
    p1.amoplefttype = p1.amoprighttype AND
    p3.provolatile != 'i';
 amopfamily | amopopr | oprname | prosrc 
------------+---------+---------+--------
(0 rows)

SELECT p1.amopfamily, p1.amopopr, p2.oprname, p3.prosrc
FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3
WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND
    p1.amoplefttype != p1.amoprighttype AND
    p3.provolatile = 'v';
 amopfamily | amopopr | oprname | prosrc 
------------+---------+---------+--------
(0 rows)

-- Multiple-datatype btree opfamilies should provide closed sets of equality
-- operators; that is if you provide int2 = int4 and int4 = int8 then you
-- should also provide int2 = int8 (and commutators of all these).  This is
-- important because the planner tries to deduce additional qual clauses from
-- transitivity of mergejoinable operators.  If there are clauses
-- int2var = int4var and int4var = int8var, the planner will want to deduce
-- int2var = int8var ... so there should be a way to represent that.  While
-- a missing cross-type operator is now only an efficiency loss rather than
-- an error condition, it still seems reasonable to insist that all built-in
-- opfamilies be complete.
-- check commutative closure
SELECT p1.amoplefttype, p1.amoprighttype
FROM pg_amop AS p1
WHERE p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
    p1.amopstrategy = 3 AND
    p1.amoplefttype != p1.amoprighttype AND
    NOT EXISTS(SELECT 1 FROM pg_amop p2 WHERE
                 p2.amopfamily = p1.amopfamily AND
                 p2.amoplefttype = p1.amoprighttype AND
                 p2.amoprighttype = p1.amoplefttype AND
                 p2.amopstrategy = 3);
 amoplefttype | amoprighttype 
--------------+---------------
(0 rows)

-- check transitive closure
SELECT p1.amoplefttype, p1.amoprighttype, p2.amoprighttype
FROM pg_amop AS p1, pg_amop AS p2
WHERE p1.amopfamily = p2.amopfamily AND
    p1.amoprighttype = p2.amoplefttype AND
    p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
    p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
    p1.amopstrategy = 3 AND p2.amopstrategy = 3 AND
    p1.amoplefttype != p1.amoprighttype AND
    p2.amoplefttype != p2.amoprighttype AND
    NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
                 p3.amopfamily = p1.amopfamily AND
                 p3.amoplefttype = p1.amoplefttype AND
                 p3.amoprighttype = p2.amoprighttype AND
                 p3.amopstrategy = 3);
 amoplefttype | amoprighttype | amoprighttype 
--------------+---------------+---------------
(0 rows)

-- We also expect that built-in multiple-datatype hash opfamilies provide
-- complete sets of cross-type operators.  Again, this isn't required, but
-- it is reasonable to expect it for built-in opfamilies.
-- if same family has x=x and y=y, it should have x=y
SELECT p1.amoplefttype, p2.amoplefttype
FROM pg_amop AS p1, pg_amop AS p2
WHERE p1.amopfamily = p2.amopfamily AND
    p1.amoplefttype = p1.amoprighttype AND
    p2.amoplefttype = p2.amoprighttype AND
    p1.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
    p2.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'hash') AND
    p1.amopstrategy = 1 AND p2.amopstrategy = 1 AND
    p1.amoplefttype != p2.amoplefttype AND
    NOT EXISTS(SELECT 1 FROM pg_amop p3 WHERE
                 p3.amopfamily = p1.amopfamily AND
                 p3.amoplefttype = p1.amoplefttype AND
                 p3.amoprighttype = p2.amoplefttype AND
                 p3.amopstrategy = 1);
 amoplefttype | amoplefttype 
--------------+--------------
(0 rows)

-- **************** pg_amproc ****************
-- Look for illegal values in pg_amproc fields
SELECT p1.amprocfamily, p1.amprocnum
FROM pg_amproc as p1
WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0
    OR p1.amprocnum < 1 OR p1.amproc = 0;
 amprocfamily | amprocnum 
--------------+-----------
(0 rows)

-- Cross-check amprocnum index against parent AM
SELECT p1.amprocfamily, p1.amprocnum, p2.oid, p2.amname
FROM pg_amproc AS p1, pg_am AS p2, pg_opfamily AS p3
WHERE p1.amprocfamily = p3.oid AND p3.opfmethod = p2.oid AND
    p1.amprocnum > p2.amsupport;
 amprocfamily | amprocnum | oid | amname 
--------------+-----------+-----+--------
(0 rows)

-- Detect missing pg_amproc entries: should have as many support functions
-- as AM expects for each datatype combination supported by the opfamily.
SELECT p1.amname, p2.opfname, p3.amproclefttype, p3.amprocrighttype
FROM pg_am AS p1, pg_opfamily AS p2, pg_amproc AS p3
WHERE p2.opfmethod = p1.oid AND p3.amprocfamily = p2.oid AND
    p1.amsupport != (SELECT count(*) FROM pg_amproc AS p4
                     WHERE p4.amprocfamily = p2.oid AND
                           p4.amproclefttype = p3.amproclefttype AND
                           p4.amprocrighttype = p3.amprocrighttype);
 amname | opfname | amproclefttype | amprocrighttype 
--------+---------+----------------+-----------------
(0 rows)

-- Also, check if there are any pg_opclass entries that don't seem to have
-- pg_amproc support.
SELECT amname, opcname, count(*)
FROM pg_am am JOIN pg_opclass op ON opcmethod = am.oid
     LEFT JOIN pg_amproc p ON amprocfamily = opcfamily AND
         amproclefttype = amprocrighttype AND amproclefttype = opcintype
GROUP BY amname, amsupport, opcname, amprocfamily
HAVING count(*) != amsupport OR amprocfamily IS NULL;
 amname | opcname | count 
--------+---------+-------
(0 rows)

-- Unfortunately, we can't check the amproc link very well because the
-- signature of the function may be different for different support routines
-- or different base data types.
-- We can check that all the referenced instances of the same support
-- routine number take the same number of parameters, but that's about it
-- for a general check...
SELECT p1.amprocfamily, p1.amprocnum,
	p2.oid, p2.proname,
	p3.opfname,
	p4.amprocfamily, p4.amprocnum,
	p5.oid, p5.proname,
	p6.opfname
FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3,
     pg_amproc AS p4, pg_proc AS p5, pg_opfamily AS p6
WHERE p1.amprocfamily = p3.oid AND p4.amprocfamily = p6.oid AND
    p3.opfmethod = p6.opfmethod AND p1.amprocnum = p4.amprocnum AND
    p1.amproc = p2.oid AND p4.amproc = p5.oid AND
    (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs);
 amprocfamily | amprocnum | oid | proname | opfname | amprocfamily | amprocnum | oid | proname | opfname 
--------------+-----------+-----+---------+---------+--------------+-----------+-----+---------+---------
(0 rows)

-- For btree, though, we can do better since we know the support routines
-- must be of the form cmp(lefttype, righttype) returns int4.
SELECT p1.amprocfamily, p1.amprocnum,
	p2.oid, p2.proname,
	p3.opfname
FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')
    AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
    (amprocnum != 1
     OR proretset
     OR prorettype != 'int4'::regtype
     OR pronargs != 2
     OR proargtypes[0] != amproclefttype
     OR proargtypes[1] != amprocrighttype);
 amprocfamily | amprocnum | oid | proname | opfname 
--------------+-----------+-----+---------+---------
(0 rows)

-- For hash we can also do a little better: the support routines must be
-- of the form hash(lefttype) returns int4.  There are several cases where
-- we cheat and use a hash function that is physically compatible with the
-- datatype even though there's no cast, so this check does find a small
-- number of entries.
SELECT p1.amprocfamily, p1.amprocnum, p2.proname, p3.opfname
FROM pg_amproc AS p1, pg_proc AS p2, pg_opfamily AS p3
WHERE p3.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'hash')
    AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND
    (amprocnum != 1
     OR proretset
     OR prorettype != 'int4'::regtype
     OR pronargs != 1
     OR NOT physically_coercible(amproclefttype, proargtypes[0])
     OR amproclefttype != amprocrighttype)
ORDER BY 1;
 amprocfamily | amprocnum |    proname     |      opfname       
--------------+-----------+----------------+--------------------
          435 |         1 | hashint4       | date_ops
         1999 |         1 | timestamp_hash | timestamptz_ops
         2222 |         1 | hashchar       | bool_ops
         2223 |         1 | hashvarlena    | bytea_ops
         2225 |         1 | hashint4       | xid_ops
         2226 |         1 | hashint4       | cid_ops
         2229 |         1 | hashvarlena    | text_pattern_ops
         2231 |         1 | hashvarlena    | bpchar_pattern_ops
(8 rows)

-- Support routines that are primary members of opfamilies must be immutable
-- (else it suggests that the index ordering isn't fixed).  But cross-type
-- members need only be stable, since they are just shorthands
-- for index probe queries.
SELECT p1.amprocfamily, p1.amproc, p2.prosrc
FROM pg_amproc AS p1, pg_proc AS p2
WHERE p1.amproc = p2.oid AND
    p1.amproclefttype = p1.amprocrighttype AND
    p2.provolatile != 'i';
 amprocfamily | amproc | prosrc 
--------------+--------+--------
(0 rows)

SELECT p1.amprocfamily, p1.amproc, p2.prosrc
FROM pg_amproc AS p1, pg_proc AS p2
WHERE p1.amproc = p2.oid AND
    p1.amproclefttype != p1.amprocrighttype AND
    p2.provolatile = 'v';
 amprocfamily | amproc | prosrc 
--------------+--------+--------
(0 rows)