sum() on int2 and int4 columns now uses an int8, not numeric, accumulator

for speed reasons; its result type also changes to int8.  avg() on these
datatypes now accumulates the running sum in int8 for speed; but we still
deliver the final result as numeric, so that fractional accuracy is
preserved.

count() now counts and returns in int8, not int4.  I am a little nervous
about this possibly breaking users' code, but there didn't seem to be
a strong sentiment for avoiding the problem.  If we get complaints during
beta, we can change count back to int4 and add a "count8" aggregate.
For that matter, users can do it for themselves with a simple CREATE
AGGREGATE command; the int4inc function is still present, so no C hacking
is needed.

Also added max() and min() aggregates for OID that do proper unsigned
comparison, instead of piggybacking on int4 aggregates.

initdb forced.

5 files changed, 35 insertions, 14 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 9d635597414..739f4cb422d 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: catversion.h,v 1.88 2001/08/13 18:45:36 tgl Exp $
+ * $Id: catversion.h,v 1.89 2001/08/14 22:21:58 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200108131
+#define CATALOG_VERSION_NO	200108132
 
 #endif
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 32f04c5ce27..ccb70eb7faf 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pg_aggregate.h,v 1.30 2001/05/18 15:59:04 tgl Exp $
+ * $Id: pg_aggregate.h,v 1.31 2001/08/14 22:21:58 tgl Exp $
  *
  * NOTES
  *	  the genbki.sh script reads this file and generates .bki
@@ -82,16 +82,16 @@ typedef FormData_pg_aggregate *Form_pg_aggregate;
  */
 
 DATA(insert OID = 0 ( avg	PGUID int8_accum	numeric_avg		20	 1231 1700 "{0,0,0}" ));
-DATA(insert OID = 0 ( avg	PGUID int4_accum	numeric_avg		23	 1231 1700 "{0,0,0}" ));
-DATA(insert OID = 0 ( avg	PGUID int2_accum	numeric_avg		21	 1231 1700 "{0,0,0}" ));
+DATA(insert OID = 0 ( avg	PGUID int4_avg_accum int8_avg		23	 1016 1700 "{0,0}" ));
+DATA(insert OID = 0 ( avg	PGUID int2_avg_accum int8_avg		21	 1016 1700 "{0,0}" ));
 DATA(insert OID = 0 ( avg	PGUID numeric_accum  numeric_avg	1700 1231 1700 "{0,0,0}" ));
 DATA(insert OID = 0 ( avg	PGUID float4_accum	float8_avg		700  1022 701 "{0,0,0}" ));
 DATA(insert OID = 0 ( avg	PGUID float8_accum	float8_avg		701  1022 701 "{0,0,0}" ));
 DATA(insert OID = 0 ( avg	PGUID interval_accum interval_avg	1186 1187 1186 "{0 second,0 second}" ));
 
 DATA(insert OID = 0 ( sum	PGUID int8_sum			-	20 1700 1700 _null_ ));
-DATA(insert OID = 0 ( sum	PGUID int4_sum			-	23 1700 1700 _null_ ));
-DATA(insert OID = 0 ( sum	PGUID int2_sum			-	21 1700 1700 _null_ ));
+DATA(insert OID = 0 ( sum	PGUID int4_sum			-	23   20   20 _null_ ));
+DATA(insert OID = 0 ( sum	PGUID int2_sum			-	21   20   20 _null_ ));
 DATA(insert OID = 0 ( sum	PGUID float4pl			-  700	700  700 _null_ ));
 DATA(insert OID = 0 ( sum	PGUID float8pl			-  701	701  701 _null_ ));
 DATA(insert OID = 0 ( sum	PGUID cash_pl			-  790	790  790 _null_ ));
@@ -101,6 +101,7 @@ DATA(insert OID = 0 ( sum	PGUID numeric_add		- 1700 1700 1700 _null_ ));
 DATA(insert OID = 0 ( max	PGUID int8larger		-	20	 20   20 _null_ ));
 DATA(insert OID = 0 ( max	PGUID int4larger		-	23	 23   23 _null_ ));
 DATA(insert OID = 0 ( max	PGUID int2larger		-	21	 21   21 _null_ ));
+DATA(insert OID = 0 ( max	PGUID oidlarger			-	26	 26   26 _null_ ));
 DATA(insert OID = 0 ( max	PGUID float4larger		-  700	700  700 _null_ ));
 DATA(insert OID = 0 ( max	PGUID float8larger		-  701	701  701 _null_ ));
 DATA(insert OID = 0 ( max	PGUID int4larger		-  702	702  702 _null_ ));
