Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHNUI-00000002yGH-3YWN for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Oct 2026 15:35:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xHNUH-00000009n5l-3bOL for pgsql-bugs@arkaria.postgresql.org; Thu, 15 Oct 2026 15:35:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHM9o-00000009DPN-03Q7 for pgsql-bugs@lists.postgresql.org; Thu, 15 Oct 2026 14:10:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHM9l-00000002Fwg-3VDu for pgsql-bugs@lists.postgresql.org; Thu, 15 Oct 2026 14:10:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=e78fTc3aSLgaSa4W3sf9TjWYWjGCUQcyN03yvcIO5Eg=; b=6lS+vDm6J9VPP/FXNqBtbhC/xp rC/bi1rbQcNIluhjWKfkLoPAcAyzcoHVdnRZnO6QM1tNBMP2TGR5RMHHNINDK4x8qmSazPJBuZYGm 0HWw1Dqf69RWr5B8SxMQojctahXgiJsS0KXWIB8697p1dQbg2cdtCduR+oFsp0KFvx7ZlqFvrCBgQ ryCpsvKMU/UizqBFPMeDJoztEDT47eYZte9iSohoDvRAAM/NoKQiP2mQtnA1dFTfsa9L2B8/MsVdB QdTfs73UyrITPjy7RHK3Yc3d9mQgL/nkdi9MridNh2oT8PQe/KSVUTHDLgDLACzv3fwI44axcRN2O esEtLfsw==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHM9k-00000006Hmk-2W07 for pgsql-bugs@lists.postgresql.org; Thu, 15 Oct 2026 14:10:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xHM9j-00000001fHW-2KYL for pgsql-bugs@lists.postgresql.org; Thu, 15 Oct 2026 14:10:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19761: btree_gist: "cannot subtract infinite dates" when inserting 'infinity' into a GiST index on date To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: alpkeles99@gmail.com Reply-To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Date: Thu, 15 Oct 2026 14:09:59 +0000 Message-ID: <19761-b72cfc453e487610@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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=3Doff; 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 =3D 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 =3D 'infinity' and COPY fail the same way. So does CREATE INDEX ... WITH (buffering =3D 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 =3D '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.