pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
9+ messages / 4 participants
[nested] [flat]

* BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
@ 2026-10-15 14:09 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 09:15 ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  0 siblings, 1 reply; 9+ messages in thread

From: PG Bug reporting form @ 2026-10-15 14:09 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: alpkeles99@gmail.com

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.








^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-16 09:15 ` Nitin Motiani <nitinmotiani@google.com>
  2026-10-23 07:04   ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Nitin Motiani @ 2026-10-16 09:15 UTC (permalink / raw)
  To: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

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
<noreply@postgresql.org> 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

Attachments:

v1-0001-btree_gist-Fix-cannot-subtract-infinite-dates-err.patch (application/x-patch, 4.9K) download From be83b080e4bb361c8c16c5992d3b8ad4ae1355a9 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <nitinmotiani@google.com> Date: Fri, 16 Oct 2026 07:45:01 +0000 Subject: [PATCH v1] btree_gist: Fix "cannot subtract infinite dates" errors. The date opclass's penalty and KNN distance functions used date_mi(), which rejects infinite dates. Once a GiST index on a date column had internal pages, inserting 'infinity' or '-infinity' failed, as did any later insert or KNN search on an index already containing one. Compute the penalty with penalty_num(), as the int4 and timestamp opclasses do, and make the distance function return infinity for infinite inputs, as gbt_ts_dist() does. Reported-by: Alperen Keles <alpkeles99@gmail.com> Discussion: https://postgr.es/m/19761-b72cfc453e487610@postgresql.org --- contrib/btree_gist/btree_date.c | 45 ++++++++++------------------ contrib/btree_gist/expected/date.out | 13 ++++++++ contrib/btree_gist/sql/date.sql | 7 +++++ 3 files changed, 35 insertions(+), 30 deletions(-) diff --git a/contrib/btree_gist/btree_date.c b/contrib/btree_gist/btree_date.c index 1824a3d0e6e..0db640511af 100644 --- a/contrib/btree_gist/btree_date.c +++ b/contrib/btree_gist/btree_date.c @@ -91,12 +91,14 @@ gbt_datekey_cmp(const void *a, const void *b, FmgrInfo *flinfo) static float8 gdb_date_dist(const void *a, const void *b, FmgrInfo *flinfo) { - /* we assume the difference can't overflow */ - Datum diff = DirectFunctionCall2(date_mi, - DateADTGetDatum(*((const DateADT *) a)), - DateADTGetDatum(*((const DateADT *) b))); + DateADT da = *((const DateADT *) a); + DateADT db = *((const DateADT *) b); - return (float8) abs(DatumGetInt32(diff)); + if (DATE_NOT_FINITE(da) || DATE_NOT_FINITE(db)) + return get_float8_infinity(); + + /* the difference of two finite dates always fits in int32 */ + return (float8) abs(da - db); } @@ -206,32 +208,15 @@ gbt_date_penalty(PG_FUNCTION_ARGS) dateKEY *origentry = (dateKEY *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(0))->key); dateKEY *newentry = (dateKEY *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(1))->key); float *result = (float *) PG_GETARG_POINTER(2); - int32 diff, - res; - - diff = DatumGetInt32(DirectFunctionCall2(date_mi, - DateADTGetDatum(newentry->upper), - DateADTGetDatum(origentry->upper))); - - res = Max(diff, 0); - - diff = DatumGetInt32(DirectFunctionCall2(date_mi, - DateADTGetDatum(origentry->lower), - DateADTGetDatum(newentry->lower))); - - res += Max(diff, 0); - - *result = 0.0; - if (res > 0) - { - diff = DatumGetInt32(DirectFunctionCall2(date_mi, - DateADTGetDatum(origentry->upper), - DateADTGetDatum(origentry->lower))); - *result += FLT_MIN; - *result += (float) (res / ((double) (res + diff))); - *result *= (FLT_MAX / (((GISTENTRY *) PG_GETARG_POINTER(0))->rel->rd_att->natts + 1)); - } + /* + * Don't use date_mi here: it rejects infinite dates, and plain int32 + * subtraction could overflow for them (DATEVAL_NOBEGIN/NOEND are + * INT32_MIN/INT32_MAX). penalty_num computes in double, where every + * int32 and every difference of two int32s is exact. + */ + penalty_num(result, origentry->lower, origentry->upper, + newentry->lower, newentry->upper); PG_RETURN_POINTER(result); } diff --git a/contrib/btree_gist/expected/date.out b/contrib/btree_gist/expected/date.out index 5db864bb82c..7a8e36111a3 100644 --- a/contrib/btree_gist/expected/date.out +++ b/contrib/btree_gist/expected/date.out @@ -89,3 +89,16 @@ SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3; 03-24-2001 | 39 (3 rows) +-- infinite dates in an index with internal pages (penalty and KNN distance) +CREATE TABLE dateinf (a date); +CREATE INDEX dateinfidx ON dateinf USING gist ( a ); +INSERT INTO dateinf SELECT '2000-01-01'::date + i FROM generate_series(1, 1000) i; +INSERT INTO dateinf VALUES ('infinity'), ('-infinity'); +SELECT a FROM dateinf ORDER BY a <-> '2000-01-01'::date LIMIT 3; + a +------------ + 01-02-2000 + 01-03-2000 + 01-04-2000 +(3 rows) + diff --git a/contrib/btree_gist/sql/date.sql b/contrib/btree_gist/sql/date.sql index f969ef0a08c..c371f8d9a2c 100644 --- a/contrib/btree_gist/sql/date.sql +++ b/contrib/btree_gist/sql/date.sql @@ -35,3 +35,10 @@ SELECT count(*) FROM datetmp WHERE a > '2001-02-13'::date; EXPLAIN (COSTS OFF) SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3; SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3; + +-- infinite dates in an index with internal pages (penalty and KNN distance) +CREATE TABLE dateinf (a date); +CREATE INDEX dateinfidx ON dateinf USING gist ( a ); +INSERT INTO dateinf SELECT '2000-01-01'::date + i FROM generate_series(1, 1000) i; +INSERT INTO dateinf VALUES ('infinity'), ('-infinity'); +SELECT a FROM dateinf ORDER BY a <-> '2000-01-01'::date LIMIT 3; --
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 09:15 ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-23 07:04   ` Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-23 13:14     ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Dilip Kumar @ 2026-10-23 07:04 UTC (permalink / raw)
  To: Nitin Motiani <nitinmotiani@google.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Fri, Oct 16, 2026 at 2:45 PM Nitin Motiani <nitinmotiani@google.com> 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
