I wrote:
> After reflection I realized that the approach I took in my first patch
> is wrong: letting ALTER FUNCTION work on an aggregate is pretty
> non-orthogonal, if the only property it can set is SUPPORT. The right
> way to do this is to add a SUPPORT option to CREATE AGGREGATE, which
> slots nicely into getting pg_dump to handle it. As attached.
Grumble ... for some reason I was thinking that ProcedureCreate would
handle checking for superuser when specifying a support function,
but it doesn't; AggregateCreate must do that. v3 attached contains
just that one addition.
regards, tom lane
From af5b492e92239da03d791427d2bf31657fc92e79 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Thu, 20 Aug 2026 16:03:13 -0400
Subject: [PATCH v3] Allow an aggregate's planner support function to be set
via CREATE AGGREGATE.
Commit 42473b3b3 implemented SupportRequestSimplifyAggref, but failed
to think about what infrastructure would be required for an extension
to use that: there is no SQL-level mechanism for attaching a planner
support function to an aggregate. That seems pretty critical for a
feature that's primarily intended to be used by extensions.
To fix, add a SUPPORT clause to CREATE AGGREGATE, and teach pg_dump
(and thereby pg_upgrade) about dumping this aggregate property.
We don't need to touch ALTER AGGREGATE, because it's already the case
that you're supposed to use CREATE OR REPLACE AGGREGATE if you want
to alter any aggregate-specific properties set by CREATE AGGREGATE.
(Maybe at some point we'll think that that policy ought to change,
but I don't think this one feature moves the needle enough.)
Per report from Andrei Lepikhov, who also provided some of the
new documentation text.
Reported-by: Andrei Lepikhov <[email protected]>
Author: Tom Lane <[email protected]>
Reviewed-by: Andrei Lepikhov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 19
---
doc/src/sgml/ref/alter_aggregate.sgml | 7 ++++
doc/src/sgml/ref/create_aggregate.sgml | 15 +++++++++
doc/src/sgml/xfunc.sgml | 18 +++++++++-
src/backend/catalog/pg_aggregate.c | 33 ++++++++++++++++++-
src/backend/commands/aggregatecmds.c | 4 +++
src/bin/pg_dump/pg_dump.c | 18 ++++++++--
src/include/catalog/pg_aggregate.h | 1 +
.../regress/expected/create_aggregate.out | 15 +++++++++
src/test/regress/sql/create_aggregate.sql | 8 +++++
9 files changed, 115 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/alter_aggregate.sgml b/doc/src/sgml/ref/alter_aggregate.sgml
index d0a39ba7b5e..cc11e077561 100644
--- a/doc/src/sgml/ref/alter_aggregate.sgml
+++ b/doc/src/sgml/ref/alter_aggregate.sgml
@@ -149,6 +149,13 @@ ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signatu
if <literal>VARIADIC "any"</literal> was used in both the direct and
aggregated argument lists, write <literal>VARIADIC "any"</literal> only once.
</para>
+
+ <para>
+ <command>ALTER AGGREGATE</command> deals only with generic properties of
+ an aggregate, such as its name. To change the aggregate-specific
+ properties of an aggregate such as its support functions, replace its
+ definition entirely with <command>CREATE OR REPLACE AGGREGATE</command>.
+ </para>
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml
index 0472ac2e874..89203d39221 100644
--- a/doc/src/sgml/ref/create_aggregate.sgml
+++ b/doc/src/sgml/ref/create_aggregate.sgml
@@ -41,6 +41,7 @@ CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable
[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
[ , MINITCOND = <replaceable class="parameter">minitial_condition</replaceable> ]
[ , SORTOP = <replaceable class="parameter">sort_operator</replaceable> ]
+ [ , SUPPORT = <replaceable class="parameter">supportfunc</replaceable> ]
[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)
@@ -53,6 +54,7 @@ CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable
[ , FINALFUNC_EXTRA ]
[ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
[ , INITCOND = <replaceable class="parameter">initial_condition</replaceable> ]
+ [ , SUPPORT = <replaceable class="parameter">supportfunc</replaceable> ]
[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
[ , HYPOTHETICAL ]
)
@@ -80,6 +82,7 @@ CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable
[ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
[ , MINITCOND = <replaceable class="parameter">minitial_condition</replaceable> ]
[ , SORTOP = <replaceable class="parameter">sort_operator</replaceable> ]
+ [ , SUPPORT = <replaceable class="parameter">supportfunc</replaceable> ]
)
</synopsis>
</refsynopsisdiv>
@@ -628,6 +631,18 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">supportfunc</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of a <firstterm>planner support
+ function</firstterm> to use for this aggregate. See
+ <xref linkend="xfunc-optimization"/> for details.
+ You must be superuser to use this option.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>PARALLEL =</literal> { <literal>SAFE</literal> | <literal>RESTRICTED</literal> | <literal>UNSAFE</literal> }</term>
<listitem>
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 050e1e50bec..97f3cb625e2 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -4167,7 +4167,7 @@ extern PgStat_Kind pgstat_register_kind(PgStat_Kind kind,
<para>
It is also possible to attach a <firstterm>planner support
- function</firstterm> to an SQL-callable function (called
+ function</firstterm> to an SQL-callable function or aggregate (called
its <firstterm>target function</firstterm>), and thereby provide
knowledge about the target function that is too complex to be
represented declaratively. Planner support functions have to be
@@ -4241,6 +4241,22 @@ supportfn(internal) returns internal
normal execution of the target function.
</para>
+ <para>
+ Aggregate function calls can also be simplified during planning. For
+ example, <literal>COUNT(<replaceable>x</replaceable>)</literal> can be
+ replaced by <literal>COUNT(*)</literal>
+ when <replaceable>x</replaceable> is known to not be null. This can be
+ done by a support function that implements
+ the <literal>SupportRequestSimplifyAggref</literal> request type. The
+ support function will be called for each instance of its target aggregate
+ found in a query parse tree. If it finds that the particular call can be
+ replaced, it can build and return a new node, usually another aggregate
+ call, leaving the node it was given unmodified. As with
+ <literal>SupportRequestSimplify</literal>, it is the support function's
+ responsibility that the replacement be equivalent to normal execution of
+ the target aggregate.
+ </para>
+
<para>
For target functions that return <type>boolean</type>, it is often useful to estimate
the fraction of rows that will be selected by a <literal>WHERE</literal> clause using that
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index 243b952b9cc..4d98a7584ae 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -68,6 +68,7 @@ AggregateCreate(const char *aggName,
char finalfnModify,
char mfinalfnModify,
List *aggsortopName,
+ List *aggsupportfuncName,
Oid aggTransType,
int32 aggTransSpace,
Oid aggmTransType,
@@ -92,6 +93,7 @@ AggregateCreate(const char *aggName,
Oid minvtransfn = InvalidOid; /* can be omitted */
Oid mfinalfn = InvalidOid; /* can be omitted */
Oid sortop = InvalidOid; /* can be omitted */
+ Oid supportfn = InvalidOid; /* can be omitted */
Oid *aggArgTypes = parameterTypes->values;
bool mtransIsStrict = false;
Oid rettype;
@@ -581,6 +583,35 @@ AggregateCreate(const char *aggName,
false, -1);
}
+ /*
+ * Validate the planner support function, if present.
+ */
+ if (aggsupportfuncName)
+ {
+ /* signature is always support(internal) returns internal */
+ fnArgs[0] = INTERNALOID;
+
+ supportfn = lookup_agg_function(aggsupportfuncName, 1,
+ fnArgs, InvalidOid,
+ &rettype);
+
+ if (rettype != INTERNALOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("return type of support function %s is not %s",
+ NameListToString(aggsupportfuncName),
+ format_type_be(INTERNALOID))));
+
+ /*
+ * Specifying a support function requires superuser, same as in CREATE
+ * FUNCTION.
+ */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to specify a support function")));
+ }
+
/*
* permission checks on used types
*/
@@ -639,7 +670,7 @@ AggregateCreate(const char *aggName,
PointerGetDatum(NULL), /* trftypes */
NIL, /* trfoids */
PointerGetDatum(NULL), /* proconfig */
- InvalidOid, /* no prosupport */
+ supportfn, /* prosupport */
1, /* procost */
0); /* prorows */
procOid = myself.objectId;
diff --git a/src/backend/commands/aggregatecmds.c b/src/backend/commands/aggregatecmds.c
index 41b45dc6402..d26cfd8081d 100644
--- a/src/backend/commands/aggregatecmds.c
+++ b/src/backend/commands/aggregatecmds.c
@@ -74,6 +74,7 @@ DefineAggregate(ParseState *pstate,
char finalfuncModify = 0;
char mfinalfuncModify = 0;
List *sortoperatorName = NIL;
+ List *supportfuncName = NIL;
TypeName *baseType = NULL;
TypeName *transType = NULL;
TypeName *mtransType = NULL;
@@ -155,6 +156,8 @@ DefineAggregate(ParseState *pstate,
mfinalfuncModify = extractModify(defel);
else if (strcmp(defel->defname, "sortop") == 0)
sortoperatorName = defGetQualifiedName(defel);
+ else if (strcmp(defel->defname, "support") == 0)
+ supportfuncName = defGetQualifiedName(defel);
else if (strcmp(defel->defname, "basetype") == 0)
baseType = defGetTypeName(defel);
else if (strcmp(defel->defname, "hypothetical") == 0)
@@ -462,6 +465,7 @@ DefineAggregate(ParseState *pstate,
finalfuncModify,
mfinalfuncModify,
sortoperatorName, /* sort operator name */
+ supportfuncName, /* planner support func name */
transTypeId, /* transition data type */
transSpace, /* transition space */
mtransTypeId, /* transition data type */
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index feed88f9854..db14834e430 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15365,6 +15365,7 @@ dumpAgg(Archive *fout, const AggInfo *agginfo)
const char *agginitval;
const char *aggminitval;
const char *proparallel;
+ const char *prosupport;
char defaultfinalmodify;
/* Do nothing if not dumping schema */
@@ -15413,11 +15414,18 @@ dumpAgg(Archive *fout, const AggInfo *agginfo)
if (fout->remoteVersion >= 110000)
appendPQExpBufferStr(query,
"aggfinalmodify,\n"
- "aggmfinalmodify\n");
+ "aggmfinalmodify,\n");
else
appendPQExpBufferStr(query,
"'0' AS aggfinalmodify,\n"
- "'0' AS aggmfinalmodify\n");
+ "'0' AS aggmfinalmodify,\n");
+
+ if (fout->remoteVersion >= 120000)
+ appendPQExpBufferStr(query,
+ "prosupport\n");
+ else
+ appendPQExpBufferStr(query,
+ "'-' AS prosupport\n");
appendPQExpBufferStr(query,
"FROM pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
@@ -15459,6 +15467,7 @@ dumpAgg(Archive *fout, const AggInfo *agginfo)
agginitval = PQgetvalue(res, 0, i_agginitval);
aggminitval = PQgetvalue(res, 0, i_aggminitval);
proparallel = PQgetvalue(res, 0, PQfnumber(res, "proparallel"));
+ prosupport = PQgetvalue(res, 0, PQfnumber(res, "prosupport"));
{
char *funcargs;
@@ -15587,6 +15596,11 @@ dumpAgg(Archive *fout, const AggInfo *agginfo)
free(aggsortconvop);
}
+ if (strcmp(prosupport, "-") != 0)
+ {
+ appendPQExpBuffer(details, ",\n SUPPORT = %s", prosupport);
+ }
+
if (aggkind == AGGKIND_HYPOTHETICAL)
appendPQExpBufferStr(details, ",\n HYPOTHETICAL");
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 2b4f5dae5f2..2668b035859 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -175,6 +175,7 @@ extern ObjectAddress AggregateCreate(const char *aggName,
char finalfnModify,
char mfinalfnModify,
List *aggsortopName,
+ List *aggsupportfuncName,
Oid aggTransType,
int32 aggTransSpace,
Oid aggmTransType,
diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out
index dcf69094237..68062620efa 100644
--- a/src/test/regress/expected/create_aggregate.out
+++ b/src/test/regress/expected/create_aggregate.out
@@ -30,12 +30,27 @@ CREATE AGGREGATE oldcnt (
-- aggregate that only cares about null/nonnull input
CREATE AGGREGATE newcnt ("any") (
sfunc = int8inc_any, stype = int8,
+ support = int8inc_support,
initcond = '0'
);
COMMENT ON AGGREGATE nosuchagg (*) IS 'should fail';
ERROR: aggregate nosuchagg(*) does not exist
COMMENT ON AGGREGATE newcnt (*) IS 'an agg(*) comment';
COMMENT ON AGGREGATE newcnt ("any") IS 'an agg(any) comment';
+-- verify that newcnt's support function enables run-condition optimization
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT newcnt(ten) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) c
+ FROM tenk1) t
+WHERE c = 1;
+ QUERY PLAN
+-------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
+ Run Condition: (newcnt(tenk1.ten) OVER w1 = 1)
+ -> Seq Scan on tenk1
+(4 rows)
+
-- multi-argument aggregate
create function sum3(int8,int8,int8) returns int8 as
'select $1 + $2 + $3' language sql strict immutable;
diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql
index d4b4036fd7d..6b88b9735af 100644
--- a/src/test/regress/sql/create_aggregate.sql
+++ b/src/test/regress/sql/create_aggregate.sql
@@ -35,6 +35,7 @@ CREATE AGGREGATE oldcnt (
-- aggregate that only cares about null/nonnull input
CREATE AGGREGATE newcnt ("any") (
sfunc = int8inc_any, stype = int8,
+ support = int8inc_support,
initcond = '0'
);
@@ -42,6 +43,13 @@ COMMENT ON AGGREGATE nosuchagg (*) IS 'should fail';
COMMENT ON AGGREGATE newcnt (*) IS 'an agg(*) comment';
COMMENT ON AGGREGATE newcnt ("any") IS 'an agg(any) comment';
+-- verify that newcnt's support function enables run-condition optimization
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT newcnt(ten) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) c
+ FROM tenk1) t
+WHERE c = 1;
+
-- multi-argument aggregate
create function sum3(int8,int8,int8) returns int8 as
'select $1 + $2 + $3' language sql strict immutable;
--
2.52.0