@@ -116,6 +117,7 @@ DATA(insert OID = 0 ( max	PGUID numeric_larger	- 1700 1700 1700 _null_ ));
 DATA(insert OID = 0 ( min	PGUID int8smaller		-	20	 20   20 _null_ ));
 DATA(insert OID = 0 ( min	PGUID int4smaller		-	23	 23   23 _null_ ));
 DATA(insert OID = 0 ( min	PGUID int2smaller		-	21	 21   21 _null_ ));
+DATA(insert OID = 0 ( min	PGUID oidsmaller		-	26	 26   26 _null_ ));
 DATA(insert OID = 0 ( min	PGUID float4smaller		-  700	700  700 _null_ ));
 DATA(insert OID = 0 ( min	PGUID float8smaller		-  701	701  701 _null_ ));
 DATA(insert OID = 0 ( min	PGUID int4smaller		-  702	702  702 _null_ ));
@@ -129,10 +131,10 @@ DATA(insert OID = 0 ( min	PGUID text_smaller		-	25	 25   25 _null_ ));
 DATA(insert OID = 0 ( min	PGUID numeric_smaller	- 1700 1700 1700 _null_ ));
 
 /*
- * Using int4inc for count() is cheating a little, since it really only
+ * Using int8inc for count() is cheating a little, since it really only
  * takes 1 parameter not 2, but nodeAgg.c won't complain ...
  */
