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

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.