Re: Add PRODUCT() aggregate function

On Fri, Sep 11, 2026 at 8:16 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> ... btw, there is another interesting definitional question here.
> AFAICS, "ON EMPTY" effectively is an override for the aggregate's
> final function. Does it actually make sense when there is a final
> function? Specifically, if the final function were willing to
> provide non-null output for zero rows in, should we still override
> it? SUM and PRODUCT don't provide a lot of guidance here.
>

The patch currently short-circuits and returns the ON EMPTY value anytime
zero
rows are processed, bypassing the aggregate's final function entirely. This
makes the most sense to me from a user perspective -- if someone explicitly
specifies ON EMPTY, they want that exact value to take precedence over the
aggregate's default empty-set behavior.

>
> I'm also wondering idly how this interacts with "inverse transition
> functions" for aggregates used as window functions.
>

I believe this scenario is already handled correctly. When a window frame
shrinks
and loses its last row, advance_windowaggregate_base() bypasses the inverse
transition function entirely. Instead, it deliberately falls back to
initialize_windowaggregate() to restore the true initial state. This
reinitialization cleanly resets our inputReceived flag to false, ensuring
that
a frame emptying out via incremental removal is accurately detected as
empty.

I'd be happy to discuss this further on the relevant thread.

Thanks

>
> regards, tom lane
>

--
*Jeevan Chalke*
*Senior Principal Engineer, Engineering Manager*
*Product Development*

enterprisedb.com <https://www.enterprisedb.com>