pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: alpkeles99@gmail.com
Subject: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
Date: Thu, 15 Oct 2026 14:09:59 +0000
Message-ID: <19761-b72cfc453e487610@postgresql.org> (raw)

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.








view thread (9+ messages)  latest in thread

Message-ID: <19761-b72cfc453e487610@postgresql.org>
Permalink:  ../19761-b72cfc453e487610@postgresql.org/
Also on:    postgresql.org/message-id/19761-b72cfc453e487610@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, alpkeles99@gmail.com
  Subject: Re: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date
  In-Reply-To: <19761-b72cfc453e487610@postgresql.org>
  References: <19761-b72cfc453e487610@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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