Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date

On Fri, Oct 23, 2026 at 6:44 PM Nitin Motiani <nitinmotiani(at)google(dot)com> wrote:
>
> On Fri, Oct 23, 2026 at 12:34 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> 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