BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19761
Logged by: Alperen Keles
Email address: alpkeles99@gmail.com
PostgreSQL version: 18.6
Operating system: macOS 26 arm64 (also Ubuntu 24.04 x86_64)
Description:
With contrib/btree_gist, a GiST index on a date column rejects the values
'infinity' and '-infinity' once the index has more than one page:
ERROR: 22008: cannot subtract infinite dates
LOCATION: date_mi, date.c:556
Inserting finite dates also fails once the index holds an infinity, and so
does a KNN search (ORDER BY d <-> ...) over such an index. The timestamp and
timestamptz opclasses handle all of these cases.
Versions tested (both behave the same):
- master at 158b6d5109f (PostgreSQL 20devel), --enable-cassert
--enable-debug
- REL_18_STABLE at a85b5523e67 (reports 18.6)
Platform: macOS 26 (Darwin 25.6.0), Apple M-series arm64, Apple clang
21.0.0.
Rechecked on 2026-10-15 (Ubuntu 24.04, x86_64, gcc 13.3, --enable-cassert
--enable-debug, server under ulimit -v 6 GB): the same five errors on
master aa7b9cf132b, 19rc1 (REL_19_STABLE 0fb192f1b02) and REL_18_STABLE
4d62ef92fda.
Fresh initdb, default configuration except fsync=off; empty ~/.psqlrc.
Script:
\set VERBOSITY verbose
CREATE EXTENSION btree_gist;
CREATE TABLE t (d date);
CREATE INDEX t_d_idx ON t USING gist (d);
-- about 410+ rows are enough for the index to grow past one page
INSERT INTO t SELECT date '2000-01-01' + i FROM generate_series(1, 500) i;
INSERT INTO t VALUES ('infinity');
INSERT INTO t VALUES ('-infinity');
-- an index built over rows that already hold infinities (sorted build)
-- succeeds, but later inserts and KNN searches fail
CREATE TABLE u (d date);
INSERT INTO u SELECT date '2000-01-01' + i FROM generate_series(1, 500) i;
INSERT INTO u VALUES ('infinity'), ('-infinity');
CREATE INDEX u_d_idx ON u USING gist (d);
INSERT INTO u VALUES ('2000-06-01');
SET enable_seqscan = off;
SELECT d FROM u ORDER BY d <-> date '2000-06-01' LIMIT 1;
-- the scalar operator rejects infinity as well
SELECT date 'infinity' <-> date '2000-01-01';
-- for comparison, timestamp handles all of this
CREATE TABLE ts (x timestamp);
CREATE INDEX ts_x_idx ON ts USING gist (x);
INSERT INTO ts SELECT timestamp '2000-01-01' + i * interval '1 day' FROM
generate_series(1, 500) i;
INSERT INTO ts VALUES ('infinity'), ('-infinity');
SELECT timestamp 'infinity' <-> timestamp '2000-01-01';
Actual output (master; 18 is identical except date.c:560):
INSERT 0 500
ERROR: 22008: cannot subtract infinite dates <- INSERT 'infinity'
ERROR: 22008: cannot subtract infinite dates <- INSERT '-infinity'
CREATE INDEX <- u_d_idx builds fine
ERROR: 22008: cannot subtract infinite dates <- INSERT '2000-06-01'
into u
ERROR: 22008: cannot subtract infinite dates <- ORDER BY d <-> ...
LIMIT 1
ERROR: 22008: cannot subtract infinite dates <- date 'infinity' <->
date
INSERT 0 2 <- timestamp infinities
OK
infinity <- timestamp <-> result
Expected output: the inserts succeed, the KNN query returns 2000-06-01, and
date <-> returns a saturated value instead of an error, as it does for
timestamp. The table accepts these values without the index, and so does a
btree index.
UPDATE ... SET d = 'infinity' and COPY fail the same way. So does
CREATE INDEX ... WITH (buffering = on) over a table that holds infinities,
since that path does not use the sorted build. Before the sorted build for
btree_gist (e4309f73f69, v18), I believe a plain CREATE INDEX over such a
table would fail as well, but I did not test older branches.
Cause: contrib/btree_gist/btree_date.c computes key differences with
DirectFunctionCall2(date_mi, ...). Since date got infinities in 8.4
(a303e4dc431), date_mi raises an error for infinite input
(src/backend/utils/adt/date.c:553-556). The calls are:
- gbt_date_penalty, lines 212, 218, 228. gistchoose calls it on every
insert once the root is an internal page, which is why a single-page
index does not show the problem. Backtrace (backtrace_functions =
'date_mi'): date_mi <- gbt_date_penalty <- gistchoose <- gistdoinsert.
- gdb_date_dist, line 95 (KNN distance, via gbt_date_distance and
gistScanPage).
- date_dist, line 123 (the date <-> date operator).
btree_ts.c avoids this. gbt_ts_penalty does its own double arithmetic on the
raw int64 values, gbt_ts_dist returns get_float8_infinity() for non-finite
input, and ts_dist/tstz_dist return a saturated interval.
Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
Hi, Thanks for the analysis. I was able to reproduce the issue. On Thu, Oct 15, 2026 at 9:03 PM PG Bug reporting form wrote: > > btree_ts.c avoids this. gbt_ts_penalty does its own double arithmetic on the > raw int64 values, gbt_ts_dist returns get_float8_infinity() for non-finite > input, and ts_dist/tstz_dist return a saturated interval. > I'm attaching a patch which uses the same logic as btree_ts.c and btree_int.c in btree_date.co to handle these issues. Please take a look. Thanks & Regards Nitin Motiani, Google
Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
От:
Dilip Kumar <dilipbalaut@gmail.com>
Дата:
On Fri, Oct 16, 2026 at 2:45 PM Nitin Motiani wrote: > > Hi, > > Thanks for the analysis. I was able to reproduce the issue. > > On Thu, Oct 15, 2026 at 9:03 PM PG Bug reporting form > wrote: > > > > btree_ts.c avoids this. gbt_ts_penalty does its own double arithmetic on the > > raw int64 values, gbt_ts_dist returns get_float8_infinity() for non-finite > > input, and ts_dist/tstz_dist return a saturated interval. > > > > I'm attaching a patch which uses the same logic as btree_ts.c and > btree_int.c in btree_date.co to handle these issues. Please take a > look. So IIUC the issue relates to the btree date handling function and not to gist? If so we can use a normal btree index in the new test cases we have added instead of gist. I haven't yet reviewed the code completely but I will do it soon. -- Regards, Dilip Kumar Google
Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
От:
Nitin Motiani <nitinmotiani@google.com>
Дата:
On Fri, Oct 23, 2026 at 12:34 PM Dilip Kumar wrote: > > So IIUC the issue relates to the btree date handling function and not > to gist? If so we can use a normal btree index in the new test cases > we have added instead of gist. I haven't yet reviewed the code > completely but I will do it soon. > Thanks for taking a look, Dilip. The issue is specific to GiST. contrib/btree_gist/btree_date.c is the GiST operator class for date in the btree_gist extension, not btree code[1]. The failing functions are its GiST penalty and distance support functions (gbt_date_penalty and gdb_date_dist). GiST calls them when routing inserts through internal pages and for KNN searches. A regular btree index on date never calls these functions. I tested the standard btree date functions with infinity, -infinity and they pass without the fix. That's why the test uses a GiST index with enough rows to have internal pages. On unpatched code, the INSERT of infinite dates fails in the penalty function, and the KNN query fails in the distance function. With the patch, both pass. One question which I'm unclear on : the bug report also noted that the SQL operator date <-> date (date_dist in btree_date.c) fails on +/-infinity. In v1, I left date_dist unchanged because its return type is int4, which has no infinity representation (similar to int4_dist raising an error when the distance cannot fit in int4). The alternative is to make date_dist return PG_INT32_MAX for infinite inputs, similar to how ts_dist saturates for timestamp. Do you think we should saturate date_dist to PG_INT32_MAX as well, or keep the error for the SQL operator? Regards, Nitin Motiani Google [1] https://www.postgresql.org/docs/current/btree-gist.html
Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
От:
Dilip Kumar <dilipbalaut@gmail.com>
Дата:
On Fri, Oct 23, 2026 at 6:44 PM Nitin Motiani wrote:
>
> On Fri, Oct 23, 2026 at 12:34 PM Dilip Kumar wrote:
> >
> > So IIUC the issue relates to the btree date handling function and not
> > to gist? If so we can use a normal btree index in the new test cases
> > we have added instead of gist. I haven't yet reviewed the code
> > completely but I will do it soon.
> >
>
> Thanks for taking a look, Dilip. The issue is specific to GiST.
> contrib/btree_gist/btree_date.c is the GiST operator class for date in
> the btree_gist extension, not btree code[1]. The failing functions are
> its GiST penalty and distance support functions (gbt_date_penalty and
> gdb_date_dist). GiST calls them when routing inserts through internal
> pages and for KNN searches. A regular btree index on date never calls
> these functions. I tested the standard btree date functions with
> infinity, -infinity and they pass without the fix.
>
> That's why the test uses a GiST index with enough rows to have
> internal pages. On unpatched code, the INSERT of infinite dates fails
> in the penalty function, and the KNN query fails in the distance
> function. With the patch, both pass.
Okay that make sense.
> One question which I'm unclear on : the bug report also noted that the
> SQL operator date <-> date (date_dist in btree_date.c) fails on
> +/-infinity. In v1, I left date_dist unchanged because its return type
> is int4, which has no infinity representation (similar to int4_dist
> raising an error when the distance cannot fit in int4). The
> alternative is to make date_dist return PG_INT32_MAX for infinite
> inputs, similar to how ts_dist saturates for timestamp. Do you think
> we should saturate date_dist to PG_INT32_MAX as well, or keep the
> error for the SQL operator?
I think we must fix date_dist as well otherwise it may cause
inconsistent behavior between index scan and sequence scan, see below
example
CREATE TABLE t (d date);
CREATE INDEX t_d_idx ON t USING gist (d);
INSERT INTO t SELECT date '2000-01-01' + i FROM generate_series(1, 500) i;
INSERT INTO t VALUES ('infinity');
INSERT INTO t VALUES ('-infinity');
postgres[3265593]=# SELECT d FROM t ORDER BY d <-> '2000-06-01' LIMIT 1;
d
------------
2000-06-01
(1 row)
-- Now test by turning off the index scan
postgres[3265593]=# set enable_indexscan =off;
postgres[3265593]=# SELECT d FROM t ORDER BY d <-> '2000-06-01' LIMIT 1;
2026-10-25 06:15:58.591 UTC [3265593] ERROR: cannot subtract infinite dates
2026-10-25 06:15:58.591 UTC [3265593] STATEMENT: SELECT d FROM t
ORDER BY d <-> '2000-06-01' LIMIT 1;
ERROR: 22008: cannot subtract infinite dates
LOCATION: date_mi, date.c:554
--
Regards,
Dilip Kumar
Google
Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
On Sun, Oct 25, 2026 at 11:51 AM Dilip Kumar wrote:
>
> I think we must fix date_dist as well otherwise it may cause
> inconsistent behavior between index scan and sequence scan, see below
> example
>
> CREATE TABLE t (d date);
> CREATE INDEX t_d_idx ON t USING gist (d);
> INSERT INTO t SELECT date '2000-01-01' + i FROM generate_series(1, 500) i;
> INSERT INTO t VALUES ('infinity');
> INSERT INTO t VALUES ('-infinity');
>
> postgres[3265593]=# SELECT d FROM t ORDER BY d <-> '2000-06-01' LIMIT 1;
> d
> ------------
> 2000-06-01
> (1 row)
>
> -- Now test by turning off the index scan
>
> postgres[3265593]=# set enable_indexscan =off;
> postgres[3265593]=# SELECT d FROM t ORDER BY d <-> '2000-06-01' LIMIT 1;
> 2026-10-25 06:15:58.591 UTC [3265593] ERROR: cannot subtract infinite dates
> 2026-10-25 06:15:58.591 UTC [3265593] STATEMENT: SELECT d FROM t
> ORDER BY d <-> '2000-06-01' LIMIT 1;
> ERROR: 22008: cannot subtract infinite dates
> LOCATION: date_mi, date.c:554
>
> --
Thanks for the review, Dilip. I'm attaching the v2 patch which makes
the change in date_dist also.
Regards,
Nitin Motiani
Google
Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
От:
Nitin Motiani <nitinmotiani@google.com>
Дата:
On Sun, Oct 25, 2026 at 5:05 PM Nitin Motiani wrote: > > Thanks for the review, Dilip. I'm attaching the v2 patch which makes > the change in date_dist also. > Hi Dilip, I have created a commitfest entry for this at https://commitfest.postgresql.org/patch/7517/. Thanks & Regards, Nitin Motiani Google
Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Nitin Motiani writes: > Thanks for the review, Dilip. I'm attaching the v2 patch which makes > the change in date_dist also. I think this is not there yet. An important behavior of the corresponding code for float8 (float_penalty_num_impl()) is that it makes the distance between two same-sign infinity values be zero, not infinity. This is mathematically wrong but we must do it for indexing purposes, else inserting multiple infinities will end up splitting each infinity onto its own index page when we want all identical infinities to pack onto as few index pages as possible. BTW, while checking this I happened to notice that gbt_float8_dist has a variant of the same problem: r = arg1 - arg2; if (unlikely(isinf(r)) && !isinf(arg1) && !isinf(arg2)) float_overflow_error(); I don't think we want an error thrown there either; returning the infinite distance (or DBL_MAX?) seems better. regards, tom lane
Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
От:
Nitin Motiani <nitinmotiani@google.com>
Дата:
On Mon, Oct 26, 2026 at 9:40 PM Tom Lane wrote: > > I think this is not there yet. An important behavior of the > corresponding code for float8 (float_penalty_num_impl()) is that > it makes the distance between two same-sign infinity values be zero, > not infinity. This is mathematically wrong but we must do it for > indexing purposes, else inserting multiple infinities will end up > splitting each infinity onto its own index page when we want all > identical infinities to pack onto as few index pages as possible. > > BTW, while checking this I happened to notice that > gbt_float8_dist has a variant of the same problem: > > r = arg1 - arg2; > if (unlikely(isinf(r)) && !isinf(arg1) && !isinf(arg2)) > float_overflow_error(); > > I don't think we want an error thrown there either; > returning the infinite distance (or DBL_MAX?) seems better. > Thanks a lot for the review, Tom. I'll be able to work on this in a couple of weeks. Will look into it in detail and send the updated patch at that time. Regards, Nitin Motiani Google