1 files changed, 45 insertions, 73 deletions

diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index a158592330..598f0ae2d6 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -425,85 +425,85 @@ WHERE p1.aggtransfn = p2.oid AND p2.proisstrict AND
 
 -- **************** pg_opclass ****************
 
--- There should not be multiple entries in pg_opclass with the same
--- nonzero opcdeftype value, because there can be only one default opclass
--- for a datatype.  (But multiple entries with zero opcdeftype are OK.)
+-- Look for illegal values in pg_opclass fields
+
+SELECT p1.oid
+FROM pg_opclass as p1
+WHERE p1.opcamid = 0 OR p1.opcintype = 0;
+
+-- There should not be multiple entries in pg_opclass with opcdefault true
+-- and the same opcamid/opcintype combination.
 
 SELECT p1.oid, p2.oid
 FROM pg_opclass AS p1, pg_opclass AS p2
 WHERE p1.oid != p2.oid AND
-    p1.opcdeftype = p2.opcdeftype AND
-    p1.opcdeftype != 0;
+    p1.opcamid = p2.opcamid AND p1.opcintype = p2.opcintype AND
+    p1.opcdefault AND p2.opcdefault;
 
 -- **************** pg_amop ****************
 
 -- Look for illegal values in pg_amop fields
 
-SELECT p1.amopclaid, p1.amopopr, p1.amopid
+SELECT p1.amopclaid, p1.amopstrategy
 FROM pg_amop as p1
-WHERE p1.amopid = 0 OR p1.amopclaid = 0 OR p1.amopopr = 0 OR
-    p1.amopstrategy <= 0;
+WHERE p1.amopclaid = 0 OR p1.amopstrategy <= 0 OR p1.amopopr = 0;
 
 -- Cross-check amopstrategy index against parent AM
 
-SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.amname
-FROM pg_amop AS p1, pg_am AS p2
-WHERE p1.amopid = p2.oid AND p1.amopstrategy > p2.amstrategies;
+SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.amname
+FROM pg_amop AS p1, pg_am AS p2, pg_opclass AS p3
+WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND
+    p1.amopstrategy > p2.amstrategies;
 
 -- Detect missing pg_amop entries: should have as many strategy functions
--- as AM expects for each opclass, unless there are none at all
--- (some opclasses only offer support for a limited set of AMs...)
+-- as AM expects for each opclass for the AM
 
 SELECT p1.oid, p1.amname, p2.oid, p2.opcname
 FROM pg_am AS p1, pg_opclass AS p2
-WHERE p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
-                          WHERE p3.amopid = p1.oid AND p3.amopclaid = p2.oid)
-              AND EXISTS (SELECT * FROM pg_amop AS p3
-                          WHERE p3.amopid = p1.oid AND p3.amopclaid = p2.oid);
+WHERE p2.opcamid = p1.oid AND
+    p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3
+                        WHERE p3.amopclaid = p2.oid);
 
 -- Check that amopopr points at a reasonable-looking operator, ie a binary
 -- operator yielding boolean.
 -- NOTE: for 7.1, add restriction that operator inputs are of same type.
 -- We used to have opclasses like "int24_ops" but these were broken.
 
-SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname
+SELECT p1.amopclaid, 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 != 16 OR p2.oprleft != p2.oprright);
 
--- If opclass is for a specific type, operator inputs should be of that type
+-- Check that operator input types match the opclass
 
-SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname, p3.oid, p3.opcname
+SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname
 FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3
 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND
-    p3.opcdeftype != 0 AND
-    (p3.opcdeftype != p2.oprleft OR p3.opcdeftype != p2.oprright);
+    (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright);
 
 -- **************** pg_amproc ****************
 
 -- Look for illegal values in pg_amproc fields
 
-SELECT p1.amid, p1.amopclaid, p1.amprocnum
+SELECT p1.amopclaid, p1.amprocnum
 FROM pg_amproc as p1
-WHERE p1.amid = 0 OR p1.amopclaid = 0 OR p1.amproc = 0 OR
-    p1.amprocnum <= 0;
+WHERE p1.amopclaid = 0 OR p1.amprocnum <= 0 OR p1.amproc = 0;
 
 -- Cross-check amprocnum index against parent AM
 
