Create the system catalog infrastructure needed for KNNGIST.

This commit adds columns amoppurpose and amopsortfamily to pg_amop, and
column amcanorderbyop to pg_am.  For the moment all the entries in
amcanorderbyop are "false", since the underlying support isn't there yet.

Also, extend the CREATE OPERATOR CLASS/ALTER OPERATOR FAMILY commands with
[ FOR SEARCH | FOR ORDER BY sort_operator_family ] clauses to allow the new
columns of pg_amop to be populated, and create pg_dump support for dumping
that information.

I also added some documentation, although it's perhaps a bit premature
given that the feature doesn't do anything useful yet.

Teodor Sigaev, Robert Haas, Tom Lane

2 files changed, 109 insertions, 14 deletions

diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 4703d497c1b..2176ea47da5 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -849,6 +849,14 @@ WHERE p1.amopfamily = 0 OR p1.amoplefttype = 0 OR p1.amoprighttype = 0
 ------------+--------------
 (0 rows)
 
+SELECT p1.amopfamily, p1.amopstrategy
+FROM pg_amop as p1
+WHERE NOT ((p1.amoppurpose = 's' AND p1.amopsortfamily = 0) OR
+           (p1.amoppurpose = 'o' AND p1.amopsortfamily <> 0));
+ amopfamily | amopstrategy 
+------------+--------------
+(0 rows)
+
 -- amoplefttype/amoprighttype must match the operator
 SELECT p1.oid, p2.oid
 FROM pg_amop AS p1, pg_operator AS p2
@@ -866,6 +874,25 @@ 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 for ordering operators not supported by 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.amoppurpose = 'o' AND NOT p2.amcanorderbyop;
+ amopfamily | amopopr | oid | amname 
+------------+---------+-----+--------
+(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
@@ -885,17 +912,45 @@ WHERE p2.amopmethod = p1.oid 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);
+                              p3.amoprighttype = p2.amoprighttype AND
+                              p3.amoppurpose = 's');
  amname | amoplefttype | amoprighttype 
 --------+--------------+---------------
 (0 rows)
 
+-- Currently, none of the AMs with fixed strategy sets support ordering ops.
+SELECT p1.amname, p2.amopfamily, p2.amopstrategy
+FROM pg_am AS p1, pg_amop AS p2
+WHERE p2.amopmethod = p1.oid AND
+    p1.amstrategies <> 0 AND p2.amoppurpose <> 's';
+ amname | amopfamily | amopstrategy 
+--------+------------+--------------
+(0 rows)
+
 -- Check that amopopr points at a reasonable-looking operator, ie a binary
--- operator yielding boolean.
+-- 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' OR p2.oprresult != 'bool'::regtype);
+    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)
@@ -950,12 +1005,12 @@ ORDER BY 1, 2, 3;
        2742 |            4 | =
 (39 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.
+-- Check that all opclass search operators 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
+WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND
     (p2.oprrest = 0 OR p2.oprjoin = 0);
  amopfamily | amopopr | oid | oprname 
 ------------+---------+-----+---------
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index 0d084a1f7a6..1a023a088e3 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -685,6 +685,11 @@ 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;
 
+SELECT p1.amopfamily, p1.amopstrategy
+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
@@ -698,6 +703,21 @@ 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 for ordering operators not supported by 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.amoppurpose = 'o' AND NOT p2.amcanorderbyop;
+
 -- Cross-check amopstrategy index against parent AM
 
 SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.amname
@@ -716,15 +736,35 @@ WHERE p2.amopmethod = p1.oid 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);
+                              p3.amoprighttype = p2.amoprighttype AND
+                              p3.amoppurpose = 's');
+
+-- Currently, none of the AMs with fixed strategy sets support ordering ops.
+
+SELECT p1.amname, p2.amopfamily, p2.amopstrategy
+FROM pg_am AS p1, pg_amop AS p2
+WHERE p2.amopmethod = p1.oid AND
+    p1.amstrategies <> 0 AND p2.amoppurpose <> 's';
 
 -- Check that amopopr points at a reasonable-looking operator, ie a binary
--- operator yielding boolean.
+-- 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' OR p2.oprresult != 'bool'::regtype);
+    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
@@ -735,13 +775,13 @@ SELECT DISTINCT amopmethod, amopstrategy, oprname
 FROM pg_amop p1 LEFT JOIN pg_operator p2 ON amopopr = p2.oid
 ORDER BY 1, 2, 3;
 
--- 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.
+-- Check that all opclass search operators 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
+WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND
     (p2.oprrest = 0 OR p2.oprjoin = 0);
 
 -- Check that each opclass in an opfamily has associated operators, that is