Re: Allow a prosupport function to be attached to an aggregate

On 21/08/2026 17:20, Tom Lane wrote:
> Andrei Lepikhov <lepihov(at)gmail(dot)com> writes:>
>> What I want to optimise is the built-in functions like
>> sum(), avg(), and similar ones, because those are what users
>> actually use.
>
> For those, wouldn't we just modify the initial contents of pg_proc?

For the trivial ones, yes, and that is a fair point. sum(x) FILTER (WHERE true)
-> sum(x) holds unconditionally; sum(x ORDER BY x) -> sum(x) holds for the exact
types, though not for float, where the order is part of the result. They would
also make a decent worked example of an aggregate support function - AFAICS
int8inc_support is still the only in-core user of SupportRequestSimplifyAggref.

Other optimisations of built-in aggregates in my pocket are an OLAP
specialisation and would be pure overhead for other workloads. I doubt it makes
sense to put a sum(numeric(N,M)) variant into core when it only pays off in a
database whose numeric columns carry a typmod.
>> With
>> only CREATE OR REPLACE AGGREGATE available, an extension would have to restate
>> the whole definition of pg_catalog.sum(numeric), including sfunc, combinefunc,
>> and so on, and keep that copy updated with every major release.
>
> I'm not particularly on board with this goal: I think letting
> extensions modify built-in aggregates is fraught with all sorts
> of issues. (To name only one, there's no way for multiple
> extensions to each attach new behaviors to the same aggregate.)

Agreed as far as it goes, though the limitation is neither new nor specific to
aggregates: prosupport is a single regproc column, so ALTER FUNCTION ... SUPPORT
on a plain function is already last-one-wins. Whether a shared object should
accept support functions from third parties at all is a fair question, and a
larger one than this patch. So, with more evidence of practical need, we can
return to it later.

In my experience that is what applications ask for. An extension also back-ports
the optimisation to already-released majors, which is a large part of why we
write one. In the meantime we will do it by rewriting prosupport directly and
adding the pg_depend entry - the only way I know to attach such an optimisation
without touching the application.

> I'm certainly not going to risk putting in half-considered
> support for such a thing at this stage of the release cycle.
> There's enough risk in adding what I proposed yesterday.
No objection. It closes the case the thread started from and the pg_dump part is
what v0 was missing.

--
regards, Andrei Lepikhov,
pgEdge