> <noreply@postgresql.org> 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






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 09:15 ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-23 07:04   ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-10-23 13:14     ` Nitin Motiani <nitinmotiani@google.com>
  2026-10-25 06:20       ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Nitin Motiani @ 2026-10-23 13:14 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Fri, Oct 23, 2026 at 12:34 PM Dilip Kumar <dilipbalaut@gmail.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.

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






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 09:15 ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-23 07:04   ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-23 13:14     ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-25 06:20       ` Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-25 11:35         ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Dilip Kumar @ 2026-10-25 06:20 UTC (permalink / raw)
  To: Nitin Motiani <nitinmotiani@google.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 09:15 ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-23 07:04   ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-23 13:14     ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-25 06:20       ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-10-25 11:35         ` Nitin Motiani <nitinmotiani@google.com>
  2026-10-26 11:21           ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-26 16:10           ` 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>
  0 siblings, 2 replies; 9+ messages in thread

From: Nitin Motiani @ 2026-10-25 11:35 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Sun, Oct 25, 2026 at 11:51 AM Dilip Kumar <dilipbalaut@gmail.com> 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

Attachments:

v2-0001-btree_gist-Fix-cannot-subtract-infinite-dates-err.patch (application/x-patch, 7.2K) download From c090b789114a424ff2672f59b6d0cbb8acfaa4b2 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <nitinmotiani@google.com> Date: Fri, 16 Oct 2026 07:45:01 +0000 Subject: [PATCH v2] btree_gist: Fix "cannot subtract infinite dates" errors. The date opclass's penalty and distance functions used date_mi(), which rejects infinite dates. Once a GiST index on a date column had internal pages, inserting 'infinity' or '-infinity' failed, as did any later insert or KNN search on an index already containing one. The date <-> date operator failed the same way, so ORDER BY d <-> x could succeed with a KNN index scan but fail with a sort. Compute the penalty with penalty_num(), as the int4 and timestamp opclasses do. Make the KNN distance function return infinity for infinite inputs, as gbt_ts_dist() does, and make the <-> operator return INT_MAX for them, as ts_dist() does. No difference of two finite dates reaches INT_MAX, so both plans order rows the same way. Reported-by: Alperen Keles <alpkeles99@gmail.com> Discussion: https://postgr.es/m/19761-b72cfc453e487610@postgresql.org --- contrib/btree_gist/btree_date.c | 65 +++++++++++++--------------- contrib/btree_gist/expected/date.out | 43 ++++++++++++++++++ contrib/btree_gist/sql/date.sql | 17 ++++++++ 3 files changed, 89 insertions(+), 36 deletions(-) diff --git a/contrib/btree_gist/btree_date.c b/contrib/btree_gist/btree_date.c index 1824a3d0e6e..f1f3cba65b0 100644 --- a/contrib/btree_gist/btree_date.c +++ b/contrib/btree_gist/btree_date.c @@ -91,12 +91,14 @@ gbt_datekey_cmp(const void *a, const void *b, FmgrInfo *flinfo) static float8 gdb_date_dist(const void *a, const void *b, FmgrInfo *flinfo) { - /* we assume the difference can't overflow */ - Datum diff = DirectFunctionCall2(date_mi, - DateADTGetDatum(*((const DateADT *) a)), - DateADTGetDatum(*((const DateADT *) b))); + DateADT da = *((const DateADT *) a); + DateADT db = *((const DateADT *) b); - return (float8) abs(DatumGetInt32(diff)); + if (DATE_NOT_FINITE(da) || DATE_NOT_FINITE(db)) + return get_float8_infinity(); + + /* the difference of two finite dates always fits in int32 */ + return (float8) abs(da - db); } @@ -119,12 +121,20 @@ PG_FUNCTION_INFO_V1(date_dist); Datum date_dist(PG_FUNCTION_ARGS) { - /* we assume the difference can't overflow */ - Datum diff = DirectFunctionCall2(date_mi, - PG_GETARG_DATUM(0), - PG_GETARG_DATUM(1)); - - PG_RETURN_INT32(abs(DatumGetInt32(diff))); + DateADT a = PG_GETARG_DATEADT(0); + DateADT b = PG_GETARG_DATEADT(1); + + /* + * int4 has no infinity, so saturate for infinite inputs, as ts_dist does + * for timestamps. This is consistent with the ordering produced by + * gdb_date_dist, since no difference of two finite dates reaches + * PG_INT32_MAX. + */ + if (DATE_NOT_FINITE(a) || DATE_NOT_FINITE(b)) + PG_RETURN_INT32(PG_INT32_MAX); + + /* the difference of two finite dates always fits in int32 */ + PG_RETURN_INT32(abs(a - b)); } @@ -206,32 +216,15 @@ gbt_date_penalty(PG_FUNCTION_ARGS) dateKEY *origentry = (dateKEY *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(0))->key); dateKEY *newentry = (dateKEY *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(1))->key); float *result = (float *) PG_GETARG_POINTER(2); - int32 diff, - res; - - diff = DatumGetInt32(DirectFunctionCall2(date_mi, - DateADTGetDatum(newentry->upper), - DateADTGetDatum(origentry->upper))); - - res = Max(diff, 0); - - diff = DatumGetInt32(DirectFunctionCall2(date_mi, - DateADTGetDatum(origentry->lower), - DateADTGetDatum(newentry->lower))); - - res += Max(diff, 0); - - *result = 0.0; - if (res > 0) - { - diff = DatumGetInt32(DirectFunctionCall2(date_mi, - DateADTGetDatum(origentry->upper), - DateADTGetDatum(origentry->lower))); - *result += FLT_MIN; - *result += (float) (res / ((double) (res + diff))); - *result *= (FLT_MAX / (((GISTENTRY *) PG_GETARG_POINTER(0))->rel->rd_att->natts + 1)); - } + /* + * Don't use date_mi here: it rejects infinite dates, and plain int32 + * subtraction could overflow for them (DATEVAL_NOBEGIN/NOEND are + * INT32_MIN/INT32_MAX). penalty_num computes in double, where every + * int32 and every difference of two int32s is exact. + */ + penalty_num(result, origentry->lower, origentry->upper, + newentry->lower, newentry->upper); PG_RETURN_POINTER(result); } diff --git a/contrib/btree_gist/expected/date.out b/contrib/btree_gist/expected/date.out index 5db864bb82c..f32d0f84e93 100644 --- a/contrib/btree_gist/expected/date.out +++ b/contrib/btree_gist/expected/date.out @@ -89,3 +89,46 @@ SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3; 03-24-2001 | 39 (3 rows) +-- infinite dates in an index with internal pages (penalty and KNN distance) +CREATE TABLE dateinf (a date); +CREATE INDEX dateinfidx ON dateinf USING gist ( a ); +INSERT INTO dateinf SELECT '2000-01-01'::date + i FROM generate_series(1, 1000) i; +INSERT INTO dateinf VALUES ('infinity'), ('-infinity'); +SELECT a FROM dateinf ORDER BY a <-> '2000-01-01'::date LIMIT 3; + a +------------ + 01-02-2000 + 01-03-2000 + 01-04-2000 +(3 rows) + +-- same ordering via sort, which evaluates the <-> operator on every row +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_indexonlyscan = off; +EXPLAIN (COSTS OFF) +SELECT a FROM dateinf ORDER BY a <-> '2000-01-01'::date LIMIT 3; + QUERY PLAN +------------------------------------------------ + Limit + -> Sort + Sort Key: ((a <-> '01-01-2000'::date)) + -> Seq Scan on dateinf +(4 rows) + +SELECT a FROM dateinf ORDER BY a <-> '2000-01-01'::date LIMIT 3; + a +------------ + 01-02-2000 + 01-03-2000 + 01-04-2000 +(3 rows) + +RESET enable_indexscan; +RESET enable_indexonlyscan; +SELECT 'infinity'::date <-> '2000-01-01'::date, '-infinity'::date <-> '2000-01-01'::date; + ?column? | ?column? +------------+------------ + 2147483647 | 2147483647 +(1 row) + diff --git a/contrib/btree_gist/sql/date.sql b/contrib/btree_gist/sql/date.sql index f969ef0a08c..f071740ca0c 100644 --- a/contrib/btree_gist/sql/date.sql +++ b/contrib/btree_gist/sql/date.sql @@ -35,3 +35,20 @@ SELECT count(*) FROM datetmp WHERE a > '2001-02-13'::date; EXPLAIN (COSTS OFF) SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3; SELECT a, a <-> '2001-02-13' FROM datetmp ORDER BY a <-> '2001-02-13' LIMIT 3; + +-- infinite dates in an index with internal pages (penalty and KNN distance) +CREATE TABLE dateinf (a date); +CREATE INDEX dateinfidx ON dateinf USING gist ( a ); +INSERT INTO dateinf SELECT '2000-01-01'::date + i FROM generate_series(1, 1000) i; +INSERT INTO dateinf VALUES ('infinity'), ('-infinity'); +SELECT a FROM dateinf ORDER BY a <-> '2000-01-01'::date LIMIT 3; +-- same ordering via sort, which evaluates the <-> operator on every row +SET enable_seqscan = on; +SET enable_indexscan = off; +SET enable_indexonlyscan = off; +EXPLAIN (COSTS OFF) +SELECT a FROM dateinf ORDER BY a <-> '2000-01-01'::date LIMIT 3; +SELECT a FROM dateinf ORDER BY a <-> '2000-01-01'::date LIMIT 3; +RESET enable_indexscan; +RESET enable_indexonlyscan; +SELECT 'infinity'::date <-> '2000-01-01'::date, '-infinity'::date <-> '2000-01-01'::date; --
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 09:15 ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-23 07:04   ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-23 13:14     ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-25 06:20       ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-25 11:35         ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-26 11:21           ` Nitin Motiani <nitinmotiani@google.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Nitin Motiani @ 2026-10-26 11:21 UTC (permalink / raw)
  To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Sun, Oct 25, 2026 at 5:05 PM Nitin Motiani <nitinmotiani@google.com> 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






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 09:15 ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-23 07:04   ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-23 13:14     ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-25 06:20       ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-25 11:35         ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-26 16:10           ` Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-27 03:59             ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Tom Lane @ 2026-10-26 16:10 UTC (permalink / raw)
  To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

