On Thu, 29 Oct 2026 at 04:05, Tomas Vondra <[email protected]> wrote: > > > > On 10/28/26 08:02, Kirill Reshke wrote: > > On Wed, 28 Oct 2026 at 01:59, Tomas Vondra <[email protected]> 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.
Yes, but you were wondering about other distance functions, so I started to search for alike problems... and found one that is neither BRIN nor distance, my bad, should be a separate thread. The only thing they share is how fix looks - almost the same code to be changed . > We probably need to fix that anyway, ofc. Having non-analyzable table is > not great. > > > regards > > -- > Tomas Vondra > -- Best regards, Kirill Reshke