-DATA(insert OID = 0 ( count PGUID int4inc			- 0 23 23 0 ));
+DATA(insert OID = 0 ( count PGUID int8inc			- 0 20 20 0 ));
 
 DATA(insert OID = 0 ( variance	PGUID int8_accum	numeric_variance	20	 1231 1700 "{0,0,0}" ));
 DATA(insert OID = 0 ( variance	PGUID int4_accum	numeric_variance	23	 1231 1700 "{0,0,0}" ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 2bb41d49b66..12b74364a6d 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pg_proc.h,v 1.203 2001/08/13 18:45:36 tgl Exp $
+ * $Id: pg_proc.h,v 1.204 2001/08/14 22:21:58 tgl Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -1491,6 +1491,8 @@ DESCR("truncate timestamp to specified units");
 DATA(insert OID = 1218 (  date_trunc	   PGUID 12 f t f t 2 f 1186 "25 1186" 100 0 0 100	interval_trunc - ));
 DESCR("truncate interval to specified units");
 
+DATA(insert OID = 1219 (  int8inc		   PGUID 12 f t t t 1 f 20 "20" 100 0 0 100  int8inc - ));
+DESCR("increment");
 DATA(insert OID = 1230 (  int8abs		   PGUID 12 f t t t 1 f 20 "20" 100 0 0 100  int8abs - ));
 DESCR("absolute value");
 
@@ -2544,9 +2546,9 @@ DATA(insert OID = 1838 (  numeric_variance PGUID 12 f t t t 1 f 1700 "1231" 100
 DESCR("VARIANCE aggregate final function");
 DATA(insert OID = 1839 (  numeric_stddev   PGUID 12 f t t t 1 f 1700 "1231" 100 0 0 100  numeric_stddev - ));
 DESCR("STDDEV aggregate final function");
-DATA(insert OID = 1840 (  int2_sum		   PGUID 12 f t t f 2 f 1700 "1700 21" 100 0 0 100	int2_sum - ));
+DATA(insert OID = 1840 (  int2_sum		   PGUID 12 f t t f 2 f 20 "20 21" 100 0 0 100	int2_sum - ));
 DESCR("SUM(int2) transition function");
-DATA(insert OID = 1841 (  int4_sum		   PGUID 12 f t t f 2 f 1700 "1700 23" 100 0 0 100	int4_sum - ));
+DATA(insert OID = 1841 (  int4_sum		   PGUID 12 f t t f 2 f 20 "20 23" 100 0 0 100	int4_sum - ));
 DESCR("SUM(int4) transition function");
 DATA(insert OID = 1842 (  int8_sum		   PGUID 12 f t t f 2 f 1700 "1700 20" 100 0 0 100	int8_sum - ));
 DESCR("SUM(int8) transition function");
@@ -2554,6 +2556,12 @@ DATA(insert OID = 1843 (  interval_accum   PGUID 12 f t t t 2 f 1187 "1187 1186"
 DESCR("aggregate transition function");
 DATA(insert OID = 1844 (  interval_avg	   PGUID 12 f t t t 1 f 1186 "1187" 100 0 0 100  interval_avg - ));
 DESCR("AVG aggregate final function");
+DATA(insert OID = 1962 (  int2_avg_accum   PGUID 12 f t t t 2 f 1016 "1016 21" 100 0 0 100	int2_avg_accum - ));
+DESCR("AVG(int2) transition function");
+DATA(insert OID = 1963 (  int4_avg_accum   PGUID 12 f t t t 2 f 1016 "1016 23" 100 0 0 100	int4_avg_accum - ));
+DESCR("AVG(int4) transition function");
+DATA(insert OID = 1964 (  int8_avg		   PGUID 12 f t t t 1 f 1700 "1016" 100 0 0 100  int8_avg - ));
+DESCR("AVG(int) aggregate final function");
 
 /* To ASCII conversion */
 DATA(insert OID = 1845 ( to_ascii	PGUID 12 f t t t 1 f	25 "25" 100 0 0 100  to_ascii_default - ));
@@ -2715,6 +2723,11 @@ DESCR("not equal");
 DATA(insert OID = 1954 (  byteacmp		   PGUID 12 f t t t 2 f 23 "17 17" 100 0 0 100	byteacmp - ));
 DESCR("less-equal-greater");
 
+DATA(insert OID = 1965 (  oidlarger		   PGUID 12 f t t t 2 f 26 "26 26" 100 0 0 100	oidlarger - ));
+DESCR("larger of two");
+DATA(insert OID = 1966 (  oidsmaller	   PGUID 12 f t t t 2 f 26 "26 26" 100 0 0 100	oidsmaller - ));
+DESCR("smaller of two");
+
  
 /*
  * prototypes for functions pg_proc.c
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index a774309aed8..97efb759c49 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: builtins.h,v 1.160 2001/08/13 18:45:36 tgl Exp $
+ * $Id: builtins.h,v 1.161 2001/08/14 22:21:59 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -303,6 +303,8 @@ extern Datum oidlt(PG_FUNCTION_ARGS);
 extern Datum oidle(PG_FUNCTION_ARGS);
 extern Datum oidge(PG_FUNCTION_ARGS);
 extern Datum oidgt(PG_FUNCTION_ARGS);
+extern Datum oidlarger(PG_FUNCTION_ARGS);
+extern Datum oidsmaller(PG_FUNCTION_ARGS);
 extern Datum oid_text(PG_FUNCTION_ARGS);
 extern Datum text_oid(PG_FUNCTION_ARGS);
 extern Datum oidvectorin(PG_FUNCTION_ARGS);
@@ -555,6 +557,9 @@ extern Datum numeric_stddev(PG_FUNCTION_ARGS);
 extern Datum int2_sum(PG_FUNCTION_ARGS);
 extern Datum int4_sum(PG_FUNCTION_ARGS);
 extern Datum int8_sum(PG_FUNCTION_ARGS);
+extern Datum int2_avg_accum(PG_FUNCTION_ARGS);
+extern Datum int4_avg_accum(PG_FUNCTION_ARGS);
+extern Datum int8_avg(PG_FUNCTION_ARGS);
 
 /* ri_triggers.c */
 extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
diff --git a/src/include/utils/int8.h b/src/include/utils/int8.h
index 2b96d83791b..849b148060a 100644
--- a/src/include/utils/int8.h
+++ b/src/include/utils/int8.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: int8.h,v 1.26 2001/06/07 00:09:32 momjian Exp $
+ * $Id: int8.h,v 1.27 2001/08/14 22:21:59 tgl Exp $
  *
  * NOTES
  * These data types are supported on all 64-bit architectures, and may
@@ -74,6 +74,7 @@ extern Datum int8div(PG_FUNCTION_ARGS);
 extern Datum int8abs(PG_FUNCTION_ARGS);
 extern Datum int8fac(PG_FUNCTION_ARGS);
 extern Datum int8mod(PG_FUNCTION_ARGS);
+extern Datum int8inc(PG_FUNCTION_ARGS);
 extern Datum int8larger(PG_FUNCTION_ARGS);
 extern Datum int8smaller(PG_FUNCTION_ARGS);