Re: BUG #17302: gist index prevents insertion of some data

On Sun, Nov 28, 2021 at 9:07 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> The last statement in the following sequence of queries:
> CREATE TABLE point_tbl (f1 point);
> CREATE INDEX gpointind ON point_tbl USING gist (f1);
> INSERT INTO point_tbl SELECT '(0,0)'::point FROM generate_series(1, 1000)
> g;
> INSERT INTO point_tbl VALUES ('(1e-300,-1e-300)'::point);
> produces:
> ERROR: value out of range: underflow
> (The error occurs inside gist_box_penalty()->box_penalty()->size_box().)
> But the following sequence:
> CREATE TABLE point_tbl (f1 point);
> INSERT INTO point_tbl SELECT '(0,0)'::point FROM generate_series(1, 1000)
> g;
> INSERT INTO point_tbl VALUES ('(1e-300,-1e-300)'::point);
> executes without an error. Moreover, the same index can be created
> successfully after the insertion. The error is also depends on number of the
> points inserted in the first step.

I think losing precision in the gist penalty is generally OK. Thus,
it shouldn't be a problem to round a very small value as zero.
Probably, we could even tolerate overflow in the gist penalty. Should
be much worse than underflow, because we might consider a very bad
penalty as very good (or vise versa). But it still affects only index
quality, not correctness.

Any thoughts?

------
Regards,
Alexander Korotkov