Re: Fix BRIN numeric_minmax_multi_ops out of range value range merging

On 10/28/26 08:02, Kirill Reshke wrote:
> 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.
>

Ah, but that's completely unrelated to the BRIN code, right? I was
puzzled for a bit, because I don't think we have minmax-multi opclasses
for [multi]ranges.

We probably need to fix that anyway, ofc. Having non-analyzable table is
not great.

regards

--
Tomas Vondra