Block various aggregate component functions from being called directly

Started by David Rowley3 days ago4 messageshackers
Jump to latest
#1David Rowley
dgrowleyml@gmail.com

We're getting bug reports (e.g., [1]/messages/by-id/DS2PR18MB9275696404EDAB513A71510D13C9702@DS2PR18MB927569.namprd18.prod.outlook.com[2]/messages/by-id/AC9E5138-2771-48A1-997D-8538A5FD9E01@gmail.com) complaining about various
aggregate component functions that don't do the right thing in some
weird cases. E.g., no overflow error from:

SELECT int2_avg_accum_inv('{1,9223372036854775807}'::int8[], -1::int2);

But who would do that?!

It seems like the new reality that people are using LLMs or fuzz
testing, and the robots are classifying these as bugs, and we're
getting reports complaining about it.

Andres mentioned in [3]/messages/by-id/lbvjl22swi3p3sf7nmmrlx2cq5aa55ry3c5ore2rpylsmi6s5j@xrrahkipnsce that we should consider making all aggregate
states internal so that the transition functions take an internal
parameter so they get blocked. I don't like that idea, as it means
having to palloc aggregate states where we currently don't have to.
Tom mentioned in [4]/messages/by-id/1571559.1788537022@sss.pgh.pa.us that doing this will require serial/deserial
functions for any aggregate we want to support parallel aggregates
for. Those two seem like good reasons not to. It also feels a bit
inflexible, as we might find something we don't want to or can't
switch to an internal parameter.

I think we should just block these from being called directly by
adding a new column to pg_proc that allows a function to be marked as
internal. I don't see any need to have CREATE FUNCTION support this
for UDFs, as it's fundamentally there to protect users from misusing
built-in functions that are not designed for some more creative
purposes and to stop the robots hassling us about bugs that aren't.

I got this far with the tagging:

postgres=# select proname from pg_proc where prointernal;
proname
----------------------------------
float4_accum
float8_accum
float8_combine
float8_avg
float8_var_pop
float8_var_samp
float8_stddev_pop
float8_stddev_samp
int4_avg_combine
int2_avg_accum
int4_avg_accum
int2_avg_accum_inv
int4_avg_accum_inv
int8_avg
int2int4_sum
float8_regr_accum
float8_regr_combine
range_intersect_agg_transfn
multirange_intersect_agg_transfn
(19 rows)

And the patch gives you:

