diff options
Diffstat (limited to 'src/test/regress/sql/opr_sanity.sql')
| -rw-r--r-- | src/test/regress/sql/opr_sanity.sql | 171 |
1 files changed, 1 insertions, 170 deletions
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index c42c8a35616..6c9784a3872 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -1037,6 +1037,7 @@ WHERE p1.oid != p2.oid AND p1.opcdefault AND p2.opcdefault; -- Ask access methods to validate opclasses +-- (this replaces a lot of SQL-level checks that used to be done in this file) SELECT oid, opcname FROM pg_opclass WHERE NOT amvalidate(oid); @@ -1073,47 +1074,12 @@ FROM pg_amop as p1 WHERE NOT ((p1.amoppurpose = 's' AND p1.amopsortfamily = 0) OR (p1.amoppurpose = 'o' AND p1.amopsortfamily <> 0)); --- 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); - -- 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; --- amopsortfamily, if present, must reference a btree family - -SELECT p1.amopfamily, p1.amopstrategy -FROM pg_amop AS p1 -WHERE p1.amopsortfamily <> 0 AND NOT EXISTS - (SELECT 1 from pg_opfamily op WHERE op.oid = p1.amopsortfamily - AND op.opfmethod = (SELECT oid FROM pg_am WHERE amname = 'btree')); - --- Check that amopopr points at a reasonable-looking operator, ie a binary --- operator. If it's a search operator it had better yield boolean, --- otherwise an input type of its sort opfamily. - -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'; - -SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname -FROM pg_amop AS p1, pg_operator AS p2 -WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND - p2.oprresult != 'bool'::regtype; - -SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname -FROM pg_amop AS p1, pg_operator AS p2 -WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 'o' AND NOT EXISTS - (SELECT 1 FROM pg_opclass op - WHERE opcfamily = p1.amopsortfamily AND opcintype = p2.oprresult); - -- 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 @@ -1171,65 +1137,6 @@ WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND p1.amoplefttype != p1.amoprighttype AND p3.provolatile = 'v'; --- 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); - --- 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); - --- 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); - -- **************** pg_amproc **************** @@ -1240,82 +1147,6 @@ 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; --- 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); - --- For btree, though, we can do better since we know the support routines --- must be of the form cmp(lefttype, righttype) returns int4 --- or sortsupport(internal) returns void. - -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 - (CASE WHEN amprocnum = 1 - THEN prorettype != 'int4'::regtype OR proretset OR pronargs != 2 - OR proargtypes[0] != amproclefttype - OR proargtypes[1] != amprocrighttype - WHEN amprocnum = 2 - THEN prorettype != 'void'::regtype OR proretset OR pronargs != 1 - OR proargtypes[0] != 'internal'::regtype - ELSE true END); - --- 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; - --- We can also check SP-GiST carefully, since the support routine signatures --- are independent of the datatype being indexed. - -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 = 'spgist') - AND p1.amprocfamily = p3.oid AND p1.amproc = p2.oid AND - (CASE WHEN amprocnum = 1 OR amprocnum = 2 OR amprocnum = 3 OR amprocnum = 4 - THEN prorettype != 'void'::regtype OR proretset OR pronargs != 2 - OR proargtypes[0] != 'internal'::regtype - OR proargtypes[1] != 'internal'::regtype - WHEN amprocnum = 5 - THEN prorettype != 'bool'::regtype OR proretset OR pronargs != 2 - OR proargtypes[0] != 'internal'::regtype - OR proargtypes[1] != 'internal'::regtype - ELSE true END); - -- 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 |