Hi, In the thread [1], there we couple of opinions [2,3] to make transition states internal. Since it is a separate topic from the overflow bug, so here is a separate thread.
Attached is a draft implementation for the two families that still keep their transition state in an int8[]: avg(int2), avg(int4). These aggregates' transition type is now declared as INTERNAL that doesn't correspond to any SQL data type and can't be called outside. This is kinda of the continuation of 69c8fbac201, which did the same thing for the numeric aggregates. After this patch only float8 built-in aggregates still keep their transition state in an array. Does it make sense to fold float8 into this patch, or keep it separate? [1] https://www.postgresql.org/message-id/1ddc2a6f-4b26-43d9-9f3b-5b5db98a486d%40gmail.com [2] https://www.postgresql.org/message-id/lbvjl22swi3p3sf7nmmrlx2cq5aa55ry3c5ore2rpylsmi6s5j%40xrrahkipnsce [3] https://www.postgresql.org/message-id/1412049.1788396306%40sss.pgh.pa.us -- regards, Andrei Lepikhov, pgEdge
From 5993ce7e528cee80fe598936ef1137fb934314e1 Mon Sep 17 00:00:00 2001 From: Andrei Lepikhov <[email protected]> Date: Fri, 4 Sep 2026 15:33:47 +0200 Subject: [PATCH v0] Make the transition state of avg(int2)/avg(int4)/sum(int2)/sum(int4) internal Commit 69c8fbac201 declared the transition state of the numeric aggregates INTERNAL, on the grounds that it does not correspond to any SQL data type. avg(int2), avg(int4) and the moving-aggregate mode of sum(int2)/sum(int4) were left behind: they still keep count and sum in a two-element int8[], which is a value anybody can construct and pass in. Supporting that costs something in every transition call: the argument may be toasted, the array may contain NULLs, its length has to be checked, and the state cannot be modified in place unless we first establish that we really are inside an aggregate and buys nothing, since no caller has any reason to build such a state by hand. So declare the transition type INTERNAL and keep count and sum in a plain struct. Add int4_avg_serialize()/int4_avg_deserialize() so that two-phase aggregation, and with it parallel and partitionwise aggregation, keeps working. Note that this makes int2_avg_accum, int4_avg_accum, their inverses, int4_avg_combine, int8_avg and int2int4_sum unusable in a user-defined aggregate declared with stype = int8[]. The two in-tree examples that did so now declare stype = internal instead. --- src/backend/utils/adt/numeric.c | 291 +++++++++++------- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_aggregate.dat | 14 +- src/include/catalog/pg_proc.dat | 34 +- .../test_pg_dump/expected/test_pg_dump.out | 5 +- .../modules/test_pg_dump/sql/test_pg_dump.sql | 5 +- src/test/regress/expected/aggregates.out | 65 ++++ .../regress/expected/create_aggregate.out | 5 +- src/test/regress/sql/aggregates.sql | 23 ++ src/test/regress/sql/create_aggregate.sql | 5 +- 10 files changed, 301 insertions(+), 148 deletions(-) diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c index 37f24e33857..84e88d0bde6 100644 --- a/src/backend/utils/adt/numeric.c +++ b/src/backend/utils/adt/numeric.c @@ -6437,206 +6437,267 @@ int8_sum(PG_FUNCTION_ARGS) /* - * Routines for avg(int2) and avg(int4). The transition datatype - * is a two-element int8 array, holding count and sum. - * - * These functions are also used for sum(int2) and sum(int4) when - * operating in moving-aggregate mode, since for correct inverse transitions - * we need to count the inputs. + * Routines for avg(int2) and avg(int4), and for the moving-aggregate mode of + * sum(int2) and sum(int4), which needs the input count for inverse + * transitions. The transition state is declared "internal" so that it cannot + * be forged from SQL; the transition functions are therefore not strict and + * must build the state themselves on first call. */ - typedef struct Int8TransTypeData { int64 count; int64 sum; } Int8TransTypeData; +/* + * Prepare state data for an aggregate function that needs to compute the sum + * and count of int2 or int4 inputs. + */ +static Int8TransTypeData * +makeInt8TransTypeData(FunctionCallInfo fcinfo) +{ + Int8TransTypeData *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 = palloc0_object(Int8TransTypeData); + + MemoryContextSwitchTo(old_context); + + return state; +} + +/* + * Transition function for int2 input. + */ Datum int2_avg_accum(PG_FUNCTION_ARGS) { - ArrayType *transarray; - int16 newval = PG_GETARG_INT16(1); - Int8TransTypeData *transdata; + Int8TransTypeData *state; - /* - * If we're invoked as an aggregate, we can cheat and modify our first - * parameter in-place to reduce palloc overhead. Otherwise we need to make - * a copy of it before scribbling on it. - */ - if (AggCheckCallContext(fcinfo, NULL)) - transarray = PG_GETARG_ARRAYTYPE_P(0); - else - transarray = PG_GETARG_ARRAYTYPE_P_COPY(0); + state = PG_ARGISNULL(0) ? NULL : (Int8TransTypeData *) PG_GETARG_POINTER(0); - if (ARR_HASNULL(transarray) || - ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData)) - elog(ERROR, "expected 2-element int8 array"); + /* Create the state data on the first call */ + if (state == NULL) + state = makeInt8TransTypeData(fcinfo); - transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray); - transdata->count++; - transdata->sum += newval; + if (!PG_ARGISNULL(1)) + { + state->count++; + state->sum += PG_GETARG_INT16(1); + } - PG_RETURN_ARRAYTYPE_P(transarray); + PG_RETURN_POINTER(state); } +/* + * Transition function for int4 input. + */ Datum int4_avg_accum(PG_FUNCTION_ARGS) { - ArrayType *transarray; - int32 newval = PG_GETARG_INT32(1); - Int8TransTypeData *transdata; + Int8TransTypeData *state; - /* - * If we're invoked as an aggregate, we can cheat and modify our first - * parameter in-place to reduce palloc overhead. Otherwise we need to make - * a copy of it before scribbling on it. - */ - if (AggCheckCallContext(fcinfo, NULL)) - transarray = PG_GETARG_ARRAYTYPE_P(0); - else - transarray = PG_GETARG_ARRAYTYPE_P_COPY(0); + state = PG_ARGISNULL(0) ? NULL : (Int8TransTypeData *) PG_GETARG_POINTER(0); - if (ARR_HASNULL(transarray) || - ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData)) - elog(ERROR, "expected 2-element int8 array"); + /* Create the state data on the first call */ + if (state == NULL) + state = makeInt8TransTypeData(fcinfo); - transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray); - transdata->count++; - transdata->sum += newval; + if (!PG_ARGISNULL(1)) + { + state->count++; + state->sum += PG_GETARG_INT32(1); + } - PG_RETURN_ARRAYTYPE_P(transarray); + PG_RETURN_POINTER(state); } +/* + * Combine function for Int8TransTypeData. + */ Datum int4_avg_combine(PG_FUNCTION_ARGS) { - ArrayType *transarray1; - ArrayType *transarray2; Int8TransTypeData *state1; Int8TransTypeData *state2; if (!AggCheckCallContext(fcinfo, NULL)) elog(ERROR, "aggregate function called in non-aggregate context"); - transarray1 = PG_GETARG_ARRAYTYPE_P(0); - transarray2 = PG_GETARG_ARRAYTYPE_P(1); + state1 = PG_ARGISNULL(0) ? NULL : (Int8TransTypeData *) PG_GETARG_POINTER(0); + state2 = PG_ARGISNULL(1) ? NULL : (Int8TransTypeData *) PG_GETARG_POINTER(1); - if (ARR_HASNULL(transarray1) || - ARR_SIZE(transarray1) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData)) - elog(ERROR, "expected 2-element int8 array"); + if (state2 == NULL) + { + /* + * NULL state2 is easy, just return state1, which we know is already + * in the agg_context + */ + if (state1 == NULL) + PG_RETURN_NULL(); + PG_RETURN_POINTER(state1); + } - if (ARR_HASNULL(transarray2) || - ARR_SIZE(transarray2) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData)) - elog(ERROR, "expected 2-element int8 array"); + /* copy state2 into a fresh state in the agg_context */ + if (state1 == NULL) + { + state1 = makeInt8TransTypeData(fcinfo); + *state1 = *state2; - state1 = (Int8TransTypeData *) ARR_DATA_PTR(transarray1); - state2 = (Int8TransTypeData *) ARR_DATA_PTR(transarray2); + PG_RETURN_POINTER(state1); + } state1->count += state2->count; state1->sum += state2->sum; - PG_RETURN_ARRAYTYPE_P(transarray1); + PG_RETURN_POINTER(state1); +} + +/* + * int4_avg_serialize + * Serialize Int8TransTypeData into bytea. Shared by avg(int2) and + * avg(int4), whose states are identical. + */ +Datum +int4_avg_serialize(PG_FUNCTION_ARGS) +{ + Int8TransTypeData *state; + StringInfoData buf; + bytea *result; + + /* Ensure we disallow calling when not in aggregate context */ + if (!AggCheckCallContext(fcinfo, NULL)) + elog(ERROR, "aggregate function called in non-aggregate context"); + + state = (Int8TransTypeData *) PG_GETARG_POINTER(0); + + pq_begintypsend(&buf); + + pq_sendint64(&buf, state->count); + pq_sendint64(&buf, state->sum); + + result = pq_endtypsend(&buf); + + PG_RETURN_BYTEA_P(result); +} + +/* + * int4_avg_deserialize + * Deserialize Int8TransTypeData from bytea. Shared by avg(int2) and + * avg(int4), whose states are identical. + */ +Datum +int4_avg_deserialize(PG_FUNCTION_ARGS) +{ + bytea *sstate; + Int8TransTypeData *result; + StringInfoData buf; + + if (!AggCheckCallContext(fcinfo, NULL)) + elog(ERROR, "aggregate function called in non-aggregate context"); + + sstate = PG_GETARG_BYTEA_PP(0); + + initReadOnlyStringInfo(&buf, VARDATA_ANY(sstate), + VARSIZE_ANY_EXHDR(sstate)); + + result = palloc_object(Int8TransTypeData); + + result->count = pq_getmsgint64(&buf); + result->sum = pq_getmsgint64(&buf); + + pq_getmsgend(&buf); + + PG_RETURN_POINTER(result); } Datum int2_avg_accum_inv(PG_FUNCTION_ARGS) { - ArrayType *transarray; - int16 newval = PG_GETARG_INT16(1); - Int8TransTypeData *transdata; + Int8TransTypeData *state; - /* - * If we're invoked as an aggregate, we can cheat and modify our first - * parameter in-place to reduce palloc overhead. Otherwise we need to make - * a copy of it before scribbling on it. - */ - if (AggCheckCallContext(fcinfo, NULL)) - transarray = PG_GETARG_ARRAYTYPE_P(0); - else - transarray = PG_GETARG_ARRAYTYPE_P_COPY(0); + state = PG_ARGISNULL(0) ? NULL : (Int8TransTypeData *) PG_GETARG_POINTER(0); - if (ARR_HASNULL(transarray) || - ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData)) - elog(ERROR, "expected 2-element int8 array"); + /* Should not get here with no state */ + if (state == NULL) + elog(ERROR, "int2_avg_accum_inv called with NULL state"); - transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray); - transdata->count--; - transdata->sum -= newval; + if (!PG_ARGISNULL(1)) + { + state->count--; + state->sum -= PG_GETARG_INT16(1); + } - PG_RETURN_ARRAYTYPE_P(transarray); + PG_RETURN_POINTER(state); } Datum int4_avg_accum_inv(PG_FUNCTION_ARGS) { - ArrayType *transarray; - int32 newval = PG_GETARG_INT32(1); - Int8TransTypeData *transdata; + Int8TransTypeData *state; - /* - * If we're invoked as an aggregate, we can cheat and modify our first - * parameter in-place to reduce palloc overhead. Otherwise we need to make - * a copy of it before scribbling on it. - */ - if (AggCheckCallContext(fcinfo, NULL)) - transarray = PG_GETARG_ARRAYTYPE_P(0); - else - transarray = PG_GETARG_ARRAYTYPE_P_COPY(0); + state = PG_ARGISNULL(0) ? NULL : (Int8TransTypeData *) PG_GETARG_POINTER(0); - if (ARR_HASNULL(transarray) || - ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData)) - elog(ERROR, "expected 2-element int8 array"); + /* Should not get here with no state */ + if (state == NULL) + elog(ERROR, "int4_avg_accum_inv called with NULL state"); - transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray); - transdata->count--; - transdata->sum -= newval; + if (!PG_ARGISNULL(1)) + { + state->count--; + state->sum -= PG_GETARG_INT32(1); + } - PG_RETURN_ARRAYTYPE_P(transarray); + PG_RETURN_POINTER(state); } Datum int8_avg(PG_FUNCTION_ARGS) { - ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0); - Int8TransTypeData *transdata; + Int8TransTypeData *state; Datum countd, sumd; - if (ARR_HASNULL(transarray) || - ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData)) - elog(ERROR, "expected 2-element int8 array"); - transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray); + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); + + state = (Int8TransTypeData *) PG_GETARG_POINTER(0); /* SQL defines AVG of no values to be NULL */ - if (transdata->count == 0) + if (state->count == 0) PG_RETURN_NULL(); - countd = NumericGetDatum(int64_to_numeric(transdata->count)); - sumd = NumericGetDatum(int64_to_numeric(transdata->sum)); + countd = NumericGetDatum(int64_to_numeric(state->count)); + sumd = NumericGetDatum(int64_to_numeric(state->sum)); PG_RETURN_DATUM(DirectFunctionCall2(numeric_div, sumd, countd)); } /* - * SUM(int2) and SUM(int4) both return int8, so we can use this - * final function for both. + * SUM(int2) and SUM(int4) both return int8, so we can use this final function + * for both. */ Datum int2int4_sum(PG_FUNCTION_ARGS) { - ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0); - Int8TransTypeData *transdata; + Int8TransTypeData *state; + + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); - if (ARR_HASNULL(transarray) || - ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData)) - elog(ERROR, "expected 2-element int8 array"); - transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray); + state = (Int8TransTypeData *) PG_GETARG_POINTER(0); /* SQL defines SUM of no values to be NULL */ - if (transdata->count == 0) + if (state->count == 0) PG_RETURN_NULL(); - PG_RETURN_DATUM(Int64GetDatumFast(transdata->sum)); + PG_RETURN_DATUM(Int64GetDatumFast(state->sum)); } diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index f11e244899e..82a53884656 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202608271 +#define CATALOG_VERSION_NO 202609031 #endif diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat index 7bce36ac9c4..e48484c2fbc 100644 --- a/src/include/catalog/pg_aggregate.dat +++ b/src/include/catalog/pg_aggregate.dat @@ -21,14 +21,16 @@ aggtransspace => '48', aggmtranstype => 'internal', aggmtransspace => '48' }, { aggfnoid => 'avg(int4)', aggtransfn => 'int4_avg_accum', aggfinalfn => 'int8_avg', aggcombinefn => 'int4_avg_combine', + aggserialfn => 'int4_avg_serialize', aggdeserialfn => 'int4_avg_deserialize', aggmtransfn => 'int4_avg_accum', aggminvtransfn => 'int4_avg_accum_inv', - aggmfinalfn => 'int8_avg', aggtranstype => '_int8', aggmtranstype => '_int8', - agginitval => '{0,0}', aggminitval => '{0,0}' }, + aggmfinalfn => 'int8_avg', aggtranstype => 'internal', + aggmtranstype => 'internal', aggtransspace => '16', aggmtransspace => '16' }, { aggfnoid => 'avg(int2)', aggtransfn => 'int2_avg_accum', aggfinalfn => 'int8_avg', aggcombinefn => 'int4_avg_combine', + aggserialfn => 'int4_avg_serialize', aggdeserialfn => 'int4_avg_deserialize', aggmtransfn => 'int2_avg_accum', aggminvtransfn => 'int2_avg_accum_inv', - aggmfinalfn => 'int8_avg', aggtranstype => '_int8', aggmtranstype => '_int8', - agginitval => '{0,0}', aggminitval => '{0,0}' }, + aggmfinalfn => 'int8_avg', aggtranstype => 'internal', + aggmtranstype => 'internal', aggtransspace => '16', aggmtransspace => '16' }, { aggfnoid => 'avg(numeric)', aggtransfn => 'numeric_avg_accum', aggfinalfn => 'numeric_avg', aggcombinefn => 'numeric_avg_combine', aggserialfn => 'numeric_avg_serialize', @@ -62,11 +64,11 @@ { aggfnoid => 'sum(int4)', aggtransfn => 'int4_sum', aggcombinefn => 'int8pl', aggmtransfn => 'int4_avg_accum', aggminvtransfn => 'int4_avg_accum_inv', aggmfinalfn => 'int2int4_sum', aggtranstype => 'int8', - aggmtranstype => '_int8', aggminitval => '{0,0}' }, + aggmtranstype => 'internal', aggmtransspace => '16' }, { aggfnoid => 'sum(int2)', aggtransfn => 'int2_sum', aggcombinefn => 'int8pl', aggmtransfn => 'int2_avg_accum', aggminvtransfn => 'int2_avg_accum_inv', aggmfinalfn => 'int2int4_sum', aggtranstype => 'int8', - aggmtranstype => '_int8', aggminitval => '{0,0}' }, + aggmtranstype => 'internal', aggmtransspace => '16' }, { aggfnoid => 'sum(float4)', aggtransfn => 'float4pl', aggcombinefn => 'float4pl', aggtranstype => 'float4' }, { aggfnoid => 'sum(float8)', aggtransfn => 'float8pl', diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 6979c7d1161..af581c4ff96 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5068,8 +5068,14 @@ proname => 'int8_avg_deserialize', prorettype => 'internal', proargtypes => 'bytea internal', prosrc => 'int8_avg_deserialize' }, { oid => '3324', descr => 'aggregate combine function', - proname => 'int4_avg_combine', prorettype => '_int8', - proargtypes => '_int8 _int8', prosrc => 'int4_avg_combine' }, + proname => 'int4_avg_combine', proisstrict => 'f', prorettype => 'internal', + proargtypes => 'internal internal', prosrc => 'int4_avg_combine' }, +{ oid => '9802', descr => 'aggregate serial function', + proname => 'int4_avg_serialize', prorettype => 'bytea', + proargtypes => 'internal', prosrc => 'int4_avg_serialize' }, +{ oid => '9803', descr => 'aggregate deserial function', + proname => 'int4_avg_deserialize', prorettype => 'internal', + proargtypes => 'bytea internal', prosrc => 'int4_avg_deserialize' }, { oid => '3178', descr => 'aggregate final function', proname => 'numeric_sum', proisstrict => 'f', prorettype => 'numeric', proargtypes => 'internal', prosrc => 'numeric_sum' }, @@ -5144,23 +5150,23 @@ proname => 'interval_sum', proisstrict => 'f', prorettype => 'interval', proargtypes => 'internal', prosrc => 'interval_sum' }, { oid => '1962', descr => 'aggregate transition function', - proname => 'int2_avg_accum', prorettype => '_int8', - proargtypes => '_int8 int2', prosrc => 'int2_avg_accum' }, + proname => 'int2_avg_accum', proisstrict => 'f', prorettype => 'internal', + proargtypes => 'internal int2', prosrc => 'int2_avg_accum' }, { oid => '1963', descr => 'aggregate transition function', - proname => 'int4_avg_accum', prorettype => '_int8', - proargtypes => '_int8 int4', prosrc => 'int4_avg_accum' }, + proname => 'int4_avg_accum', proisstrict => 'f', prorettype => 'internal', + proargtypes => 'internal int4', prosrc => 'int4_avg_accum' }, { oid => '3570', descr => 'aggregate transition function', - proname => 'int2_avg_accum_inv', prorettype => '_int8', - proargtypes => '_int8 int2', prosrc => 'int2_avg_accum_inv' }, + proname => 'int2_avg_accum_inv', proisstrict => 'f', prorettype => 'internal', + proargtypes => 'internal int2', prosrc => 'int2_avg_accum_inv' }, { oid => '3571', descr => 'aggregate transition function', - proname => 'int4_avg_accum_inv', prorettype => '_int8', - proargtypes => '_int8 int4', prosrc => 'int4_avg_accum_inv' }, + proname => 'int4_avg_accum_inv', proisstrict => 'f', prorettype => 'internal', + proargtypes => 'internal int4', prosrc => 'int4_avg_accum_inv' }, { oid => '1964', descr => 'aggregate final function', - proname => 'int8_avg', prorettype => 'numeric', proargtypes => '_int8', - prosrc => 'int8_avg' }, + proname => 'int8_avg', proisstrict => 'f', prorettype => 'numeric', + proargtypes => 'internal', prosrc => 'int8_avg' }, { oid => '3572', descr => 'aggregate final function', - proname => 'int2int4_sum', prorettype => 'int8', proargtypes => '_int8', - prosrc => 'int2int4_sum' }, + proname => 'int2int4_sum', proisstrict => 'f', prorettype => 'int8', + proargtypes => 'internal', prosrc => 'int2int4_sum' }, { oid => '2805', descr => 'aggregate transition function', proname => 'int8inc_float8_float8', prorettype => 'int8', proargtypes => 'int8 float8 float8', prosrc => 'int8inc_float8_float8' }, diff --git a/src/test/modules/test_pg_dump/expected/test_pg_dump.out b/src/test/modules/test_pg_dump/expected/test_pg_dump.out index 98c9cd481e7..2fe170c56b4 100644 --- a/src/test/modules/test_pg_dump/expected/test_pg_dump.out +++ b/src/test/modules/test_pg_dump/expected/test_pg_dump.out @@ -9,9 +9,8 @@ CREATE MATERIALIZED VIEW test_pg_dump_mv1 AS SELECT * FROM test_pg_dump_t1; CREATE SCHEMA test_pg_dump_s1; CREATE TYPE test_pg_dump_e1 AS ENUM ('abc', 'def'); CREATE AGGREGATE newavg ( - sfunc = int4_avg_accum, basetype = int4, stype = _int8, - finalfunc = int8_avg, - initcond1 = '{0,0}' + sfunc = int4_avg_accum, basetype = int4, stype = internal, + finalfunc = int8_avg ); CREATE FUNCTION test_pg_dump(int) RETURNS int AS $$ BEGIN diff --git a/src/test/modules/test_pg_dump/sql/test_pg_dump.sql b/src/test/modules/test_pg_dump/sql/test_pg_dump.sql index 87e66cae6e3..e7ca5c1c49b 100644 --- a/src/test/modules/test_pg_dump/sql/test_pg_dump.sql +++ b/src/test/modules/test_pg_dump/sql/test_pg_dump.sql @@ -11,9 +11,8 @@ CREATE SCHEMA test_pg_dump_s1; CREATE TYPE test_pg_dump_e1 AS ENUM ('abc', 'def'); CREATE AGGREGATE newavg ( - sfunc = int4_avg_accum, basetype = int4, stype = _int8, - finalfunc = int8_avg, - initcond1 = '{0,0}' + sfunc = int4_avg_accum, basetype = int4, stype = internal, + finalfunc = int8_avg ); CREATE FUNCTION test_pg_dump(int) RETURNS int AS $$ diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 7d07619956f..a86155c1f87 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -3647,6 +3647,33 @@ SELECT eatarray(rwagg(ARRAY[1.0::real])), eatarray(rwagg(ARRAY[1.0::real])); (1 row) ROLLBACK; +-- The transition state of avg(int2)/avg(int4), and of the moving-aggregate +-- mode of sum(int2)/sum(int4), is "internal", so none of the supporting +-- functions can be reached from SQL. +SELECT int2_avg_accum('{0,0}'::int8[], '1'::int2); +ERROR: function int2_avg_accum(bigint[], smallint) does not exist +LINE 1: SELECT int2_avg_accum('{0,0}'::int8[], '1'::int2); + ^ +DETAIL: No function of that name accepts the given argument types. +HINT: You might need to add explicit type casts. +SELECT int4_avg_accum_inv('{1,1}'::int8[], '1'::int4); +ERROR: function int4_avg_accum_inv(bigint[], integer) does not exist +LINE 1: SELECT int4_avg_accum_inv('{1,1}'::int8[], '1'::int4); + ^ +DETAIL: No function of that name accepts the given argument types. +HINT: You might need to add explicit type casts. +SELECT int8_avg('{1,2}'::int8[]); +ERROR: function int8_avg(bigint[]) does not exist +LINE 1: SELECT int8_avg('{1,2}'::int8[]); + ^ +DETAIL: No function of that name accepts the given argument types. +HINT: You might need to add explicit type casts. +SELECT int2int4_sum('{1,2}'::int8[]); +ERROR: function int2int4_sum(bigint[]) does not exist +LINE 1: SELECT int2int4_sum('{1,2}'::int8[]); + ^ +DETAIL: No function of that name accepts the given argument types. +HINT: You might need to add explicit type casts. -- test coverage for aggregate combine/serial/deserial functions BEGIN; SET parallel_setup_cost = 0; @@ -3732,6 +3759,44 @@ FROM (SELECT * FROM tenk1 8333541.588539713493 | 4999.5000000000000000 (1 row) +-- avg(int2) and avg(int4) cover int4_avg_combine, int4_avg_serialize and +-- int4_avg_deserialize +EXPLAIN (COSTS OFF, VERBOSE) +SELECT avg(unique1::int2), avg(unique1::int4) +FROM (SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1) u; + QUERY PLAN +------------------------------------------------------------------------------------------ + Finalize Aggregate + Output: avg((tenk1.unique1)::smallint), avg(tenk1.unique1) + -> Gather + Output: (PARTIAL avg((tenk1.unique1)::smallint)), (PARTIAL avg(tenk1.unique1)) + Workers Planned: 4 + -> Partial Aggregate + Output: PARTIAL avg((tenk1.unique1)::smallint), PARTIAL avg(tenk1.unique1) + -> Parallel Append + -> Parallel Seq Scan on public.tenk1 + Output: tenk1.unique1 + -> Parallel Seq Scan on public.tenk1 tenk1_1 + Output: tenk1_1.unique1 + -> Parallel Seq Scan on public.tenk1 tenk1_2 + Output: tenk1_2.unique1 + -> Parallel Seq Scan on public.tenk1 tenk1_3 + Output: tenk1_3.unique1 +(16 rows) + +SELECT avg(unique1::int2), avg(unique1::int4) +FROM (SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1) u; + avg | avg +-----------------------+----------------------- + 4999.5000000000000000 | 4999.5000000000000000 +(1 row) + ROLLBACK; -- test coverage for dense_rank SELECT dense_rank(x) WITHIN GROUP (ORDER BY x) FROM (VALUES (1),(1),(2),(2),(3),(3)) v(x) GROUP BY (x) ORDER BY 1; diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out index 68062620efa..a228bdaa373 100644 --- a/src/test/regress/expected/create_aggregate.out +++ b/src/test/regress/expected/create_aggregate.out @@ -3,9 +3,8 @@ -- -- all functions CREATEd CREATE AGGREGATE newavg ( - sfunc = int4_avg_accum, basetype = int4, stype = _int8, - finalfunc = int8_avg, - initcond1 = '{0,0}' + sfunc = int4_avg_accum, basetype = int4, stype = internal, + finalfunc = int8_avg ); -- test comments COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment'; diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 91f8342166f..67bad7f1146 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -1595,6 +1595,14 @@ SELECT eatarray(rwagg(ARRAY[1.0::real])), eatarray(rwagg(ARRAY[1.0::real])); ROLLBACK; +-- The transition state of avg(int2)/avg(int4), and of the moving-aggregate +-- mode of sum(int2)/sum(int4), is "internal", so none of the supporting +-- functions can be reached from SQL. +SELECT int2_avg_accum('{0,0}'::int8[], '1'::int2); +SELECT int4_avg_accum_inv('{1,1}'::int8[], '1'::int4); +SELECT int8_avg('{1,2}'::int8[]); +SELECT int2int4_sum('{1,2}'::int8[]); + -- test coverage for aggregate combine/serial/deserial functions BEGIN; @@ -1636,6 +1644,21 @@ FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk1) u; +-- avg(int2) and avg(int4) cover int4_avg_combine, int4_avg_serialize and +-- int4_avg_deserialize +EXPLAIN (COSTS OFF, VERBOSE) +SELECT avg(unique1::int2), avg(unique1::int4) +FROM (SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1) u; + +SELECT avg(unique1::int2), avg(unique1::int4) +FROM (SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1 + UNION ALL SELECT * FROM tenk1) u; + ROLLBACK; -- test coverage for dense_rank diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql index 6b88b9735af..7fbb01382d3 100644 --- a/src/test/regress/sql/create_aggregate.sql +++ b/src/test/regress/sql/create_aggregate.sql @@ -4,9 +4,8 @@ -- all functions CREATEd CREATE AGGREGATE newavg ( - sfunc = int4_avg_accum, basetype = int4, stype = _int8, - finalfunc = int8_avg, - initcond1 = '{0,0}' + sfunc = int4_avg_accum, basetype = int4, stype = internal, + finalfunc = int8_avg ); -- test comments -- 2.55.0