Nitin Motiani <nitinmotiani@google.com> 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






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 09:15 ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-23 07:04   ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-23 13:14     ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-25 06:20       ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Dilip Kumar <dilipbalaut@gmail.com>
  2026-10-25 11:35         ` Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date Nitin Motiani <nitinmotiani@google.com>
  2026-10-26 16:10           ` 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>
@ 2026-10-27 03:59             ` Nitin Motiani <nitinmotiani@google.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Nitin Motiani @ 2026-10-27 03:59 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org

On Mon, Oct 26, 2026 at 9:40 PM Tom Lane <tgl@sss.pgh.pa.us> 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






^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2026-10-27 03:59 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-15 14:09 BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date PG Bug reporting form <noreply@postgresql.org>
2026-10-16 09:15 ` Nitin Motiani <nitinmotiani@google.com>
2026-10-23 07:04   ` Dilip Kumar <dilipbalaut@gmail.com>
2026-10-23 13:14     ` Nitin Motiani <nitinmotiani@google.com>
2026-10-25 06:20       ` Dilip Kumar <dilipbalaut@gmail.com>
2026-10-25 11:35         ` Nitin Motiani <nitinmotiani@google.com>
2026-10-26 11:21           ` Nitin Motiani <nitinmotiani@google.com>
2026-10-26 16:10           ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-27 03:59             ` Nitin Motiani <nitinmotiani@google.com>

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