-SELECT p1.amid, p1.amopclaid, p1.amprocnum, p2.oid, p2.amname
-FROM pg_amproc AS p1, pg_am AS p2
-WHERE p1.amid = p2.oid AND p1.amprocnum > p2.amsupport;
+SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.amname
+FROM pg_amproc AS p1, pg_am AS p2, pg_opclass AS p3
+WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND
+    p1.amprocnum > p2.amsupport;
 
 -- Detect missing pg_amproc entries: should have as many support functions
--- as AM expects for each opclass, unless there are none at all
--- (some opclasses only offer support for a limited set of AMs...)
+-- as AM expects for each opclass for the AM
 
 SELECT p1.oid, p1.amname, p2.oid, p2.opcname
 FROM pg_am AS p1, pg_opclass AS p2
-WHERE p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3
-                       WHERE p3.amid = p1.oid AND p3.amopclaid = p2.oid)
-           AND EXISTS (SELECT * FROM pg_amproc AS p3
-                       WHERE p3.amid = p1.oid AND p3.amopclaid = p2.oid);
+WHERE p2.opcamid = p1.oid AND
+    p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3
+                     WHERE p3.amopclaid = p2.oid);
 
 -- Unfortunately, we can't check the amproc link very well because the
 -- signature of the function may be different for different support routines
@@ -511,43 +511,15 @@ WHERE p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3
 -- 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...
 
-SELECT p1.amid, p1.amopclaid, p1.amprocnum,
+SELECT p1.amopclaid, p1.amprocnum,
 	p2.oid, p2.proname,
-	p3.amid, p3.amopclaid, p3.amprocnum,
-	p4.oid, p4.proname
-FROM pg_amproc AS p1, pg_proc AS p2, pg_amproc AS p3, pg_proc AS p4
-WHERE p1.amid = p3.amid AND p1.amprocnum = p3.amprocnum AND
-    p1.amproc = p2.oid AND p3.amproc = p4.oid AND
-    (p2.proretset OR p4.proretset OR p2.pronargs != p4.pronargs);
-
--- Cross-check that each opclass that has any entries for a given AM
--- has all the entries that any other opclass does.  This catches cases
--- where an opclass has pg_amop but not pg_amproc entries or vice versa.
--- (The above tests for missing pg_amop or pg_amproc entries are redundant
--- with this, but I'll leave them in place anyway.)
-
--- All the strategy index numbers used for each AM
-CREATE TEMP TABLE amopstrategies AS
-    SELECT DISTINCT amopid, amopstrategy FROM pg_amop;
-
--- All the support proc numbers used for each AM
-CREATE TEMP TABLE amprocnums AS
-    SELECT DISTINCT amid, amprocnum FROM pg_amproc;
-
--- All the opclasses that claim to have support for each AM in either table.
--- UNION implies DISTINCT, so we do not need DISTINCT in the sub-selects.
-CREATE TEMP TABLE amopclassids AS
-    SELECT amid, amopclaid FROM pg_amproc UNION
-    SELECT amopid, amopclaid FROM pg_amop;
-
--- Look for AMs that are missing one or more strategy operators
-SELECT * FROM amopclassids c, amopstrategies s
-WHERE c.amid = s.amopid AND NOT EXISTS
-      (SELECT 1 FROM pg_amop a WHERE a.amopid = c.amid AND
-       a.amopclaid = c.amopclaid AND a.amopstrategy = s.amopstrategy);
-
--- Look for AMs that are missing one or more support procs
-SELECT * FROM amopclassids c, amprocnums p
-WHERE c.amid = p.amid AND NOT EXISTS
-      (SELECT 1 FROM pg_amproc a WHERE a.amid = c.amid AND
-       a.amopclaid = c.amopclaid AND a.amprocnum = p.amprocnum);
+	p3.opcname,
+	p4.amopclaid, p4.amprocnum,
+	p5.oid, p5.proname,
+	p6.opcname
+FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3,
+     pg_amproc AS p4, pg_proc AS p5, pg_opclass AS p6
+WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND
+    p3.opcamid = p6.opcamid 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);