Hi,
There was recently talk about if we should start using 128-bit integers
(where available) to speed up the aggregate functions over integers
which uses numeric for their internal state. So I hacked together a
patch for this to see what the performance gain would be.
Previous thread:
http://www.postgresql.org/message-id/20141017182500.gf2...@alap3.anarazel.de
What the patch does is switching from using numerics in the aggregate
state to int128 and then convert the type from the 128-bit integer in
the final function.
The functions where we can make use of int128 states are:
- sum(int8)
- avg(int8)
- var_*(int2)
- var_*(int4)
- stdev_*(int2)
- stdev_*(int4)
The initial benchmark results look very promising. When summing 10
million int8 I get a speedup of ~2.5x and similarly for var_samp() on 10
million int4 I see a speed up of ~3.7x. To me this indicates that it is
worth the extra code. What do you say? Is this worth implementing?
The current patch still requires work. I have not written the detection
of int128 support yet, and the patch needs code cleanup (for example: I
used an int16_ prefix on the added functions, suggestions for better
names are welcome). I also need to decide on what estimate to use for
the size of that state.
The patch should work and pass make check on platforms where __int128_t
is supported.
The simple benchmarks:
CREATE TABLE test_int8 AS SELECT x::int8 FROM generate_series(1,
10000000) x;
Before:
# SELECT sum(x) FROM test_int8;
sum
----------------
50000005000000
(1 row)
Time: 2521.217 ms
After:
# SELECT sum(x) FROM test_int8;
sum
----------------
50000005000000
(1 row)
Time: 1022.811 ms
CREATE TABLE test_int4 AS SELECT x::int4 FROM generate_series(1,
10000000) x;
Before:
# SELECT var_samp(x) FROM test_int4;
var_samp
--------------------
8333334166666.6667
(1 row)
Time: 3808.546 ms
After:
# SELECT var_samp(x) FROM test_int4;
var_samp
--------------------
8333334166666.6667
(1 row)
Time: 1033.243 ms
Andreas
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
new file mode 100644
index 2d6a4cb..65a3d08
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
*************** typedef int16 NumericDigit;
*** 119,124 ****
--- 119,129 ----
* The weight is arbitrary in that case, but we normally set it to zero.
*/
+ /* FIXME: Do this properly */
+ typedef __int128_t int128;
+ typedef __uint128_t uint128;
+ #define HAVE_INT128 1
+
struct NumericShort
{
uint16 n_header; /* Sign + display scale + weight */
*************** static void apply_typmod(NumericVar *var
*** 389,394 ****
--- 394,402 ----
static int32 numericvar_to_int4(NumericVar *var);
static bool numericvar_to_int8(NumericVar *var, int64 *result);
static void int8_to_numericvar(int64 val, NumericVar *var);
+ #ifdef HAVE_INT128
+ static void int16_to_numericvar(int128 val, NumericVar *var);
+ #endif
static double numeric_to_double_no_overflow(Numeric num);
static double numericvar_to_double_no_overflow(NumericVar *var);
*************** numeric_accum_inv(PG_FUNCTION_ARGS)
*** 2775,2783 ****
--- 2783,2867 ----
* routines for SUM and AVG of these datatypes.
*/
+ #ifdef HAVE_INT128
+ typedef struct Int16AggState
+ {
+ bool calcSumX2; /* if true, calculate sumX2 */
+ int64 N; /* count of processed numbers */
+ int128 sumX; /* sum of processed numbers */
+ int128 sumX2; /* sum of squares of processed numbers */
+ } Int16AggState;
+
+ /*
+ * Prepare state data for a numeric aggregate function that needs to compute
+ * sum, count and optionally sum of squares of the input.
+ */
+ static Int16AggState *
+ makeInt16AggState(FunctionCallInfo fcinfo, bool calcSumX2)
+ {
+ Int16AggState *state;
+ MemoryContext agg_context;
+ MemoryContext old_context;
+
+ if (!AggCheckCallContext(fcinfo, &agg_context))
+ elog(ERROR, "aggregate function called in non-aggregate context");
+
+ old_context = MemoryContextSwitchTo(agg_context);
+
+ state = (Int16AggState *) palloc0(sizeof(Int16AggState));
+ state->calcSumX2 = calcSumX2;
+
+ MemoryContextSwitchTo(old_context);
+
+ return state;
+ }
+
+ /*
+ * Accumulate a new input value for numeric aggregate functions.
+ */
+ static void
+ do_int16_accum(Int16AggState *state, int128 newval)
+ {
+ if (state->calcSumX2)
+ state->sumX2 += newval * newval;
+
+ state->sumX += newval;
+ state->N++;
+ }
+
+ /*
+ * Remove an input value from the aggregated state.
+ */
+ static void
+ do_int16_discard(Int16AggState *state, int128 newval)
+ {
+ if (state->calcSumX2)
+ state->sumX2 -= newval * newval;
+
+ state->sumX -= newval;
+ state->N--;
+ }
+ #endif
+
Datum
int2_accum(PG_FUNCTION_ARGS)
{
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ /* Create the state data on the first call */
+ if (state == NULL)
+ state = makeInt16AggState(fcinfo, true);
+
+ if (!PG_ARGISNULL(1))
+ {
+ int16 newval = PG_GETARG_INT16(1);
+
+ do_int16_accum(state, newval);
+ }
+ #else
NumericAggState *state;
state = PG_ARGISNULL(0) ? NULL : (NumericAggState *) PG_GETARG_POINTER(0);
*************** int2_accum(PG_FUNCTION_ARGS)
*** 2794,2799 ****
--- 2878,2884 ----
PG_GETARG_DATUM(1)));
do_numeric_accum(state, newval);
}
+ #endif
PG_RETURN_POINTER(state);
}
*************** int2_accum(PG_FUNCTION_ARGS)
*** 2801,2806 ****
--- 2886,2907 ----
Datum
int4_accum(PG_FUNCTION_ARGS)
{
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ /* Create the state data on the first call */
+ if (state == NULL)
+ state = makeInt16AggState(fcinfo, true);
+
+ if (!PG_ARGISNULL(1))
+ {
+ int32 newval = PG_GETARG_INT32(1);
+
+ do_int16_accum(state, newval);
+ }
+ #else
NumericAggState *state;
state = PG_ARGISNULL(0) ? NULL : (NumericAggState *) PG_GETARG_POINTER(0);
*************** int4_accum(PG_FUNCTION_ARGS)
*** 2817,2822 ****
--- 2918,2924 ----
PG_GETARG_DATUM(1)));
do_numeric_accum(state, newval);
}
+ #endif
PG_RETURN_POINTER(state);
}
*************** int8_accum(PG_FUNCTION_ARGS)
*** 2850,2855 ****
--- 2952,2973 ----
Datum
int8_avg_accum(PG_FUNCTION_ARGS)
{
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ /* Create the state data on the first call */
+ if (state == NULL)
+ state = makeInt16AggState(fcinfo, false);
+
+ if (!PG_ARGISNULL(1))
+ {
+ int64 newval = PG_GETARG_INT64(1);
+
+ do_int16_accum(state, newval);
+ }
+ #else
NumericAggState *state;
state = PG_ARGISNULL(0) ? NULL : (NumericAggState *) PG_GETARG_POINTER(0);
*************** int8_avg_accum(PG_FUNCTION_ARGS)
*** 2866,2871 ****
--- 2984,2990 ----
PG_GETARG_DATUM(1)));
do_numeric_accum(state, newval);
}
+ #endif
PG_RETURN_POINTER(state);
}
*************** int8_avg_accum(PG_FUNCTION_ARGS)
*** 2878,2883 ****
--- 2997,3018 ----
Datum
int2_accum_inv(PG_FUNCTION_ARGS)
{
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ /* Should not get here with no state */
+ if (state == NULL)
+ elog(ERROR, "int2_accum_inv called with NULL state");
+
+ if (!PG_ARGISNULL(1))
+ {
+ int16 newval = PG_GETARG_INT16(1);
+
+ do_int16_discard(state, newval);
+ }
+ #else
NumericAggState *state;
state = PG_ARGISNULL(0) ? NULL : (NumericAggState *) PG_GETARG_POINTER(0);
*************** int2_accum_inv(PG_FUNCTION_ARGS)
*** 2897,2902 ****
--- 3032,3038 ----
if (!do_numeric_discard(state, newval))
elog(ERROR, "do_numeric_discard failed unexpectedly");
}
+ #endif
PG_RETURN_POINTER(state);
}
*************** int2_accum_inv(PG_FUNCTION_ARGS)
*** 2904,2909 ****
--- 3040,3061 ----
Datum
int4_accum_inv(PG_FUNCTION_ARGS)
{
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ /* Should not get here with no state */
+ if (state == NULL)
+ elog(ERROR, "int4_accum_inv called with NULL state");
+
+ if (!PG_ARGISNULL(1))
+ {
+ int32 newval = PG_GETARG_INT32(1);
+
+ do_int16_discard(state, newval);
+ }
+ #else
NumericAggState *state;
state = PG_ARGISNULL(0) ? NULL : (NumericAggState *) PG_GETARG_POINTER(0);
*************** int4_accum_inv(PG_FUNCTION_ARGS)
*** 2923,2928 ****
--- 3075,3081 ----
if (!do_numeric_discard(state, newval))
elog(ERROR, "do_numeric_discard failed unexpectedly");
}
+ #endif
PG_RETURN_POINTER(state);
}
*************** int8_accum_inv(PG_FUNCTION_ARGS)
*** 2954,2959 ****
--- 3107,3213 ----
}
Datum
+ int8_avg_accum_inv(PG_FUNCTION_ARGS)
+ {
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ /* Should not get here with no state */
+ if (state == NULL)
+ elog(ERROR, "int8_avg_accum_inv called with NULL state");
+
+ if (!PG_ARGISNULL(1))
+ {
+ int64 newval = PG_GETARG_INT64(1);
+
+ do_int16_discard(state, newval);
+ }
+ #else
+ NumericAggState *state;
+
+ state = PG_ARGISNULL(0) ? NULL : (NumericAggState *) PG_GETARG_POINTER(0);
+
+ /* Should not get here with no state */
+ if (state == NULL)
+ elog(ERROR, "int8_avg_accum_inv called with NULL state");
+
+ if (!PG_ARGISNULL(1))
+ {
+ Numeric newval;
+
+ newval = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
+ PG_GETARG_DATUM(1)));
+
+ /* Should never fail, all inputs have dscale 0 */
+ if (!do_numeric_discard(state, newval))
+ elog(ERROR, "do_numeric_discard failed unexpectedly");
+ }
+ #endif
+
+ PG_RETURN_POINTER(state);
+ }
+
+ Datum
+ int16_sum(PG_FUNCTION_ARGS)
+ {
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+ Numeric res;
+ NumericVar result;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ /* If there were no non-null inputs, return NULL */
+ if (state == NULL || state->N == 0)
+ PG_RETURN_NULL();
+
+ init_var(&result);
+
+ int16_to_numericvar(state->sumX, &result);
+
+ res = make_result(&result);
+
+ free_var(&result);
+
+ PG_RETURN_NUMERIC(res);
+ #else
+ return numeric_sum(fcinfo);
+ #endif
+ }
+
+ Datum
+ int16_avg(PG_FUNCTION_ARGS)
+ {
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+ NumericVar result;
+ Datum countd, sumd;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ /* If there were no non-null inputs, return NULL */
+ if (state == NULL || state->N == 0)
+ PG_RETURN_NULL();
+
+ init_var(&result);
+
+ int16_to_numericvar(state->sumX, &result);
+
+ countd = DirectFunctionCall1(int8_numeric,
+ Int64GetDatumFast(state->N));
+ sumd = NumericGetDatum(make_result(&result));
+
+ free_var(&result);
+
+ PG_RETURN_DATUM(DirectFunctionCall2(numeric_div, sumd, countd));
+ #else
+ return numeric_avg(fcinfo);
+ #endif
+ }
+
+ Datum
numeric_avg(PG_FUNCTION_ARGS)
{
NumericAggState *state;
*************** numeric_stddev_pop(PG_FUNCTION_ARGS)
*** 3155,3160 ****
--- 3409,3529 ----
PG_RETURN_NUMERIC(res);
}
+ #ifdef HAVE_INT128
+ static Numeric
+ int16_stddev_internal(Int16AggState *state,
+ bool variance, bool sample,
+ bool *is_null)
+ {
+ NumericAggState numstate;
+ Numeric res;
+
+ init_var(&numstate.sumX);
+ init_var(&numstate.sumX2);
+ numstate.NaNcount = 0;
+ numstate.agg_context = NULL;
+
+ if (state) {
+ numstate.N = state->N;
+ int16_to_numericvar(state->sumX, &numstate.sumX);
+ int16_to_numericvar(state->sumX2, &numstate.sumX2);
+ } else {
+ numstate.N = 0;
+ }
+
+ res = numeric_stddev_internal(&numstate, variance, sample, is_null);
+
+ free_var(&numstate.sumX);
+ free_var(&numstate.sumX2);
+
+ return res;
+ }
+ #endif
+
+ Datum
+ int16_var_samp(PG_FUNCTION_ARGS)
+ {
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+ Numeric res;
+ bool is_null;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ res = int16_stddev_internal(state, true, true, &is_null);
+
+ if (is_null)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_NUMERIC(res);
+ #else
+ return numeric_var_samp(fcinfo);
+ #endif
+ }
+
+ Datum
+ int16_stddev_samp(PG_FUNCTION_ARGS)
+ {
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+ Numeric res;
+ bool is_null;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ res = int16_stddev_internal(state, false, true, &is_null);
+
+ if (is_null)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_NUMERIC(res);
+ #else
+ return numeric_stddev_samp(fcinfo);
+ #endif
+ }
+
+ Datum
+ int16_var_pop(PG_FUNCTION_ARGS)
+ {
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+ Numeric res;
+ bool is_null;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ res = int16_stddev_internal(state, true, false, &is_null);
+
+ if (is_null)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_NUMERIC(res);
+ #else
+ return numeric_var_pop(fcinfo);
+ #endif
+ }
+
+ Datum
+ int16_stddev_pop(PG_FUNCTION_ARGS)
+ {
+ #ifdef HAVE_INT128
+ Int16AggState *state;
+ Numeric res;
+ bool is_null;
+
+ state = PG_ARGISNULL(0) ? NULL : (Int16AggState *) PG_GETARG_POINTER(0);
+
+ res = int16_stddev_internal(state, false, false, &is_null);
+
+ if (is_null)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_NUMERIC(res);
+ #else
+ return numeric_stddev_pop(fcinfo);
+ #endif
+ }
+
/*
* SUM transition functions for integer datatypes.
*
*************** int8_to_numericvar(int64 val, NumericVar
*** 4377,4382 ****
--- 4746,4798 ----
var->weight = ndigits - 1;
}
+ #ifdef HAVE_INT128
+ /*
+ * Convert 128 bit integer to numeric.
+ */
+ static void
+ int16_to_numericvar(int128 val, NumericVar *var)
+ {
+ uint128 uval,
+ newuval;
+ NumericDigit *ptr;
+ int ndigits;
+
+ /* int16 can require at most 39 decimal digits; add one for safety */
+ alloc_var(var, 40 / DEC_DIGITS);
+ if (val < 0)
+ {
+ var->sign = NUMERIC_NEG;
+ uval = -val;
+ }
+ else
+ {
+ var->sign = NUMERIC_POS;
+ uval = val;
+ }
+ var->dscale = 0;
+ if (val == 0)
+ {
+ var->ndigits = 0;
+ var->weight = 0;
+ return;
+ }
+ ptr = var->digits + var->ndigits;
+ ndigits = 0;
+ do
+ {
+ ptr--;
+ ndigits++;
+ newuval = uval / NBASE;
+ *ptr = uval - newuval * NBASE;
+ uval = newuval;
+ } while (uval);
+ var->digits = ptr;
+ var->ndigits = ndigits;
+ var->weight = ndigits - 1;
+ }
+ #endif
+
/*
* Convert numeric to float8; if out of range, return +/- HUGE_VAL
*/
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
new file mode 100644
index 3ba9e5e..b204bb2
*** a/src/include/catalog/pg_aggregate.h
--- b/src/include/catalog/pg_aggregate.h
*************** typedef FormData_pg_aggregate *Form_pg_a
*** 125,131 ****
*/
/* avg */
! DATA(insert ( 2100 n 0 int8_avg_accum numeric_avg int8_avg_accum int8_accum_inv numeric_avg f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2101 n 0 int4_avg_accum int8_avg int4_avg_accum int4_avg_accum_inv int8_avg f f 0 1016 0 1016 0 "{0,0}" "{0,0}" ));
DATA(insert ( 2102 n 0 int2_avg_accum int8_avg int2_avg_accum int2_avg_accum_inv int8_avg f f 0 1016 0 1016 0 "{0,0}" "{0,0}" ));
DATA(insert ( 2103 n 0 numeric_avg_accum numeric_avg numeric_avg_accum numeric_accum_inv numeric_avg f f 0 2281 128 2281 128 _null_ _null_ ));
--- 125,131 ----
*/
/* avg */
! DATA(insert ( 2100 n 0 int8_avg_accum int16_avg int8_avg_accum int8_avg_accum_inv int16_avg f f 0 2281 48 2281 48 _null_ _null_ ));
DATA(insert ( 2101 n 0 int4_avg_accum int8_avg int4_avg_accum int4_avg_accum_inv int8_avg f f 0 1016 0 1016 0 "{0,0}" "{0,0}" ));
DATA(insert ( 2102 n 0 int2_avg_accum int8_avg int2_avg_accum int2_avg_accum_inv int8_avg f f 0 1016 0 1016 0 "{0,0}" "{0,0}" ));
DATA(insert ( 2103 n 0 numeric_avg_accum numeric_avg numeric_avg_accum numeric_accum_inv numeric_avg f f 0 2281 128 2281 128 _null_ _null_ ));
*************** DATA(insert ( 2105 n 0 float8_accum floa
*** 134,140 ****
DATA(insert ( 2106 n 0 interval_accum interval_avg interval_accum interval_accum_inv interval_avg f f 0 1187 0 1187 0 "{0 second,0 second}" "{0 second,0 second}" ));
/* sum */
! DATA(insert ( 2107 n 0 int8_avg_accum numeric_sum int8_avg_accum int8_accum_inv numeric_sum f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2108 n 0 int4_sum - int4_avg_accum int4_avg_accum_inv int2int4_sum f f 0 20 0 1016 0 _null_ "{0,0}" ));
DATA(insert ( 2109 n 0 int2_sum - int2_avg_accum int2_avg_accum_inv int2int4_sum f f 0 20 0 1016 0 _null_ "{0,0}" ));
DATA(insert ( 2110 n 0 float4pl - - - - f f 0 700 0 0 0 _null_ _null_ ));
--- 134,140 ----
DATA(insert ( 2106 n 0 interval_accum interval_avg interval_accum interval_accum_inv interval_avg f f 0 1187 0 1187 0 "{0 second,0 second}" "{0 second,0 second}" ));
/* sum */
! DATA(insert ( 2107 n 0 int8_avg_accum int16_sum int8_avg_accum int8_avg_accum_inv int16_sum f f 0 2281 48 2281 48 _null_ _null_ ));
DATA(insert ( 2108 n 0 int4_sum - int4_avg_accum int4_avg_accum_inv int2int4_sum f f 0 20 0 1016 0 _null_ "{0,0}" ));
DATA(insert ( 2109 n 0 int2_sum - int2_avg_accum int2_avg_accum_inv int2int4_sum f f 0 20 0 1016 0 _null_ "{0,0}" ));
DATA(insert ( 2110 n 0 float4pl - - - - f f 0 700 0 0 0 _null_ _null_ ));
*************** DATA(insert ( 2803 n 0 int8inc - in
*** 195,242 ****
/* var_pop */
DATA(insert ( 2718 n 0 int8_accum numeric_var_pop int8_accum int8_accum_inv numeric_var_pop f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2719 n 0 int4_accum numeric_var_pop int4_accum int4_accum_inv numeric_var_pop f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2720 n 0 int2_accum numeric_var_pop int2_accum int2_accum_inv numeric_var_pop f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2721 n 0 float4_accum float8_var_pop - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2722 n 0 float8_accum float8_var_pop - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2723 n 0 numeric_accum numeric_var_pop numeric_accum numeric_accum_inv numeric_var_pop f f 0 2281 128 2281 128 _null_ _null_ ));
/* var_samp */
DATA(insert ( 2641 n 0 int8_accum numeric_var_samp int8_accum int8_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2642 n 0 int4_accum numeric_var_samp int4_accum int4_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2643 n 0 int2_accum numeric_var_samp int2_accum int2_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2644 n 0 float4_accum float8_var_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2645 n 0 float8_accum float8_var_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2646 n 0 numeric_accum numeric_var_samp numeric_accum numeric_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
/* variance: historical Postgres syntax for var_samp */
DATA(insert ( 2148 n 0 int8_accum numeric_var_samp int8_accum int8_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2149 n 0 int4_accum numeric_var_samp int4_accum int4_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2150 n 0 int2_accum numeric_var_samp int2_accum int2_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2151 n 0 float4_accum float8_var_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2152 n 0 float8_accum float8_var_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2153 n 0 numeric_accum numeric_var_samp numeric_accum numeric_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
/* stddev_pop */
DATA(insert ( 2724 n 0 int8_accum numeric_stddev_pop int8_accum int8_accum_inv numeric_stddev_pop f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2725 n 0 int4_accum numeric_stddev_pop int4_accum int4_accum_inv numeric_stddev_pop f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2726 n 0 int2_accum numeric_stddev_pop int2_accum int2_accum_inv numeric_stddev_pop f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2727 n 0 float4_accum float8_stddev_pop - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2728 n 0 float8_accum float8_stddev_pop - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2729 n 0 numeric_accum numeric_stddev_pop numeric_accum numeric_accum_inv numeric_stddev_pop f f 0 2281 128 2281 128 _null_ _null_ ));
/* stddev_samp */
DATA(insert ( 2712 n 0 int8_accum numeric_stddev_samp int8_accum int8_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2713 n 0 int4_accum numeric_stddev_samp int4_accum int4_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2714 n 0 int2_accum numeric_stddev_samp int2_accum int2_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2715 n 0 float4_accum float8_stddev_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2716 n 0 float8_accum float8_stddev_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2717 n 0 numeric_accum numeric_stddev_samp numeric_accum numeric_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
/* stddev: historical Postgres syntax for stddev_samp */
DATA(insert ( 2154 n 0 int8_accum numeric_stddev_samp int8_accum int8_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2155 n 0 int4_accum numeric_stddev_samp int4_accum int4_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2156 n 0 int2_accum numeric_stddev_samp int2_accum int2_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2157 n 0 float4_accum float8_stddev_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2158 n 0 float8_accum float8_stddev_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2159 n 0 numeric_accum numeric_stddev_samp numeric_accum numeric_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
--- 195,242 ----
/* var_pop */
DATA(insert ( 2718 n 0 int8_accum numeric_var_pop int8_accum int8_accum_inv numeric_var_pop f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2719 n 0 int4_accum int16_var_pop int4_accum int4_accum_inv int16_var_pop f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2720 n 0 int2_accum int16_var_pop int2_accum int2_accum_inv int16_var_pop f f 0 2281 128 2281 128 _null_ _null_ ));
DATA(insert ( 2721 n 0 float4_accum float8_var_pop - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2722 n 0 float8_accum float8_var_pop - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2723 n 0 numeric_accum numeric_var_pop numeric_accum numeric_accum_inv numeric_var_pop f f 0 2281 128 2281 128 _null_ _null_ ));
/* var_samp */
DATA(insert ( 2641 n 0 int8_accum numeric_var_samp int8_accum int8_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2642 n 0 int4_accum int16_var_samp int4_accum int4_accum_inv int16_var_samp f f 0 2281 48 2281 48 _null_ _null_ ));
! DATA(insert ( 2643 n 0 int2_accum int16_var_samp int2_accum int2_accum_inv int16_var_samp f f 0 2281 48 2281 48 _null_ _null_ ));
DATA(insert ( 2644 n 0 float4_accum float8_var_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2645 n 0 float8_accum float8_var_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2646 n 0 numeric_accum numeric_var_samp numeric_accum numeric_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
/* variance: historical Postgres syntax for var_samp */
DATA(insert ( 2148 n 0 int8_accum numeric_var_samp int8_accum int8_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2149 n 0 int4_accum int16_var_samp int4_accum int4_accum_inv int16_var_samp f f 0 2281 48 2281 48 _null_ _null_ ));
! DATA(insert ( 2150 n 0 int2_accum int16_var_samp int2_accum int2_accum_inv int16_var_samp f f 0 2281 48 2281 48 _null_ _null_ ));
DATA(insert ( 2151 n 0 float4_accum float8_var_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2152 n 0 float8_accum float8_var_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2153 n 0 numeric_accum numeric_var_samp numeric_accum numeric_accum_inv numeric_var_samp f f 0 2281 128 2281 128 _null_ _null_ ));
/* stddev_pop */
DATA(insert ( 2724 n 0 int8_accum numeric_stddev_pop int8_accum int8_accum_inv numeric_stddev_pop f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2725 n 0 int4_accum int16_stddev_pop int4_accum int4_accum_inv int16_stddev_pop f f 0 2281 48 2281 48 _null_ _null_ ));
! DATA(insert ( 2726 n 0 int2_accum int16_stddev_pop int2_accum int2_accum_inv int16_stddev_pop f f 0 2281 48 2281 48 _null_ _null_ ));
DATA(insert ( 2727 n 0 float4_accum float8_stddev_pop - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2728 n 0 float8_accum float8_stddev_pop - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2729 n 0 numeric_accum numeric_stddev_pop numeric_accum numeric_accum_inv numeric_stddev_pop f f 0 2281 128 2281 128 _null_ _null_ ));
/* stddev_samp */
DATA(insert ( 2712 n 0 int8_accum numeric_stddev_samp int8_accum int8_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2713 n 0 int4_accum int16_stddev_samp int4_accum int4_accum_inv int16_stddev_samp f f 0 2281 48 2281 48 _null_ _null_ ));
! DATA(insert ( 2714 n 0 int2_accum int16_stddev_samp int2_accum int2_accum_inv int16_stddev_samp f f 0 2281 48 2281 48 _null_ _null_ ));
DATA(insert ( 2715 n 0 float4_accum float8_stddev_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2716 n 0 float8_accum float8_stddev_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2717 n 0 numeric_accum numeric_stddev_samp numeric_accum numeric_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
/* stddev: historical Postgres syntax for stddev_samp */
DATA(insert ( 2154 n 0 int8_accum numeric_stddev_samp int8_accum int8_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
! DATA(insert ( 2155 n 0 int4_accum int16_stddev_samp int4_accum int4_accum_inv int16_stddev_samp f f 0 2281 48 2281 48 _null_ _null_ ));
! DATA(insert ( 2156 n 0 int2_accum int16_stddev_samp int2_accum int2_accum_inv int16_stddev_samp f f 0 2281 48 2281 48 _null_ _null_ ));
DATA(insert ( 2157 n 0 float4_accum float8_stddev_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2158 n 0 float8_accum float8_stddev_samp - - - f f 0 1022 0 0 0 "{0,0,0}" _null_ ));
DATA(insert ( 2159 n 0 numeric_accum numeric_stddev_samp numeric_accum numeric_accum_inv numeric_stddev_samp f f 0 2281 128 2281 128 _null_ _null_ ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index b6dc1b8..6564f0c
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 3568 ( int4_accum_inv
*** 2455,2460 ****
--- 2455,2462 ----
DESCR("aggregate transition function");
DATA(insert OID = 3569 ( int8_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_ int8_accum_inv _null_ _null_ _null_ ));
DESCR("aggregate transition function");
+ DATA(insert OID = 13569 ( int8_avg_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 20" _null_ _null_ _null_ _null_ int8_avg_accum_inv _null_ _null_ _null_ ));
+ DESCR("aggregate transition function");
DATA(insert OID = 3178 ( numeric_sum PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ numeric_sum _null_ _null_ _null_ ));
DESCR("aggregate final function");
DATA(insert OID = 1837 ( numeric_avg PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ numeric_avg _null_ _null_ _null_ ));
*************** DATA(insert OID = 1841 ( int4_sum P
*** 2473,2478 ****
--- 2475,2494 ----
DESCR("aggregate transition function");
DATA(insert OID = 1842 ( int8_sum PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 1700 "1700 20" _null_ _null_ _null_ _null_ int8_sum _null_ _null_ _null_ ));
DESCR("aggregate transition function");
+
+ DATA(insert OID = 13178 ( int16_sum PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ int16_sum _null_ _null_ _null_ ));
+ DESCR("aggregate final function");
+ DATA(insert OID = 11837 ( int16_avg PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ int16_avg _null_ _null_ _null_ ));
+ DESCR("aggregate final function");
+ DATA(insert OID = 12514 ( int16_var_pop PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ int16_var_pop _null_ _null_ _null_ ));
+ DESCR("aggregate final function");
+ DATA(insert OID = 11838 ( int16_var_samp PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ int16_var_samp _null_ _null_ _null_ ));
+ DESCR("aggregate final function");
+ DATA(insert OID = 12596 ( int16_stddev_pop PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ int16_stddev_pop _null_ _null_ _null_ ));
+ DESCR("aggregate final function");
+ DATA(insert OID = 11839 ( int16_stddev_samp PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 1700 "2281" _null_ _null_ _null_ _null_ int16_stddev_samp _null_ _null_ _null_ ));
+ DESCR("aggregate final function");
+
DATA(insert OID = 1843 ( interval_accum PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1187 "1187 1186" _null_ _null_ _null_ _null_ interval_accum _null_ _null_ _null_ ));
DESCR("aggregate transition function");
DATA(insert OID = 3549 ( interval_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1187 "1187 1186" _null_ _null_ _null_ _null_ interval_accum_inv _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
new file mode 100644
index fb1b4a4..02d2c88
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum numeric_var_pop(PG_FUNCTION
*** 1018,1031 ****
--- 1018,1038 ----
extern Datum numeric_var_samp(PG_FUNCTION_ARGS);
extern Datum numeric_stddev_pop(PG_FUNCTION_ARGS);
extern Datum numeric_stddev_samp(PG_FUNCTION_ARGS);
+ extern Datum int16_var_pop(PG_FUNCTION_ARGS);
+ extern Datum int16_var_samp(PG_FUNCTION_ARGS);
+ extern Datum int16_stddev_pop(PG_FUNCTION_ARGS);
+ extern Datum int16_stddev_samp(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 int16_sum(PG_FUNCTION_ARGS);
extern Datum int2_avg_accum(PG_FUNCTION_ARGS);
extern Datum int4_avg_accum(PG_FUNCTION_ARGS);
extern Datum int2_avg_accum_inv(PG_FUNCTION_ARGS);
extern Datum int4_avg_accum_inv(PG_FUNCTION_ARGS);
+ extern Datum int8_avg_accum_inv(PG_FUNCTION_ARGS);
extern Datum int8_avg(PG_FUNCTION_ARGS);
+ extern Datum int16_avg(PG_FUNCTION_ARGS);
extern Datum int2int4_sum(PG_FUNCTION_ARGS);
extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
extern Datum hash_numeric(PG_FUNCTION_ARGS);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
