Add aggsortop column to pg_aggregate, so that MIN/MAX optimization can

be supported for all datatypes.  Add CREATE AGGREGATE and pg_dump support
too.  Add specialized min/max aggregates for bpchar, instead of depending
on text's min/max, because otherwise the possible use of bpchar indexes
cannot be recognized.
initdb forced because of catalog changes.

4 files changed, 118 insertions, 12 deletions

diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index feb912b87fe..445f41ffb94 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -25,6 +25,14 @@ WHERE	aggfinalfn != 0 AND
 ------+------------
 (0 rows)
 
+SELECT	ctid, aggsortop 
+FROM	pg_catalog.pg_aggregate fk 
+WHERE	aggsortop != 0 AND 
+	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.aggsortop);
+ ctid | aggsortop 
+------+-----------
+(0 rows)
+
 SELECT	ctid, aggtranstype 
 FROM	pg_catalog.pg_aggregate fk 
 WHERE	aggtranstype != 0 AND 
@@ -33,14 +41,6 @@ WHERE	aggtranstype != 0 AND
 ------+--------------
 (0 rows)
 
-SELECT	ctid, amgettuple 
-FROM	pg_catalog.pg_am fk 
-WHERE	amgettuple != 0 AND 
-	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amgettuple);
- ctid | amgettuple 
-------+------------
-(0 rows)
-
 SELECT	ctid, aminsert 
 FROM	pg_catalog.pg_am fk 
 WHERE	aminsert != 0 AND 
@@ -57,6 +57,22 @@ WHERE	ambeginscan != 0 AND
 ------+-------------
 (0 rows)
 
+SELECT	ctid, amgettuple 
+FROM	pg_catalog.pg_am fk 
+WHERE	amgettuple != 0 AND 
+	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amgettuple);
+ ctid | amgettuple 
+------+------------
+(0 rows)
+
+SELECT	ctid, amgetmulti 
+FROM	pg_catalog.pg_am fk 
+WHERE	amgetmulti != 0 AND 
+	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amgetmulti);
+ ctid | amgetmulti 
+------+------------
+(0 rows)
+
 SELECT	ctid, amrescan 
 FROM	pg_catalog.pg_am fk 
 WHERE	amrescan != 0 AND 
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 8740b0e938c..e2e59d675e6 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -677,6 +677,53 @@ WHERE a.aggfnoid = p.oid AND
 ----------+---------+-----+---------
 (0 rows)
 
+-- Cross-check aggsortop (if present) against pg_operator.
+-- We expect to find only "<" for "min" and ">" for "max".
+SELECT DISTINCT proname, oprname
+FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
+WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
+ORDER BY 1;
+ proname | oprname 
+---------+---------
+ max     | >
+ min     | <
+(2 rows)
+
+-- Check datatypes match
+SELECT a.aggfnoid::oid, o.oid
+FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
+WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
+    (oprkind != 'b' OR oprresult != 'boolean'::regtype
+     OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
+ aggfnoid | oid 
+----------+-----
+(0 rows)
+
+-- Check operator is a suitable btree opclass member
+SELECT a.aggfnoid::oid, o.oid
+FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
+WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
+    NOT EXISTS(SELECT 1 FROM pg_amop ao, pg_opclass oc
+               WHERE amopclaid = oc.oid AND amopsubtype = 0
+                     AND amopopr = o.oid AND opcamid = 403
+                     AND opcintype = o.oprleft AND opcdefault);
+ aggfnoid | oid 
+----------+-----
+(0 rows)
+
+-- Check correspondence of btree strategies and names
+SELECT DISTINCT proname, oprname, amopstrategy
+FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
+     pg_amop as ao, pg_opclass oc
+WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
+    amopclaid = oc.oid AND amopopr = o.oid AND opcamid = 403
+ORDER BY 1;
+ proname | oprname | amopstrategy 
+---------+---------+--------------
+ max     | >       |            5
+ min     | <       |            1
+(2 rows)
+
 -- **************** pg_opclass ****************
 -- Look for illegal values in pg_opclass fields
 SELECT p1.oid
diff --git a/src/test/regress/sql/oidjoins.sql b/src/test/regress/sql/oidjoins.sql
index 9df84436bf1..910d55f9f94 100644
--- a/src/test/regress/sql/oidjoins.sql
+++ b/src/test/regress/sql/oidjoins.sql
@@ -13,14 +13,14 @@ SELECT	ctid, aggfinalfn
 FROM	pg_catalog.pg_aggregate fk 
 WHERE	aggfinalfn != 0 AND 
 	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.aggfinalfn);
+SELECT	ctid, aggsortop 
+FROM	pg_catalog.pg_aggregate fk 
+WHERE	aggsortop != 0 AND 
+	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.aggsortop);
 SELECT	ctid, aggtranstype 
 FROM	pg_catalog.pg_aggregate fk 
 WHERE	aggtranstype != 0 AND 
 	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.aggtranstype);
-SELECT	ctid, amgettuple 
-FROM	pg_catalog.pg_am fk 
-WHERE	amgettuple != 0 AND 
-	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amgettuple);
 SELECT	ctid, aminsert 
 FROM	pg_catalog.pg_am fk 
 WHERE	aminsert != 0 AND 
@@ -29,6 +29,14 @@ SELECT	ctid, ambeginscan
 FROM	pg_catalog.pg_am fk 
 WHERE	ambeginscan != 0 AND 
 	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.ambeginscan);
+SELECT	ctid, amgettuple 
+FROM	pg_catalog.pg_am fk 
+WHERE	amgettuple != 0 AND 
+	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amgettuple);
+SELECT	ctid, amgetmulti 
+FROM	pg_catalog.pg_am fk 
+WHERE	amgetmulti != 0 AND 
+	NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amgetmulti);
 SELECT	ctid, amrescan 
 FROM	pg_catalog.pg_am fk 
 WHERE	amrescan != 0 AND 
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index 84e9b9f738e..3b74c1bbd7b 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -561,6 +561,41 @@ WHERE a.aggfnoid = p.oid AND
     a.agginitval IS NULL AND
     NOT binary_coercible(p.proargtypes[0], a.aggtranstype);
 
+-- Cross-check aggsortop (if present) against pg_operator.
+-- We expect to find only "<" for "min" and ">" for "max".
+
+SELECT DISTINCT proname, oprname
+FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
+WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid
+ORDER BY 1;
+
+-- Check datatypes match
+
+SELECT a.aggfnoid::oid, o.oid
+FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
+WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
+    (oprkind != 'b' OR oprresult != 'boolean'::regtype
+     OR oprleft != p.proargtypes[0] OR oprright != p.proargtypes[0]);
+
+-- Check operator is a suitable btree opclass member
+
+SELECT a.aggfnoid::oid, o.oid
+FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p
+WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
+    NOT EXISTS(SELECT 1 FROM pg_amop ao, pg_opclass oc
+               WHERE amopclaid = oc.oid AND amopsubtype = 0
+                     AND amopopr = o.oid AND opcamid = 403
+                     AND opcintype = o.oprleft AND opcdefault);
+
+-- Check correspondence of btree strategies and names
+
+SELECT DISTINCT proname, oprname, amopstrategy
+FROM pg_operator AS o, pg_aggregate AS a, pg_proc AS p,
+     pg_amop as ao, pg_opclass oc
+WHERE a.aggfnoid = p.oid AND a.aggsortop = o.oid AND
+    amopclaid = oc.oid AND amopopr = o.oid AND opcamid = 403
+ORDER BY 1;
+
 -- **************** pg_opclass ****************
 
 -- Look for illegal values in pg_opclass fields