postgres=# SELECT
int2_avg_accum_inv('{1,9223372036854775807}'::int8[], -1::int2);
ERROR: function int2_avg_accum_inv(bigint[], smallint) cannot be
called directly
LINE 1: SELECT int2_avg_accum_inv('{1,9223372036854775807}'::int8[],...
^
DETAIL: This function is for internal use only.

But you can still use these in CREATE AGGREGATE, and I suppose CREATE
OPERATOR too, and even CREATE FUNCTION if you want to bypass it.

I'm not convinced I've got the prointernal checks in all the right
places, and there are probably more or less functions that need marked
as internal. Certainly, the aggregate.sql tests are failing because
I've blocked functions like float8_accum and float8_regr_accum. Maybe
we could get around that by doing CREATE FUNCTION and referencing the
core function. I'd need to remove the
Assert(AggCheckCallContext(fcinfo, NULL)) though.

The attached is a proof-of-concept patch for discussion. Is it worth
me continuing with this?

David

[1]: /messages/by-id/DS2PR18MB9275696404EDAB513A71510D13C9702@DS2PR18MB927569.namprd18.prod.outlook.com
[2]: /messages/by-id/AC9E5138-2771-48A1-997D-8538A5FD9E01@gmail.com
[3]: /messages/by-id/lbvjl22swi3p3sf7nmmrlx2cq5aa55ry3c5ore2rpylsmi6s5j@xrrahkipnsce
[4]: /messages/by-id/1571559.1788537022@sss.pgh.pa.us

Attachments:

t259444_1
poc_introduce_internal-only_functions.patchapplication/octet-stream; name=poc_introduce_internal-only_functions.patchDownload+274-308
#2Chao Li
li.evan.chao@gmail.com
In reply to: David Rowley (#1)
Re: Block various aggregate component functions from being called directly

On Nov 19, 2026, at 15:20, David Rowley <dgrowleyml@gmail.com> wrote:

We're getting bug reports (e.g., [1][2]) complaining about various
aggregate component functions that don't do the right thing in some
weird cases. E.g., no overflow error from:

SELECT int2_avg_accum_inv('{1,9223372036854775807}'::int8[], -1::int2);

But who would do that?!

I have the same feeling.

But, IMO, as infrastructure software, PG is supposed to handle any invalid inputs gracefully. I think it’s a problem of ROI. We have limited development resources, and fixing every unusual case might not be worthwhile. Preventing this kind of unintended use seems like a reasonable way to handle that. So, I think this patch is a good trade-off.

It seems like the new reality that people are using LLMs or fuzz
testing, and the robots are classifying these as bugs, and we're
getting reports complaining about it.

Andres mentioned in [3] that we should consider making all aggregate
states internal so that the transition functions take an internal
parameter so they get blocked. I don't like that idea, as it means
having to palloc aggregate states where we currently don't have to.
Tom mentioned in [4] that doing this will require serial/deserial
functions for any aggregate we want to support parallel aggregates
for. Those two seem like good reasons not to. It also feels a bit
inflexible, as we might find something we don't want to or can't
switch to an internal parameter.

I think we should just block these from being called directly by
adding a new column to pg_proc that allows a function to be marked as
internal. I don't see any need to have CREATE FUNCTION support this
for UDFs, as it's fundamentally there to protect users from misusing
built-in functions that are not designed for some more creative
purposes and to stop the robots hassling us about bugs that aren't.

I got this far with the tagging:

postgres=# select proname from pg_proc where prointernal;
proname
----------------------------------
float4_accum
float8_accum
float8_combine
float8_avg
float8_var_pop
float8_var_samp
float8_stddev_pop
float8_stddev_samp
int4_avg_combine
int2_avg_accum
int4_avg_accum
int2_avg_accum_inv
int4_avg_accum_inv
int8_avg
int2int4_sum
float8_regr_accum
float8_regr_combine
range_intersect_agg_transfn
multirange_intersect_agg_transfn
(19 rows)

And the patch gives you:

postgres=# SELECT
int2_avg_accum_inv('{1,9223372036854775807}'::int8[], -1::int2);
ERROR: function int2_avg_accum_inv(bigint[], smallint) cannot be
called directly
LINE 1: SELECT int2_avg_accum_inv('{1,9223372036854775807}'::int8[],...
^
DETAIL: This function is for internal use only.

But you can still use these in CREATE AGGREGATE, and I suppose CREATE
OPERATOR too, and even CREATE FUNCTION if you want to bypass it.

I'm not convinced I've got the prointernal checks in all the right
places, and there are probably more or less functions that need marked
as internal. Certainly, the aggregate.sql tests are failing because
I've blocked functions like float8_accum and float8_regr_accum. Maybe
we could get around that by doing CREATE FUNCTION and referencing the
core function. I'd need to remove the
Assert(AggCheckCallContext(fcinfo, NULL)) though.

The attached is a proof-of-concept patch for discussion. Is it worth
me continuing with this?

+1 from me. I think this patch creates infra that allows us to make similar functions internal easily.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: David Rowley (#1)
Re: Block various aggregate component functions from being called directly

David Rowley <dgrowleyml@gmail.com> writes:

I think we should just block these from being called directly by
adding a new column to pg_proc that allows a function to be marked as
internal.

+1, although instead of a new column, would it make more sense to
invent a new prokind value?

I don't see any need to have CREATE FUNCTION support this
for UDFs,

-1. Extensions could have use for doing this too, and not allowing
extensions to have access to a feature the core code has is kind
of antithetical to project spirit IMO.

regards, tom lane

#4Zsolt Parragi
zsolt.parragi@percona.com
In reply to: David Rowley (#1)
Re: Block various aggregate component functions from being called directly

On Thu, 19 Nov 2026, David Rowley <dgrowleyml@gmail.com> wrote:

We're getting bug reports (e.g., [1][2]) complaining about various
aggregate component functions that don't do the right thing in some
weird cases. E.g., no overflow error from:

SELECT int2_avg_accum_inv('{1,9223372036854775807}'::int8[], -1::int2);

But who would do that?!

I agree with the part that these report make little sense. While in an ideal world there would be no bugs, even nitpick ones like this, the reality is that there are far more important things to fix.

My approach to dealing with these generally is to try to keep new code as correct as possible, including fixing even unlikely corner case bugs when working on a specific area of the code, but otherwise leave existing code as-is unless there is another good reason for editing it, at which point I might as well fix that too. This of course only helps with llm self-reviews, and doesn't make outside bug reports less likely, other than the part where it slowly makes the codebase better.

But you can still use these in CREATE AGGREGATE, and I suppose CREATE
OPERATOR too, and even CREATE FUNCTION if you want to bypass it.

...

The attached is a proof-of-concept patch for discussion. Is it worth
me continuing with this?

That part is the problem, for example Claude still happily points out that even with the patch:

CREATE AGGREGATE my_inv(int2) (sfunc = int2_avg_accum_inv, stype = int8[],
initcond = '{1,9223372036854775807}');
SELECT my_inv(-1::int2);
my_inv
--------------------------
{0,-9223372036854775808}

So I don't think this change would prevent from these reports, they will just find different reproducers. It might have the opposite effect, where because of the chance more agents start to look into this area, and send even more bug reports.

It seems like the new reality that people are using LLMs or fuzz
testing, and the robots are classifying these as bugs, and we're
getting reports complaining about it.

Also, you don't even need fuzzing for this, LLMs quite often find these type of issues simply by code reading and reasoning. My setups have very detailed instructions about what to classify as bugs and how to triage their seriousness, but even with that they sometimes do report issues like this as critical. Even if we manage to completely hide these cases from the SQL interface (and I'm not sure if that is possible), they could still find and report it.