On 03/09/2026 01:44, Michael Paquier wrote:
> On Wed, Sep 02, 2026 at 10:19:29AM +0200, Andrei Lepikhov wrote:
>> I use direct calls mainly to make regression tests run faster.
>>
>> The first case arose during benchmarking built-in SUM(int4) with various
>> parallelising methods [1] (bare research topic) at scale. This may not be a
>> big
>> issue right now, but as databases get larger, it could become one. I think
>> it's
>> more likely to happen first in the microcurrency space, where the base unit
>> is a
>> cent instead of a dollar, especially with very large partitioned tables.
>>
>> [1]
>> https://www.pgedge.com/blog/do-global-hash-tables-strike-back-in-postgresql
>
> Honestly, I don't know how to feel about this patch.
>
> I see the reason why you are doing it for efficiency, but you are
> abusing direct function calls (not in the docs) to emulate patterns
> that we support behind operators (in user-visible documentation), or
> even casts (in user-visible documentation).
Ok, no problem. Here is the same thing through the documented path only:
SELECT sum(2147483647::int4) FROM (SELECT generate_series(1, 4294967298)) s;
sum
---------------------
9223372036854775806
(1 row)
Time: 277753.407 ms (04:37.753)
SELECT sum(2147483647::int4) FROM (SELECT generate_series(1, 4294967299)) s;
sum
----------------------
-9223372034707292163
(1 row)
Time: 274398.422 ms (04:34.398)
int8 holds up to 9223372036854775807, so 4294967299 rows of INT_MAX land one row
past the limit. The first query shows the last value that still fits, the second
one silently returns a negative sum.
avg(int4) keeps the same int8 accumulator, and in practical terms it looks
worse:
SELECT avg(2147483647::int4) FROM (SELECT generate_series(1, 4294967299)) s;
avg
----------------------
-2147483646.00000000
(1 row)
Time: 341468.965 ms (05:41.469)
The average of 4.3 billion non-negative values comes out negative.
Two details make me think this deserves a fix rather than a documentation note:
* For sum(int4) the answer depends on the plan. int4_sum has no overflow check,
but the combine function is int8pl, which does. So the same query over the same
data returns a wrapped negative number under a serial plan and can fail with
"bigint out of range" under parallel aggregation.
* avg(int4) is not even inconsistent - it is wrong either way. Both
int4_avg_accum and int4_avg_combine add into state->sum unchecked, so no plan
shape turns this into an error. The int2 variants behave the same.
Yes, 4.3 billion rows is a lot to ask of my Intel Macbook laptop - about four
and a half minutes per query on mine. On a modern server reading a large table
it may be reached in reasonable time.
--
regards, Andrei Lepikhov,
pgEdge
