diff options
Diffstat (limited to 'src/test/regress/expected/opr_sanity.out')
| -rw-r--r-- | src/test/regress/expected/opr_sanity.out | 204 |
1 files changed, 1 insertions, 203 deletions
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 45f13f3d067..7c09fa3d590 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1567,6 +1567,7 @@ WHERE p1.oid != p2.oid AND (0 rows) -- 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); oid | opcname -----+--------- @@ -1610,15 +1611,6 @@ WHERE NOT ((p1.amoppurpose = 's' AND p1.amopsortfamily = 0) OR ------------+-------------- (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 @@ -1627,44 +1619,6 @@ WHERE p1.amopfamily = p2.oid AND p1.amopmethod != p2.opfmethod; -----+----- (0 rows) --- 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')); - amopfamily | amopstrategy -------------+-------------- -(0 rows) - --- 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'; - amopfamily | amopopr | oid | oprname -------------+---------+-----+--------- -(0 rows) - -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; - amopfamily | amopopr | oid | oprname -------------+---------+-----+--------- -(0 rows) - -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); - 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 @@ -1843,72 +1797,6 @@ WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND ------------+---------+---------+-------- (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 @@ -1919,96 +1807,6 @@ WHERE p1.amprocfamily = 0 OR p1.amproclefttype = 0 OR p1.amprocrighttype = 0 --------------+----------- (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 --- 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); - 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 -(6 rows) - --- 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); - amprocfamily | amprocnum | oid | proname | opfname ---------------+-----------+-----+---------+--------- -(0 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 |