pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17302: gist index prevents insertion of some data
4+ messages / 4 participants
[nested] [flat]
* BUG #17302: gist index prevents insertion of some data
@ 2021-11-28 18:00 PG Bug reporting form <noreply@postgresql.org>
2021-12-01 22:08 ` Re: BUG #17302: gist index prevents insertion of some data Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2021-11-28 18:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 17302
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 14.1
Operating system: Ubuntu 20.04
Description:
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17302: gist index prevents insertion of some data
2021-11-28 18:00 BUG #17302: gist index prevents insertion of some data PG Bug reporting form <noreply@postgresql.org>
@ 2021-12-01 22:08 ` Alexander Korotkov <aekorotkov@gmail.com>
2021-12-01 22:14 ` Re: BUG #17302: gist index prevents insertion of some data Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Alexander Korotkov @ 2021-12-01 22:08 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; +Cc: Alexander Law <exclusion@gmail.com>
On Sun, Nov 28, 2021 at 9:07 PM PG Bug reporting form
<noreply@postgresql.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17302: gist index prevents insertion of some data
2021-11-28 18:00 BUG #17302: gist index prevents insertion of some data PG Bug reporting form <noreply@postgresql.org>
2021-12-01 22:08 ` Re: BUG #17302: gist index prevents insertion of some data Alexander Korotkov <aekorotkov@gmail.com>
@ 2021-12-01 22:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-02 06:02 ` Re: BUG #17302: gist index prevents insertion of some data Darafei "Komяpa" Praliaskouski <me@komzpa.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-12-01 22:14 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Alexander Law <exclusion@gmail.com>
Alexander Korotkov <aekorotkov@gmail.com> writes:
> 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.
Check.
> Probably, we could even tolerate overflow in the gist penalty.
As long as overflow -> infinity, yeah I think so. Seems like it
was a mistake to insert the overflow-testing functions in this code
at all, and we should simplify it down to plain C addition/subtraction/
multiplication.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17302: gist index prevents insertion of some data
2021-11-28 18:00 BUG #17302: gist index prevents insertion of some data PG Bug reporting form <noreply@postgresql.org>
2021-12-01 22:08 ` Re: BUG #17302: gist index prevents insertion of some data Alexander Korotkov <aekorotkov@gmail.com>
2021-12-01 22:14 ` Re: BUG #17302: gist index prevents insertion of some data Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-12-02 06:02 ` Darafei "Komяpa" Praliaskouski <me@komzpa.net>
0 siblings, 0 replies; 4+ messages in thread
From: Darafei "Komяpa" Praliaskouski @ 2021-12-02 06:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alexander Korotkov <aekorotkov@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Alexander Law <exclusion@gmail.com>
On Thu, Dec 2, 2021 at 1:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alexander Korotkov <aekorotkov@gmail.com> writes:
> > 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.
>
> Check.
>
> > Probably, we could even tolerate overflow in the gist penalty.
>
> As long as overflow -> infinity, yeah I think so. Seems like it
> was a mistake to insert the overflow-testing functions in this code
> at all, and we should simplify it down to plain C addition/subtraction/
> multiplication.
>
The underflow should not throw an interrupting exception ever, even on
plain SQL-level calculations.
The code to implement was added in error by a series of misunderstandings
and gets in the way of simple things too often. I dug into the history here:
https://www.postgresql.org/message-id/CAC8Q8t%2BXJH68WB%2BsKN0BV0uGc3ZjA2DtbQuoJ5EhB4JAcS0C%2BQ%40ma...
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-12-02 06:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-28 18:00 BUG #17302: gist index prevents insertion of some data PG Bug reporting form <noreply@postgresql.org>
2021-12-01 22:08 ` Alexander Korotkov <aekorotkov@gmail.com>
2021-12-01 22:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-02 06:02 ` Darafei "Komяpa" Praliaskouski <me@komzpa.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox