Hi, Commit 42473b3b31 added SupportRequestSimplifyAggref, which the planner issues for any Aggref whose function names a support function. Nice, but extensions cannot reach it, because there is no way to attach a support function to an aggregate:
ALTER FUNCTION pg_catalog.sum(numeric) SUPPORT numeric_support; ERROR: "pg_catalog.sum" is an aggregate function CREATE/ALTER aggregate doesn't support this feature at all. This does not look like a decision about support functions. AlterFunction() rejects an aggregate for every property it can change, not just for SUPPORT, and the other properties it covers are ones AggregateCreate() fixes for aggregates anyway. prosupport is different: nothing else in the catalog determines it, so refusing to set it leaves the capability simply unreachable rather than merely inconvenient. Updating pg_proc directly is not an answer. It records no pg_depend entry, so the support function can then be dropped and leave a dangling OID behind. My interest is extension-defined optimizations of numeric aggregates [1] or even more extravagant dumb transformations, like the following (doable for specific conditions): SUM(x ORDER BY x) → SUM(x) SUM(x) FILTER (WHERE true) → SUM(x) SUM(N) -> N * COUNT(*) Please find attached a patch adding SUPPORT to CREATE AGGREGATE's option list and an ALTER AGGREGATE ... SUPPORT form. Both require superuser, as the equivalent function clauses do, and record a normal dependency. [1] https://github.com/danolivo/pg_numeric_agg_support -- regards, Andrei Lepikhov, pgEdge
From b90baa875491f48568edf7ed7f41b5850a9c289d Mon Sep 17 00:00:00 2001 From: Claude <[email protected]> Date: Sun, 16 Aug 2026 20:18:30 +0000 Subject: [PATCH v0] Enable altering prosupport function of an aggregate The prosupport has been settable only through CREATE/ALTER FUNCTION, that rejects aggregates, so an aggregate could acquire a support function only as a pg_proc.dat entry or direct catalog update. Extensions should have more convenient way by a DDL command. Add SUPPORT to CREATE AGGREGATE's option list, and accept it in ALTER AGGREGATE. ALTER FUNCTION continues to reject them, as it does for every other property. Setting a support function requires superuser, as the equivalent function clauses do, and records a normal dependency on it. CREATE OR REPLACE AGGREGATE without SUPPORT clears it, matching CREATE OR REPLACE FUNCTION; as for functions, ALTER cannot clear it. The restriction that SUPPORT is the only alterable property of an aggregate is enforced by the grammar for ALTER AGGREGATE. --- doc/src/sgml/ref/alter_aggregate.sgml | 20 ++++++++++++++++++++ doc/src/sgml/ref/create_aggregate.sgml | 21 +++++++++++++++++++++ src/backend/catalog/pg_aggregate.c | 6 ++++-- src/backend/commands/aggregatecmds.c | 11 ++++++++++- src/backend/commands/functioncmds.c | 20 ++++++++++++++++++-- src/backend/parser/gram.y | 15 ++++++++++++++- src/backend/tcop/utility.c | 3 +++ src/bin/psql/tab-complete.in.c | 2 +- src/include/catalog/pg_aggregate.h | 3 ++- src/include/commands/defrem.h | 1 + 10 files changed, 94 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/ref/alter_aggregate.sgml b/doc/src/sgml/ref/alter_aggregate.sgml index d0a39ba7b5e..4079a6f7a13 100644 --- a/doc/src/sgml/ref/alter_aggregate.sgml +++ b/doc/src/sgml/ref/alter_aggregate.sgml @@ -25,6 +25,7 @@ ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signatu ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signature</replaceable> ) OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signature</replaceable> ) SET SCHEMA <replaceable>new_schema</replaceable> +ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signature</replaceable> ) SUPPORT <replaceable>support_function</replaceable> <phrase>where <replaceable>aggregate_signature</replaceable> is:</phrase> @@ -53,6 +54,8 @@ ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signatu the owner doesn't do anything you couldn't do by dropping and recreating the aggregate function. However, a superuser can alter ownership of any aggregate function anyway.) + To set the aggregate function's planner support function, you must be + superuser. </para> </refsect1> @@ -133,6 +136,23 @@ ALTER AGGREGATE <replaceable>name</replaceable> ( <replaceable>aggregate_signatu </para> </listitem> </varlistentry> + + <varlistentry> + <term><literal>SUPPORT</literal> <replaceable class="parameter">support_function</replaceable></term> + <listitem> + <para> + Set or change the planner support function to use for this aggregate. + See <xref linkend="xfunc-optimization"/> for details. You must be + superuser to use this option. + </para> + + <para> + This option cannot be used to remove the support function altogether, + since it must name a new support function. Use <command>CREATE OR + REPLACE AGGREGATE</command> if you need to do that. + </para> + </listitem> + </varlistentry> </variablelist> </refsect1> diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml index 0472ac2e874..b5829091abc 100644 --- a/doc/src/sgml/ref/create_aggregate.sgml +++ b/doc/src/sgml/ref/create_aggregate.sgml @@ -42,6 +42,7 @@ CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable [ , MINITCOND = <replaceable class="parameter">minitial_condition</replaceable> ] [ , SORTOP = <replaceable class="parameter">sort_operator</replaceable> ] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] + [ , SUPPORT = <replaceable class="parameter">support_function</replaceable> ] ) CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ] @@ -54,6 +55,7 @@ CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ] [ , INITCOND = <replaceable class="parameter">initial_condition</replaceable> ] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] + [ , SUPPORT = <replaceable class="parameter">support_function</replaceable> ] [ , HYPOTHETICAL ] ) @@ -644,6 +646,25 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; </listitem> </varlistentry> + <varlistentry> + <term><literal>SUPPORT =</literal> <replaceable class="parameter">support_function</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> + + <para> + Note that this is unrelated to the aggregate's transition, final and + other helper functions described above, which implement the aggregate + itself; a planner support function instead advises the planner about + calls to the aggregate. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>HYPOTHETICAL</literal></term> <listitem> diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c index 243b952b9cc..9cb28e0e569 100644 --- a/src/backend/catalog/pg_aggregate.c +++ b/src/backend/catalog/pg_aggregate.c @@ -30,6 +30,7 @@ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/lsyscache.h" +#include "utils/regproc.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -74,7 +75,8 @@ AggregateCreate(const char *aggName, int32 aggmTransSpace, const char *agginitval, const char *aggminitval, - char proparallel) + char proparallel, + Oid prosupport) { Relation aggdesc; HeapTuple tup; @@ -639,7 +641,7 @@ AggregateCreate(const char *aggName, PointerGetDatum(NULL), /* trftypes */ NIL, /* trfoids */ PointerGetDatum(NULL), /* proconfig */ - InvalidOid, /* no prosupport */ + prosupport, /* planner support function */ 1, /* procost */ 0); /* prorows */ procOid = myself.objectId; diff --git a/src/backend/commands/aggregatecmds.c b/src/backend/commands/aggregatecmds.c index 41b45dc6402..f8e51c1d81c 100644 --- a/src/backend/commands/aggregatecmds.c +++ b/src/backend/commands/aggregatecmds.c @@ -82,6 +82,8 @@ DefineAggregate(ParseState *pstate, char *initval = NULL; char *minitval = NULL; char *parallel = NULL; + DefElem *supportItem = NULL; + Oid prosupport = InvalidOid; int numArgs; int numDirectArgs = 0; oidvector *parameterTypes; @@ -186,6 +188,8 @@ DefineAggregate(ParseState *pstate, minitval = defGetString(defel); else if (strcmp(defel->defname, "parallel") == 0) parallel = defGetString(defel); + else if (strcmp(defel->defname, "support") == 0) + supportItem = defel; else ereport(WARNING, (errcode(ERRCODE_SYNTAX_ERROR), @@ -434,6 +438,10 @@ DefineAggregate(ParseState *pstate, errmsg("parameter \"parallel\" must be SAFE, RESTRICTED, or UNSAFE"))); } + /* interpret_func_support handles the privilege check */ + if (supportItem) + prosupport = interpret_func_support(supportItem); + /* * Most of the argument-checking is done inside of AggregateCreate */ @@ -468,7 +476,8 @@ DefineAggregate(ParseState *pstate, mtransSpace, /* transition space */ initval, /* initial condition */ minitval, /* initial condition */ - proparallel); /* parallel safe? */ + proparallel, /* parallel safe? */ + prosupport); /* planner support function */ } /* diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 3afd762e9dc..3b9bc0b58b4 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -684,7 +684,12 @@ update_proconfig_value(ArrayType *a, List *set_items) return a; } -static Oid +/* + * Interpret a SUPPORT clause, and return the OID of the named planner + * support function. This is shared by CREATE/ALTER FUNCTION and + * CREATE/ALTER AGGREGATE; it also enforces the privilege check. + */ +Oid interpret_func_support(DefElem *defel) { List *procName = defGetQualifiedName(defel); @@ -1397,7 +1402,14 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) aclcheck_error(ACLCHECK_NOT_OWNER, stmt->objtype, NameListToString(stmt->func->objname)); - if (procForm->prokind == PROKIND_AGGREGATE) + /* + * An aggregate's properties belong to CREATE AGGREGATE, except for the + * planner support function, which has no other DDL home. Only ALTER + * AGGREGATE can set that, and its grammar admits no other action, as + * asserted below. + */ + if (procForm->prokind == PROKIND_AGGREGATE && + stmt->objtype != OBJECT_AGGREGATE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is an aggregate function", @@ -1425,6 +1437,10 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) elog(ERROR, "option \"%s\" not recognized", defel->defname); } + /* SUPPORT is the only thing the grammar lets us do to an aggregate */ + Assert(procForm->prokind != PROKIND_AGGREGATE || + (support_item != NULL && list_length(stmt->actions) == 1)); + if (volatility_item) procForm->provolatile = interpret_func_volatility(volatility_item); if (strict_item) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 17035fb4d15..498c75e4c42 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -9164,12 +9164,15 @@ table_func_column_list: ; /***************************************************************************** - * ALTER FUNCTION / ALTER PROCEDURE / ALTER ROUTINE + * ALTER FUNCTION / ALTER PROCEDURE / ALTER ROUTINE / ALTER AGGREGATE * * RENAME and OWNER subcommands are already provided by the generic * ALTER infrastructure, here we just specify alterations that can * only be applied to functions. * + * For aggregates, SUPPORT is the only alterable property; the rest of an + * aggregate's definition belongs to CREATE AGGREGATE. + * *****************************************************************************/ AlterFunctionStmt: ALTER FUNCTION function_with_argtypes alterfunc_opt_list opt_restrict @@ -9199,6 +9202,16 @@ AlterFunctionStmt: n->actions = $4; $$ = (Node *) n; } + | ALTER AGGREGATE aggregate_with_argtypes SUPPORT any_name + { + AlterFunctionStmt *n = makeNode(AlterFunctionStmt); + + n->objtype = OBJECT_AGGREGATE; + n->func = $3; + n->actions = list_make1(makeDefElem("support", + (Node *) $5, @4)); + $$ = (Node *) n; + } ; alterfunc_opt_list: diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 4d33fcb5e9d..37dd3d4f8f2 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -2759,6 +2759,9 @@ CreateCommandTag(Node *parsetree) case OBJECT_ROUTINE: tag = CMDTAG_ALTER_ROUTINE; break; + case OBJECT_AGGREGATE: + tag = CMDTAG_ALTER_AGGREGATE; + break; default: tag = CMDTAG_UNKNOWN; } diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 190fff7ea0e..da849e5b1af 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2244,7 +2244,7 @@ match_previous_words(int pattern_id, else if (Matches("ALTER", "AGGREGATE", MatchAny, MatchAny)) { if (ends_with(prev_wd, ')')) - COMPLETE_WITH("OWNER TO", "RENAME TO", "SET SCHEMA"); + COMPLETE_WITH("OWNER TO", "RENAME TO", "SET SCHEMA", "SUPPORT"); else COMPLETE_WITH_FUNCTION_ARG(prev2_wd); } diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h index 2b4f5dae5f2..ccf6377e9ee 100644 --- a/src/include/catalog/pg_aggregate.h +++ b/src/include/catalog/pg_aggregate.h @@ -181,6 +181,7 @@ extern ObjectAddress AggregateCreate(const char *aggName, int32 aggmTransSpace, const char *agginitval, const char *aggminitval, - char proparallel); + char proparallel, + Oid prosupport); #endif /* PG_AGGREGATE_H */ diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 574f860bdd2..48dadcf1bc7 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -58,6 +58,7 @@ extern void GetOperatorFromCompareType(Oid opclass, Oid rhstype, CompareType cmp extern ObjectAddress CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt); extern void RemoveFunctionById(Oid funcOid); extern ObjectAddress AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt); +extern Oid interpret_func_support(DefElem *defel); extern ObjectAddress CreateCast(CreateCastStmt *stmt); extern ObjectAddress CreateTransform(CreateTransformStmt *stmt); extern void IsThereFunctionInNamespace(const char *proname, int pronargs, -- 2.55.0
