Re: Fix BRIN numeric_minmax_multi_ops out of range value range merging

On Wed, 28 Oct 2026 at 01:59, Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>
> On 10/27/26 19:59, Kirill Reshke wrote:
> > Hi
> > While reviewing some reports in pgsql-bugs I bumped into simple SQL
> > reproduced of BRIN out of range issue
> >
> > CREATE TABLE t (n numeric);
> > INSERT INTO t VALUES (1), (1e1000);
> > CREATE INDEX ON t USING brin (n numeric_minmax_multi_ops);
> >
> > This fails. brin_minmax_multi_distance_numeric computes heuristic, so
> > it can be approximated. Patch uses numeric_sub_safe with an
> > ErrorSaveContext and returns inf if any error occurred (which can only
> > be an overflow in current code base, see make_result_safe)
> >
> > The numeric-distance overflow risk was discussed during the original
> > minmax-multi development in 2018 [0], with normalizing value into 0-1
> > range. I didn't fix that way, the issue fixed in the current
> > implementation.
> >
>
> Thanks for the report. I'll take a closer look at the fix, but it seems
> like the right approach to me.

Thanks for looking.

> I wonder if we need to do anything for the other distance functions.

Looks like there is an same issue with numrange_subdiff, like

postgres=# SELECT numrange_subdiff('1e1000'::numeric, '2e1000'::numeric);
ERROR: "-10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
is out of range for type double precision

Attached fixes numrange_subdiff in the same way as brin numeric distance.

numrange_subdiff is called from the planner without try/catch or
anything, so this overflow impact should be query failure, not index
build failure.

I did not however reproduced query failure, because of ANALYZE failure:

```
postgres=# CREATE TABLE t2 (r nummultirange);
CREATE TABLE
postgres=# INSERT INTO t2 VALUES (
'{[1e1000, 2e1000], [-9e131071, 9e131071]}'::nummultirange
);
INSERT 0 1
postgres=# ANALYZE t;
ERROR: value overflows numeric format
```

so a table with extreme numeric bounds cannot be analyzed at all.

> Those can't fail like this, because the subtraction happens without a
> function call (or a check). AFAICS it should not lead to bug/failure,
> except maybe inefficient index.
>
> But overflows are undefined behavior (at least for the integer types),
> so maybe we should do something about it anyway.
>
>
> regards
>
> --
> Tomas Vondra
>

--
Best regards,
Kirill Reshke