Re: [PATCH] Negative Transition Aggregate Functions (WIP)
| От | Tom Lane |
|---|---|
| Тема | Re: [PATCH] Negative Transition Aggregate Functions (WIP) |
| Дата | |
| Msg-id | 4256.1397166746@sss.pgh.pa.us обсуждение исходный текст |
| Ответ на | Re: [PATCH] Negative Transition Aggregate Functions (WIP) (Dean Rasheed <dean.a.rasheed@gmail.com>) |
| Ответы |
Re: [PATCH] Negative Transition Aggregate Functions (WIP)
(Dean Rasheed <dean.a.rasheed@gmail.com>)
|
| Список | pgsql-hackers |
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> I was imagining that firsttrans would only be passed the first value
> to be aggregated, not any previous state, and that it would be illegal
> to specify both an initcond and a firsttrans function.
> The forward transition function would only be called for values after
> the first, by which point the state would be non-null, and so it could
> be made strict in most cases. The same would apply to the invertible
> transition functions, so they wouldn't have to do null counting, which
> in turn would make their state types simpler.
I put together a very fast proof-of-concept patch for this (attached).
It has a valid execution path for an aggregate with initfunc, but I didn't
bother writing the CREATE AGGREGATE support yet. I made sum(int4) work
as you suggest, marking the transfn strict and ripping out int4_sum's
internal support for null inputs. The result seems to be about a 4% or so
improvement in the overall aggregation speed, for a simple "SELECT
sum(int4col) FROM table" query. So from a performance standpoint this
seems only marginally worth doing. The real problem is not that 4% isn't
worth the trouble, it's that AFAICS the only built-in aggregates that
can benefit are sum(int2) and sum(int4). So that looks like a rather
narrow use-case.
You had suggested upthread that we could use this idea to make the
transition functions strict for aggregates using "internal" transition
datatypes, but that does not work because the initfunc would violate
the safety rule that a function returning internal must take at least
one internal-type argument. That puts a pretty strong damper on the
usefulness of the approach, given how many internal-transtype aggregates
we have (and the moving-aggregate case is not going to be better is it?)
So at this point I'm feeling unexcited about the initfunc idea.
Unless it does something really good for the moving-aggregate case,
I think we should drop it.
regards, tom lane
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index fe6dc8a..1ca5c8f 100644
*** a/src/backend/catalog/pg_aggregate.c
--- b/src/backend/catalog/pg_aggregate.c
*************** AggregateCreate(const char *aggName,
*** 390,395 ****
--- 390,396 ----
values[Anum_pg_aggregate_aggfnoid - 1] = ObjectIdGetDatum(procOid);
values[Anum_pg_aggregate_aggkind - 1] = CharGetDatum(aggKind);
values[Anum_pg_aggregate_aggnumdirectargs - 1] = Int16GetDatum(numDirectArgs);
+ values[Anum_pg_aggregate_agginitfn - 1] = ObjectIdGetDatum(InvalidOid);
values[Anum_pg_aggregate_aggtransfn - 1] = ObjectIdGetDatum(transfn);
values[Anum_pg_aggregate_aggfinalfn - 1] = ObjectIdGetDatum(finalfn);
values[Anum_pg_aggregate_aggsortop - 1] = ObjectIdGetDatum(sortop);
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 7e4bca5..2671c49 100644
*** a/src/backend/executor/nodeAgg.c
--- b/src/backend/executor/nodeAgg.c
*************** typedef struct AggStatePerAggData
*** 152,157 ****
--- 152,158 ----
int numTransInputs;
/* Oids of transfer functions */
+ Oid initfn_oid; /* may be InvalidOid */
Oid transfn_oid;
Oid finalfn_oid; /* may be InvalidOid */
*************** typedef struct AggStatePerAggData
*** 160,165 ****
--- 161,167 ----
* corresponding oid is not InvalidOid. Note in particular that fn_strict
* flags are kept here.
*/
+ FmgrInfo initfn;
FmgrInfo transfn;
FmgrInfo finalfn;
*************** typedef struct AggHashEntryData
*** 296,301 ****
--- 298,306 ----
static void initialize_aggregates(AggState *aggstate,
AggStatePerAgg peragg,
AggStatePerGroup pergroup);
+ static void initialize_transition_value(AggState *aggstate,
+ AggStatePerAgg peraggstate,
+ AggStatePerGroup pergroupstate);
static void advance_transition_function(AggState *aggstate,
AggStatePerAgg peraggstate,
AggStatePerGroup pergroupstate);
*************** initialize_aggregates(AggState *aggstate
*** 403,408 ****
--- 408,498 ----
}
/*
+ * Initialize the transition value upon reaching the first non-NULL input(s).
+ *
+ * We use this code when the initcond is NULL and the transfn is strict,
+ * which otherwise would mean the transition value can never become non-null.
+ * If an initfn has been provided, call it on the current input value(s);
+ * otherwise, take the current input value as the new transition value.
+ * (In the latter case, we already checked that this is okay datatype-wise.)
+ */
+ static void
+ initialize_transition_value(AggState *aggstate,
+ AggStatePerAgg peraggstate,
+ AggStatePerGroup pergroupstate)
+ {
+ FunctionCallInfo tfcinfo = &peraggstate->transfn_fcinfo;
+ MemoryContext oldContext;
+
+ if (OidIsValid(peraggstate->initfn_oid))
+ {
+ FunctionCallInfoData fcinfo;
+ int numInitArgs;
+ Datum newVal;
+
+ /* We run the transition functions in per-input-tuple memory context */
+ oldContext = MemoryContextSwitchTo(aggstate->tmpcontext->ecxt_per_tuple_memory);
+
+ /*
+ * Set up an fcinfo for calling the initfn. It takes one fewer
+ * argument than the transfn: the old transValue is not passed, since
+ * by definition it's NULL at this point. We know that the other
+ * transfn arguments are all non-null, so no need to check.
+ */
+ numInitArgs = peraggstate->numTransInputs;
+ InitFunctionCallInfoData(fcinfo, &(peraggstate->initfn),
+ numInitArgs,
+ peraggstate->aggCollation,
+ (void *) aggstate, NULL);
+ /* Skip tfcinfo->arg[0], which would be the transValue */
+ memcpy(fcinfo.arg, tfcinfo->arg + 1, numInitArgs * sizeof(Datum));
+ memset(fcinfo.argnull, 0, numInitArgs * sizeof(bool));
+
+ /* set up aggstate->curperagg for AggGetAggref() */
+ aggstate->curperagg = peraggstate;
+
+ newVal = FunctionCallInvoke(&fcinfo);
+
+ aggstate->curperagg = NULL;
+
+ /*
+ * If pass-by-ref datatype, must copy the new value into aggcontext.
+ * Needn't pfree the prior transValue, since it's NULL.
+ */
+ if (!peraggstate->transtypeByVal && !fcinfo.isnull)
+ {
+ MemoryContextSwitchTo(aggstate->aggcontext);
+ newVal = datumCopy(newVal,
+ peraggstate->transtypeByVal,
+ peraggstate->transtypeLen);
+ }
+
+ pergroupstate->transValue = newVal;
+ pergroupstate->transValueIsNull = fcinfo.isnull;
+
+ MemoryContextSwitchTo(oldContext);
+ }
+ else
+ {
+ /*
+ * No initfn, so use the current input value as the new transValue.
+ *
+ * We must copy the datum into aggcontext if it is pass-by-ref. We do
+ * not need to pfree the old transValue, since it's NULL.
+ */
+ oldContext = MemoryContextSwitchTo(aggstate->aggcontext);
+ pergroupstate->transValue = datumCopy(tfcinfo->arg[1],
+ peraggstate->transtypeByVal,
+ peraggstate->transtypeLen);
+ pergroupstate->transValueIsNull = false;
+ MemoryContextSwitchTo(oldContext);
+ }
+
+ /* Reset state so we won't do this again in the current group */
+ pergroupstate->noTransValue = false;
+ }
+
+ /*
* Given new input value(s), advance the transition function of an aggregate.
*
* The new values (and null flags) have been preloaded into argument positions
*************** advance_transition_function(AggState *ag
*** 438,458 ****
if (pergroupstate->noTransValue)
{
/*
! * transValue has not been initialized. This is the first non-NULL
! * input value. We use it as the initial value for transValue. (We
! * already checked that the agg's input type is binary-compatible
! * with its transtype, so straight copy here is OK.)
! *
! * We must copy the datum into aggcontext if it is pass-by-ref. We
! * do not need to pfree the old transValue, since it's NULL.
*/
! oldContext = MemoryContextSwitchTo(aggstate->aggcontext);
! pergroupstate->transValue = datumCopy(fcinfo->arg[1],
! peraggstate->transtypeByVal,
! peraggstate->transtypeLen);
! pergroupstate->transValueIsNull = false;
! pergroupstate->noTransValue = false;
! MemoryContextSwitchTo(oldContext);
return;
}
if (pergroupstate->transValueIsNull)
--- 528,538 ----
if (pergroupstate->noTransValue)
{
/*
! * We had a NULL initcond, and this is the first non-NULL input
! * value(s). Perform special initialization, using the initfn if
! * any.
*/
! initialize_transition_value(aggstate, peraggstate, pergroupstate);
return;
}
if (pergroupstate->transValueIsNull)
*************** ExecInitAgg(Agg *node, EState *estate, i
*** 1676,1682 ****
Form_pg_aggregate aggform;
Oid aggtranstype;
AclResult aclresult;
! Oid transfn_oid,
finalfn_oid;
Expr *transfnexpr,
*finalfnexpr;
--- 1756,1763 ----
Form_pg_aggregate aggform;
Oid aggtranstype;
AclResult aclresult;
! Oid initfn_oid,
! transfn_oid,
finalfn_oid;
Expr *transfnexpr,
*finalfnexpr;
*************** ExecInitAgg(Agg *node, EState *estate, i
*** 1728,1733 ****
--- 1809,1815 ----
get_func_name(aggref->aggfnoid));
InvokeFunctionExecuteHook(aggref->aggfnoid);
+ peraggstate->initfn_oid = initfn_oid = aggform->agginitfn;
peraggstate->transfn_oid = transfn_oid = aggform->aggtransfn;
peraggstate->finalfn_oid = finalfn_oid = aggform->aggfinalfn;
*************** ExecInitAgg(Agg *node, EState *estate, i
*** 1802,1808 ****
&transfnexpr,
&finalfnexpr);
! /* set up infrastructure for calling the transfn and finalfn */
fmgr_info(transfn_oid, &peraggstate->transfn);
fmgr_info_set_expr((Node *) transfnexpr, &peraggstate->transfn);
--- 1884,1896 ----
&transfnexpr,
&finalfnexpr);
! /* set up infrastructure for calling the aggregate's functions */
! if (OidIsValid(initfn_oid))
! {
! fmgr_info(initfn_oid, &peraggstate->initfn);
! // fmgr_info_set_expr((Node *) initfnexpr, &peraggstate->initfn);
! }
!
fmgr_info(transfn_oid, &peraggstate->transfn);
fmgr_info_set_expr((Node *) transfnexpr, &peraggstate->transfn);
*************** ExecInitAgg(Agg *node, EState *estate, i
*** 1849,1855 ****
* transValue. This should have been checked at agg definition time,
* but just in case...
*/
! if (peraggstate->transfn.fn_strict && peraggstate->initValueIsNull)
{
if (numArguments <= numDirectArgs ||
!IsBinaryCoercible(inputTypes[numDirectArgs], aggtranstype))
--- 1937,1944 ----
* transValue. This should have been checked at agg definition time,
* but just in case...
*/
! if (peraggstate->transfn.fn_strict && peraggstate->initValueIsNull &&
! !OidIsValid(initfn_oid))
{
if (numArguments <= numDirectArgs ||
!IsBinaryCoercible(inputTypes[numDirectArgs], aggtranstype))
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 64eb0f8..cc2a55d 100644
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
*************** int4_sum(PG_FUNCTION_ARGS)
*** 3021,3036 ****
{
int64 newval;
- if (PG_ARGISNULL(0))
- {
- /* No non-null input seen so far... */
- if (PG_ARGISNULL(1))
- PG_RETURN_NULL(); /* still no non-null */
- /* This is the first non-null input. */
- newval = (int64) PG_GETARG_INT32(1);
- PG_RETURN_INT64(newval);
- }
-
/*
* If we're invoked as an aggregate, we can cheat and modify our first
* parameter in-place to avoid palloc overhead. If not, we need to return
--- 3021,3026 ----
*************** int4_sum(PG_FUNCTION_ARGS)
*** 3043,3051 ****
{
int64 *oldsum = (int64 *) PG_GETARG_POINTER(0);
! /* Leave the running sum unchanged in the new input is null */
! if (!PG_ARGISNULL(1))
! *oldsum = *oldsum + (int64) PG_GETARG_INT32(1);
PG_RETURN_POINTER(oldsum);
}
--- 3033,3039 ----
{
int64 *oldsum = (int64 *) PG_GETARG_POINTER(0);
! *oldsum = *oldsum + (int64) PG_GETARG_INT32(1);
PG_RETURN_POINTER(oldsum);
}
*************** int4_sum(PG_FUNCTION_ARGS)
*** 3054,3064 ****
{
int64 oldsum = PG_GETARG_INT64(0);
- /* Leave sum unchanged if new input is null. */
- if (PG_ARGISNULL(1))
- PG_RETURN_INT64(oldsum);
-
- /* OK to do the addition. */
newval = oldsum + (int64) PG_GETARG_INT32(1);
PG_RETURN_INT64(newval);
--- 3042,3047 ----
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index f189998..79d327c 100644
*** a/src/include/catalog/pg_aggregate.h
--- b/src/include/catalog/pg_aggregate.h
***************
*** 30,35 ****
--- 30,36 ----
* aggfnoid pg_proc OID of the aggregate itself
* aggkind aggregate kind, see AGGKIND_ categories below
* aggnumdirectargs number of arguments that are "direct" arguments
+ * agginitfn first-transition function (0 if none)
* aggtransfn transition function
* aggfinalfn final function (0 if none)
* aggsortop associated sort operator (0 if none)
*************** CATALOG(pg_aggregate,2600) BKI_WITHOUT_O
*** 45,50 ****
--- 46,52 ----
regproc aggfnoid;
char aggkind;
int16 aggnumdirectargs;
+ regproc agginitfn;
regproc aggtransfn;
regproc aggfinalfn;
Oid aggsortop;
*************** typedef FormData_pg_aggregate *Form_pg_a
*** 68,83 ****
* ----------------
*/
! #define Natts_pg_aggregate 9
#define Anum_pg_aggregate_aggfnoid 1
#define Anum_pg_aggregate_aggkind 2
#define Anum_pg_aggregate_aggnumdirectargs 3
! #define Anum_pg_aggregate_aggtransfn 4
! #define Anum_pg_aggregate_aggfinalfn 5
! #define Anum_pg_aggregate_aggsortop 6
! #define Anum_pg_aggregate_aggtranstype 7
! #define Anum_pg_aggregate_aggtransspace 8
! #define Anum_pg_aggregate_agginitval 9
/*
* Symbolic values for aggkind column. We distinguish normal aggregates
--- 70,86 ----
* ----------------
*/
! #define Natts_pg_aggregate 10
#define Anum_pg_aggregate_aggfnoid 1
#define Anum_pg_aggregate_aggkind 2
#define Anum_pg_aggregate_aggnumdirectargs 3
! #define Anum_pg_aggregate_agginitfn 4
! #define Anum_pg_aggregate_aggtransfn 5
! #define Anum_pg_aggregate_aggfinalfn 6
! #define Anum_pg_aggregate_aggsortop 7
! #define Anum_pg_aggregate_aggtranstype 8
! #define Anum_pg_aggregate_aggtransspace 9
! #define Anum_pg_aggregate_agginitval 10
/*
* Symbolic values for aggkind column. We distinguish normal aggregates
*************** typedef FormData_pg_aggregate *Form_pg_a
*** 101,277 ****
*/
/* avg */
! DATA(insert ( 2100 n 0 int8_avg_accum numeric_avg 0 2281 128 _null_ ));
! DATA(insert ( 2101 n 0 int4_avg_accum int8_avg 0 1016 0 "{0,0}" ));
! DATA(insert ( 2102 n 0 int2_avg_accum int8_avg 0 1016 0 "{0,0}" ));
! DATA(insert ( 2103 n 0 numeric_avg_accum numeric_avg 0 2281 128 _null_ ));
! DATA(insert ( 2104 n 0 float4_accum float8_avg 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2105 n 0 float8_accum float8_avg 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2106 n 0 interval_accum interval_avg 0 1187 0 "{0 second,0 second}" ));
/* sum */
! DATA(insert ( 2107 n 0 int8_avg_accum numeric_sum 0 2281 128 _null_ ));
! DATA(insert ( 2108 n 0 int4_sum - 0 20 0 _null_ ));
! DATA(insert ( 2109 n 0 int2_sum - 0 20 0 _null_ ));
! DATA(insert ( 2110 n 0 float4pl - 0 700 0 _null_ ));
! DATA(insert ( 2111 n 0 float8pl - 0 701 0 _null_ ));
! DATA(insert ( 2112 n 0 cash_pl - 0 790 0 _null_ ));
! DATA(insert ( 2113 n 0 interval_pl - 0 1186 0 _null_ ));
! DATA(insert ( 2114 n 0 numeric_avg_accum numeric_sum 0 2281 128 _null_ ));
/* max */
! DATA(insert ( 2115 n 0 int8larger - 413 20 0 _null_ ));
! DATA(insert ( 2116 n 0 int4larger - 521 23 0 _null_ ));
! DATA(insert ( 2117 n 0 int2larger - 520 21 0 _null_ ));
! DATA(insert ( 2118 n 0 oidlarger - 610 26 0 _null_ ));
! DATA(insert ( 2119 n 0 float4larger - 623 700 0 _null_ ));
! DATA(insert ( 2120 n 0 float8larger - 674 701 0 _null_ ));
! DATA(insert ( 2121 n 0 int4larger - 563 702 0 _null_ ));
! DATA(insert ( 2122 n 0 date_larger - 1097 1082 0 _null_ ));
! DATA(insert ( 2123 n 0 time_larger - 1112 1083 0 _null_ ));
! DATA(insert ( 2124 n 0 timetz_larger - 1554 1266 0 _null_ ));
! DATA(insert ( 2125 n 0 cashlarger - 903 790 0 _null_ ));
! DATA(insert ( 2126 n 0 timestamp_larger - 2064 1114 0 _null_ ));
! DATA(insert ( 2127 n 0 timestamptz_larger - 1324 1184 0 _null_ ));
! DATA(insert ( 2128 n 0 interval_larger - 1334 1186 0 _null_ ));
! DATA(insert ( 2129 n 0 text_larger - 666 25 0 _null_ ));
! DATA(insert ( 2130 n 0 numeric_larger - 1756 1700 0 _null_ ));
! DATA(insert ( 2050 n 0 array_larger - 1073 2277 0 _null_ ));
! DATA(insert ( 2244 n 0 bpchar_larger - 1060 1042 0 _null_ ));
! DATA(insert ( 2797 n 0 tidlarger - 2800 27 0 _null_ ));
! DATA(insert ( 3526 n 0 enum_larger - 3519 3500 0 _null_ ));
/* min */
! DATA(insert ( 2131 n 0 int8smaller - 412 20 0 _null_ ));
! DATA(insert ( 2132 n 0 int4smaller - 97 23 0 _null_ ));
! DATA(insert ( 2133 n 0 int2smaller - 95 21 0 _null_ ));
! DATA(insert ( 2134 n 0 oidsmaller - 609 26 0 _null_ ));
! DATA(insert ( 2135 n 0 float4smaller - 622 700 0 _null_ ));
! DATA(insert ( 2136 n 0 float8smaller - 672 701 0 _null_ ));
! DATA(insert ( 2137 n 0 int4smaller - 562 702 0 _null_ ));
! DATA(insert ( 2138 n 0 date_smaller - 1095 1082 0 _null_ ));
! DATA(insert ( 2139 n 0 time_smaller - 1110 1083 0 _null_ ));
! DATA(insert ( 2140 n 0 timetz_smaller - 1552 1266 0 _null_ ));
! DATA(insert ( 2141 n 0 cashsmaller - 902 790 0 _null_ ));
! DATA(insert ( 2142 n 0 timestamp_smaller - 2062 1114 0 _null_ ));
! DATA(insert ( 2143 n 0 timestamptz_smaller - 1322 1184 0 _null_ ));
! DATA(insert ( 2144 n 0 interval_smaller - 1332 1186 0 _null_ ));
! DATA(insert ( 2145 n 0 text_smaller - 664 25 0 _null_ ));
! DATA(insert ( 2146 n 0 numeric_smaller - 1754 1700 0 _null_ ));
! DATA(insert ( 2051 n 0 array_smaller - 1072 2277 0 _null_ ));
! DATA(insert ( 2245 n 0 bpchar_smaller - 1058 1042 0 _null_ ));
! DATA(insert ( 2798 n 0 tidsmaller - 2799 27 0 _null_ ));
! DATA(insert ( 3527 n 0 enum_smaller - 3518 3500 0 _null_ ));
/* count */
! DATA(insert ( 2147 n 0 int8inc_any - 0 20 0 "0" ));
! DATA(insert ( 2803 n 0 int8inc - 0 20 0 "0" ));
/* var_pop */
! DATA(insert ( 2718 n 0 int8_accum numeric_var_pop 0 2281 128 _null_ ));
! DATA(insert ( 2719 n 0 int4_accum numeric_var_pop 0 2281 128 _null_ ));
! DATA(insert ( 2720 n 0 int2_accum numeric_var_pop 0 2281 128 _null_ ));
! DATA(insert ( 2721 n 0 float4_accum float8_var_pop 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2722 n 0 float8_accum float8_var_pop 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2723 n 0 numeric_accum numeric_var_pop 0 2281 128 _null_ ));
/* var_samp */
! DATA(insert ( 2641 n 0 int8_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2642 n 0 int4_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2643 n 0 int2_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2644 n 0 float4_accum float8_var_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2645 n 0 float8_accum float8_var_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2646 n 0 numeric_accum numeric_var_samp 0 2281 128 _null_ ));
/* variance: historical Postgres syntax for var_samp */
! DATA(insert ( 2148 n 0 int8_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2149 n 0 int4_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2150 n 0 int2_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2151 n 0 float4_accum float8_var_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2152 n 0 float8_accum float8_var_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2153 n 0 numeric_accum numeric_var_samp 0 2281 128 _null_ ));
/* stddev_pop */
! DATA(insert ( 2724 n 0 int8_accum numeric_stddev_pop 0 2281 128 _null_ ));
! DATA(insert ( 2725 n 0 int4_accum numeric_stddev_pop 0 2281 128 _null_ ));
! DATA(insert ( 2726 n 0 int2_accum numeric_stddev_pop 0 2281 128 _null_ ));
! DATA(insert ( 2727 n 0 float4_accum float8_stddev_pop 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2728 n 0 float8_accum float8_stddev_pop 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2729 n 0 numeric_accum numeric_stddev_pop 0 2281 128 _null_ ));
/* stddev_samp */
! DATA(insert ( 2712 n 0 int8_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2713 n 0 int4_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2714 n 0 int2_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2715 n 0 float4_accum float8_stddev_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2716 n 0 float8_accum float8_stddev_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2717 n 0 numeric_accum numeric_stddev_samp 0 2281 128 _null_ ));
/* stddev: historical Postgres syntax for stddev_samp */
! DATA(insert ( 2154 n 0 int8_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2155 n 0 int4_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2156 n 0 int2_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2157 n 0 float4_accum float8_stddev_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2158 n 0 float8_accum float8_stddev_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2159 n 0 numeric_accum numeric_stddev_samp 0 2281 128 _null_ ));
/* SQL2003 binary regression aggregates */
! DATA(insert ( 2818 n 0 int8inc_float8_float8 - 0 20 0 "0" ));
! DATA(insert ( 2819 n 0 float8_regr_accum float8_regr_sxx 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2820 n 0 float8_regr_accum float8_regr_syy 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2821 n 0 float8_regr_accum float8_regr_sxy 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2822 n 0 float8_regr_accum float8_regr_avgx 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2823 n 0 float8_regr_accum float8_regr_avgy 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2824 n 0 float8_regr_accum float8_regr_r2 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2825 n 0 float8_regr_accum float8_regr_slope 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2826 n 0 float8_regr_accum float8_regr_intercept 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2827 n 0 float8_regr_accum float8_covar_pop 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2828 n 0 float8_regr_accum float8_covar_samp 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2829 n 0 float8_regr_accum float8_corr 0 1022 0 "{0,0,0,0,0,0}" ));
/* boolean-and and boolean-or */
! DATA(insert ( 2517 n 0 booland_statefunc - 58 16 0 _null_ ));
! DATA(insert ( 2518 n 0 boolor_statefunc - 59 16 0 _null_ ));
! DATA(insert ( 2519 n 0 booland_statefunc - 58 16 0 _null_ ));
/* bitwise integer */
! DATA(insert ( 2236 n 0 int2and - 0 21 0 _null_ ));
! DATA(insert ( 2237 n 0 int2or - 0 21 0 _null_ ));
! DATA(insert ( 2238 n 0 int4and - 0 23 0 _null_ ));
! DATA(insert ( 2239 n 0 int4or - 0 23 0 _null_ ));
! DATA(insert ( 2240 n 0 int8and - 0 20 0 _null_ ));
! DATA(insert ( 2241 n 0 int8or - 0 20 0 _null_ ));
! DATA(insert ( 2242 n 0 bitand - 0 1560 0 _null_ ));
! DATA(insert ( 2243 n 0 bitor - 0 1560 0 _null_ ));
/* xml */
! DATA(insert ( 2901 n 0 xmlconcat2 - 0 142 0 _null_ ));
/* array */
! DATA(insert ( 2335 n 0 array_agg_transfn array_agg_finalfn 0 2281 0 _null_ ));
/* text */
! DATA(insert ( 3538 n 0 string_agg_transfn string_agg_finalfn 0 2281 0 _null_ ));
/* bytea */
! DATA(insert ( 3545 n 0 bytea_string_agg_transfn bytea_string_agg_finalfn 0 2281 0 _null_ ));
/* json */
! DATA(insert ( 3175 n 0 json_agg_transfn json_agg_finalfn 0 2281 0 _null_ ));
! DATA(insert ( 3197 n 0 json_object_agg_transfn json_object_agg_finalfn 0 2281 0 _null_ ));
/* ordered-set and hypothetical-set aggregates */
! DATA(insert ( 3972 o 1 ordered_set_transition percentile_disc_final 0 2281 0
_null_ ));
! DATA(insert ( 3974 o 1 ordered_set_transition percentile_cont_float8_final 0 2281 0
_null_));
! DATA(insert ( 3976 o 1 ordered_set_transition percentile_cont_interval_final 0 2281 0
_null_ ));
! DATA(insert ( 3978 o 1 ordered_set_transition percentile_disc_multi_final 0 2281 0
_null_ ));
! DATA(insert ( 3980 o 1 ordered_set_transition percentile_cont_float8_multi_final 0 2281 0
_null_ ));
! DATA(insert ( 3982 o 1 ordered_set_transition percentile_cont_interval_multi_final 0 2281 0
_null_));
! DATA(insert ( 3984 o 0 ordered_set_transition mode_final 0 2281 0
_null_ ));
! DATA(insert ( 3986 h 1 ordered_set_transition_multi rank_final 0 2281 0
_null_));
! DATA(insert ( 3988 h 1 ordered_set_transition_multi percent_rank_final 0 2281 0
_null_));
! DATA(insert ( 3990 h 1 ordered_set_transition_multi cume_dist_final 0 2281 0
_null_ ));
! DATA(insert ( 3992 h 1 ordered_set_transition_multi dense_rank_final 0 2281 0
_null_));
/*
--- 104,280 ----
*/
/* avg */
! DATA(insert ( 2100 n 0 - int8_avg_accum numeric_avg 0 2281 128 _null_ ));
! DATA(insert ( 2101 n 0 - int4_avg_accum int8_avg 0 1016 0 "{0,0}" ));
! DATA(insert ( 2102 n 0 - int2_avg_accum int8_avg 0 1016 0 "{0,0}" ));
! DATA(insert ( 2103 n 0 - numeric_avg_accum numeric_avg 0 2281 128 _null_ ));
! DATA(insert ( 2104 n 0 - float4_accum float8_avg 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2105 n 0 - float8_accum float8_avg 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2106 n 0 - interval_accum interval_avg 0 1187 0 "{0 second,0 second}" ));
/* sum */
! DATA(insert ( 2107 n 0 - int8_avg_accum numeric_sum 0 2281 128 _null_ ));
! DATA(insert ( 2108 n 0 481 int4_sum - 0 20 0 _null_ ));
! DATA(insert ( 2109 n 0 - int2_sum - 0 20 0 _null_ ));
! DATA(insert ( 2110 n 0 - float4pl - 0 700 0 _null_ ));
! DATA(insert ( 2111 n 0 - float8pl - 0 701 0 _null_ ));
! DATA(insert ( 2112 n 0 - cash_pl - 0 790 0 _null_ ));
! DATA(insert ( 2113 n 0 - interval_pl - 0 1186 0 _null_ ));
! DATA(insert ( 2114 n 0 - numeric_avg_accum numeric_sum 0 2281 128 _null_ ));
/* max */
! DATA(insert ( 2115 n 0 - int8larger - 413 20 0 _null_ ));
! DATA(insert ( 2116 n 0 - int4larger - 521 23 0 _null_ ));
! DATA(insert ( 2117 n 0 - int2larger - 520 21 0 _null_ ));
! DATA(insert ( 2118 n 0 - oidlarger - 610 26 0 _null_ ));
! DATA(insert ( 2119 n 0 - float4larger - 623 700 0 _null_ ));
! DATA(insert ( 2120 n 0 - float8larger - 674 701 0 _null_ ));
! DATA(insert ( 2121 n 0 - int4larger - 563 702 0 _null_ ));
! DATA(insert ( 2122 n 0 - date_larger - 1097 1082 0 _null_ ));
! DATA(insert ( 2123 n 0 - time_larger - 1112 1083 0 _null_ ));
! DATA(insert ( 2124 n 0 - timetz_larger - 1554 1266 0 _null_ ));
! DATA(insert ( 2125 n 0 - cashlarger - 903 790 0 _null_ ));
! DATA(insert ( 2126 n 0 - timestamp_larger - 2064 1114 0 _null_ ));
! DATA(insert ( 2127 n 0 - timestamptz_larger - 1324 1184 0 _null_ ));
! DATA(insert ( 2128 n 0 - interval_larger - 1334 1186 0 _null_ ));
! DATA(insert ( 2129 n 0 - text_larger - 666 25 0 _null_ ));
! DATA(insert ( 2130 n 0 - numeric_larger - 1756 1700 0 _null_ ));
! DATA(insert ( 2050 n 0 - array_larger - 1073 2277 0 _null_ ));
! DATA(insert ( 2244 n 0 - bpchar_larger - 1060 1042 0 _null_ ));
! DATA(insert ( 2797 n 0 - tidlarger - 2800 27 0 _null_ ));
! DATA(insert ( 3526 n 0 - enum_larger - 3519 3500 0 _null_ ));
/* min */
! DATA(insert ( 2131 n 0 - int8smaller - 412 20 0 _null_ ));
! DATA(insert ( 2132 n 0 - int4smaller - 97 23 0 _null_ ));
! DATA(insert ( 2133 n 0 - int2smaller - 95 21 0 _null_ ));
! DATA(insert ( 2134 n 0 - oidsmaller - 609 26 0 _null_ ));
! DATA(insert ( 2135 n 0 - float4smaller - 622 700 0 _null_ ));
! DATA(insert ( 2136 n 0 - float8smaller - 672 701 0 _null_ ));
! DATA(insert ( 2137 n 0 - int4smaller - 562 702 0 _null_ ));
! DATA(insert ( 2138 n 0 - date_smaller - 1095 1082 0 _null_ ));
! DATA(insert ( 2139 n 0 - time_smaller - 1110 1083 0 _null_ ));
! DATA(insert ( 2140 n 0 - timetz_smaller - 1552 1266 0 _null_ ));
! DATA(insert ( 2141 n 0 - cashsmaller - 902 790 0 _null_ ));
! DATA(insert ( 2142 n 0 - timestamp_smaller - 2062 1114 0 _null_ ));
! DATA(insert ( 2143 n 0 - timestamptz_smaller - 1322 1184 0 _null_ ));
! DATA(insert ( 2144 n 0 - interval_smaller - 1332 1186 0 _null_ ));
! DATA(insert ( 2145 n 0 - text_smaller - 664 25 0 _null_ ));
! DATA(insert ( 2146 n 0 - numeric_smaller - 1754 1700 0 _null_ ));
! DATA(insert ( 2051 n 0 - array_smaller - 1072 2277 0 _null_ ));
! DATA(insert ( 2245 n 0 - bpchar_smaller - 1058 1042 0 _null_ ));
! DATA(insert ( 2798 n 0 - tidsmaller - 2799 27 0 _null_ ));
! DATA(insert ( 3527 n 0 - enum_smaller - 3518 3500 0 _null_ ));
/* count */
! DATA(insert ( 2147 n 0 - int8inc_any - 0 20 0 "0" ));
! DATA(insert ( 2803 n 0 - int8inc - 0 20 0 "0" ));
/* var_pop */
! DATA(insert ( 2718 n 0 - int8_accum numeric_var_pop 0 2281 128 _null_ ));
! DATA(insert ( 2719 n 0 - int4_accum numeric_var_pop 0 2281 128 _null_ ));
! DATA(insert ( 2720 n 0 - int2_accum numeric_var_pop 0 2281 128 _null_ ));
! DATA(insert ( 2721 n 0 - float4_accum float8_var_pop 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2722 n 0 - float8_accum float8_var_pop 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2723 n 0 - numeric_accum numeric_var_pop 0 2281 128 _null_ ));
/* var_samp */
! DATA(insert ( 2641 n 0 - int8_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2642 n 0 - int4_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2643 n 0 - int2_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2644 n 0 - float4_accum float8_var_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2645 n 0 - float8_accum float8_var_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2646 n 0 - numeric_accum numeric_var_samp 0 2281 128 _null_ ));
/* variance: historical Postgres syntax for var_samp */
! DATA(insert ( 2148 n 0 - int8_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2149 n 0 - int4_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2150 n 0 - int2_accum numeric_var_samp 0 2281 128 _null_ ));
! DATA(insert ( 2151 n 0 - float4_accum float8_var_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2152 n 0 - float8_accum float8_var_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2153 n 0 - numeric_accum numeric_var_samp 0 2281 128 _null_ ));
/* stddev_pop */
! DATA(insert ( 2724 n 0 - int8_accum numeric_stddev_pop 0 2281 128 _null_ ));
! DATA(insert ( 2725 n 0 - int4_accum numeric_stddev_pop 0 2281 128 _null_ ));
! DATA(insert ( 2726 n 0 - int2_accum numeric_stddev_pop 0 2281 128 _null_ ));
! DATA(insert ( 2727 n 0 - float4_accum float8_stddev_pop 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2728 n 0 - float8_accum float8_stddev_pop 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2729 n 0 - numeric_accum numeric_stddev_pop 0 2281 128 _null_ ));
/* stddev_samp */
! DATA(insert ( 2712 n 0 - int8_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2713 n 0 - int4_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2714 n 0 - int2_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2715 n 0 - float4_accum float8_stddev_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2716 n 0 - float8_accum float8_stddev_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2717 n 0 - numeric_accum numeric_stddev_samp 0 2281 128 _null_ ));
/* stddev: historical Postgres syntax for stddev_samp */
! DATA(insert ( 2154 n 0 - int8_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2155 n 0 - int4_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2156 n 0 - int2_accum numeric_stddev_samp 0 2281 128 _null_ ));
! DATA(insert ( 2157 n 0 - float4_accum float8_stddev_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2158 n 0 - float8_accum float8_stddev_samp 0 1022 0 "{0,0,0}" ));
! DATA(insert ( 2159 n 0 - numeric_accum numeric_stddev_samp 0 2281 128 _null_ ));
/* SQL2003 binary regression aggregates */
! DATA(insert ( 2818 n 0 - int8inc_float8_float8 - 0 20 0 "0" ));
! DATA(insert ( 2819 n 0 - float8_regr_accum float8_regr_sxx 0 1022 0 "{0,0,0,0,0,0}"
));
! DATA(insert ( 2820 n 0 - float8_regr_accum float8_regr_syy 0 1022 0 "{0,0,0,0,0,0}"
));
! DATA(insert ( 2821 n 0 - float8_regr_accum float8_regr_sxy 0 1022 0 "{0,0,0,0,0,0}"
));
! DATA(insert ( 2822 n 0 - float8_regr_accum float8_regr_avgx 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2823 n 0 - float8_regr_accum float8_regr_avgy 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2824 n 0 - float8_regr_accum float8_regr_r2 0 1022 0 "{0,0,0,0,0,0}"
));
! DATA(insert ( 2825 n 0 - float8_regr_accum float8_regr_slope 0 1022 0 "{0,0,0,0,0,0}"
));
! DATA(insert ( 2826 n 0 - float8_regr_accum float8_regr_intercept 0 1022 0 "{0,0,0,0,0,0}"
));
! DATA(insert ( 2827 n 0 - float8_regr_accum float8_covar_pop 0 1022 0 "{0,0,0,0,0,0}" ));
! DATA(insert ( 2828 n 0 - float8_regr_accum float8_covar_samp 0 1022 0 "{0,0,0,0,0,0}"
));
! DATA(insert ( 2829 n 0 - float8_regr_accum float8_corr 0 1022 0 "{0,0,0,0,0,0}"
));
/* boolean-and and boolean-or */
! DATA(insert ( 2517 n 0 - booland_statefunc - 58 16 0 _null_ ));
! DATA(insert ( 2518 n 0 - boolor_statefunc - 59 16 0 _null_ ));
! DATA(insert ( 2519 n 0 - booland_statefunc - 58 16 0 _null_ ));
/* bitwise integer */
! DATA(insert ( 2236 n 0 - int2and - 0 21 0 _null_ ));
! DATA(insert ( 2237 n 0 - int2or - 0 21 0 _null_ ));
! DATA(insert ( 2238 n 0 - int4and - 0 23 0 _null_ ));
! DATA(insert ( 2239 n 0 - int4or - 0 23 0 _null_ ));
! DATA(insert ( 2240 n 0 - int8and - 0 20 0 _null_ ));
! DATA(insert ( 2241 n 0 - int8or - 0 20 0 _null_ ));
! DATA(insert ( 2242 n 0 - bitand - 0 1560 0 _null_ ));
! DATA(insert ( 2243 n 0 - bitor - 0 1560 0 _null_ ));
/* xml */
! DATA(insert ( 2901 n 0 - xmlconcat2 - 0 142 0 _null_ ));
/* array */
! DATA(insert ( 2335 n 0 - array_agg_transfn array_agg_finalfn 0 2281 0 _null_ ));
/* text */
! DATA(insert ( 3538 n 0 - string_agg_transfn string_agg_finalfn 0 2281 0 _null_ ));
/* bytea */
! DATA(insert ( 3545 n 0 - bytea_string_agg_transfn bytea_string_agg_finalfn 0 2281 0 _null_
));
/* json */
! DATA(insert ( 3175 n 0 - json_agg_transfn json_agg_finalfn 0 2281 0 _null_ ));
! DATA(insert ( 3197 n 0 - json_object_agg_transfn json_object_agg_finalfn 0 2281 0 _null_
));
/* ordered-set and hypothetical-set aggregates */
! DATA(insert ( 3972 o 1 - ordered_set_transition percentile_disc_final 0
2281 0 _null_ ));
! DATA(insert ( 3974 o 1 - ordered_set_transition percentile_cont_float8_final 0
2281 0 _null_ ));
! DATA(insert ( 3976 o 1 - ordered_set_transition percentile_cont_interval_final 0
2281 0 _null_ ));
! DATA(insert ( 3978 o 1 - ordered_set_transition percentile_disc_multi_final 0
2281 0 _null_ ));
! DATA(insert ( 3980 o 1 - ordered_set_transition percentile_cont_float8_multi_final 0
2281 0 _null_ ));
! DATA(insert ( 3982 o 1 - ordered_set_transition percentile_cont_interval_multi_final 0
2281 0 _null_ ));
! DATA(insert ( 3984 o 0 - ordered_set_transition mode_final 0
2281 0 _null_ ));
! DATA(insert ( 3986 h 1 - ordered_set_transition_multi rank_final 0
2281 0 _null_ ));
! DATA(insert ( 3988 h 1 - ordered_set_transition_multi percent_rank_final 0
2281 0 _null_ ));
! DATA(insert ( 3990 h 1 - ordered_set_transition_multi cume_dist_final 0
2281 0 _null_ ));
! DATA(insert ( 3992 h 1 - ordered_set_transition_multi dense_rank_final 0 2281
0 _null_ ));
/*
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 7b9c587..0bcb76d 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 1839 ( numeric_stddev
*** 2445,2451 ****
DESCR("aggregate final function");
DATA(insert OID = 1840 ( int2_sum PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 20 "20 21" _null_ _null_ _null_
_null_int2_sum _null_ _null_ _null_ ));
DESCR("aggregate transition function");
! DATA(insert OID = 1841 ( int4_sum PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 20 "20 23" _null_ _null_ _null_
_null_int4_sum _null_ _null_ _null_ ));
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");
--- 2445,2451 ----
DESCR("aggregate final function");
DATA(insert OID = 1840 ( int2_sum PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 20 "20 21" _null_ _null_ _null_
_null_int2_sum _null_ _null_ _null_ ));
DESCR("aggregate transition function");
! DATA(insert OID = 1841 ( int4_sum PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 20 "20 23" _null_ _null_ _null_
_null_int4_sum _null_ _null_ _null_ ));
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");
В списке pgsql-hackers по дате отправления:
Следующее
От: Florian PflugДата:
Сообщение: Re: [PATCH] Negative Transition Aggregate Functions (WIP)