public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Nestorov <[email protected]>
To: Maxime Schoemans <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: pgsql-hackers mailing list <[email protected]>
Subject: Re: [PATCH] btree_gist: add cross-type integer operator support for GiST
Date: Wed, 24 Jun 2026 17:34:39 +0200
Message-ID: <aea734bb-fd8d-4642-8972-2a57e5643a03@Spark> (raw)
In-Reply-To: <[email protected]>
References: <aac10ffa-a0ca-4c49-846b-3655cbc6b37e@Spark>
<36b4f67d-5975-452c-a6b8-b6407f0924ee@Spark>
<[email protected]>
<18e88767-6c31-402a-887e-37c38b366a6a@Spark>
<80ef3b41-1a71-47a4-a320-29e118d7092c@Spark>
<[email protected]>
<ac05a661-53a5-478a-9907-f56a960dab51@Spark>
<[email protected]>
Hey Maxime!
I squashed your code-related changes on top of 0001, and the test-related changes into 0002.
I also moved my test-related changes (Makefile / meson.build) from 0001 to 0002.
I squashed the remaining fixes (the InvalidOid and the pgindent; 0005) into 0001 as well, so now we have:
- 0001 - code-related changes
- 0002 - test-related changes
- 0003 - docs changes
I also added one more test to 0002 ("Width-sensitivity guard") and added the commit messages.
Last but not least, I preserved your changes as co-author in 0001 and 0002.
I hope I didn't miss anything!
Best regards!
Attachments:
[application/octet-stream] v5-0001-Implement-cross-type-operators-for-GiST-indexes.patch (30.4K, 3-v5-0001-Implement-cross-type-operators-for-GiST-indexes.patch)
download | inline diff:
From 3800bca3c6be27f0077e5cb5232e56af1b40dc53 Mon Sep 17 00:00:00 2001
From: Alexander Nestorov <[email protected]>
Date: Thu, 4 Jun 2026 00:06:54 +0200
Subject: [PATCH] Implement cross-type operators for GiST indexes
btree_gist's GiST opclasses were same-type only: the planner could use
an index only when the query value's type exactly matched the indexed
column. A common case like an int8 column compared against an int4
literal (WHERE bigint_col = 42) therefore could not use the index
unless the query was written with an explicit cast, which is easy to
forget and which ORMs and parameter binding routinely get wrong.
Add cross-type query operator support among the integer trio (int2,
int4, int8) to the gist_int2_ops, gist_int4_ops and gist_int8_ops
families. Each family gains the six B-tree comparison strategies
(<, <=, =, >=, >, <>) and the <-> distance operator against the other
two integer types.
The additions are deliberately pg_amop-only: GiST's amvalidate requires
support functions in a family to have matching left/right input types,
so no cross-type consistent/distance functions are registered. Instead
the existing support functions dispatch on the operator's subtype OID.
Same-type queries take the normal path; cross-type queries select a
comparison/distance callback that reads the query and key sides at their
own widths, so out-of-range query constants compare by normal integer
semantics without being narrowed to the column type.
To let the cross-type path reuse gbt_num_consistent(), all comparison
callbacks are now invoked as f(query, key): query on the left, key on
the right; one call site whose order differed is normalized. Note that
the subtype is InvalidOid for exclusion-constraint and temporal PK/FK
checks, which is handled as the same-type case.
This is exposed as btree_gist version 1.10.
Co-authored-by: Maxime Schoemans <[email protected]>
---
contrib/btree_gist/Makefile | 2 +-
contrib/btree_gist/btree_gist--1.9--1.10.sql | 134 +++++++++++++++++++
contrib/btree_gist/btree_gist.control | 2 +-
contrib/btree_gist/btree_int2.c | 130 ++++++++++++++++--
contrib/btree_gist/btree_int4.c | 130 ++++++++++++++++--
contrib/btree_gist/btree_int8.c | 130 ++++++++++++++++--
contrib/btree_gist/btree_utils_num.c | 16 ++-
contrib/btree_gist/btree_utils_num.h | 39 +++++-
contrib/btree_gist/meson.build | 1 +
src/tools/pgindent/typedefs.list | 1 +
10 files changed, 547 insertions(+), 38 deletions(-)
create mode 100644 contrib/btree_gist/btree_gist--1.9--1.10.sql
diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile
index fbbbca95598..380c7642fde 100644
--- a/contrib/btree_gist/Makefile
+++ b/contrib/btree_gist/Makefile
@@ -32,13 +32,13 @@ OBJS = \
EXTENSION = btree_gist
DATA = btree_gist--1.0--1.1.sql \
btree_gist--1.1--1.2.sql btree_gist--1.2--1.3.sql \
btree_gist--1.3--1.4.sql btree_gist--1.4--1.5.sql \
btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql \
btree_gist--1.7--1.8.sql btree_gist--1.8--1.9.sql \
- btree_gist--1.9.sql
+ btree_gist--1.9.sql btree_gist--1.9--1.10.sql
PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes"
REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \
time timetz date interval macaddr macaddr8 inet cidr text varchar char \
bytea bit varbit numeric uuid not_equal enum bool partitions \
stratnum without_overlaps
diff --git a/contrib/btree_gist/btree_gist--1.9--1.10.sql b/contrib/btree_gist/btree_gist--1.9--1.10.sql
new file mode 100644
index 00000000000..9cd57455086
--- /dev/null
+++ b/contrib/btree_gist/btree_gist--1.9--1.10.sql
@@ -0,0 +1,134 @@
+/* contrib/btree_gist/btree_gist--1.9--1.10.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.10'" to load this file. \quit
+
+-- Add cross-type operator support for the integer trio (int2, int4, int8)
+-- to the existing GiST operator families.
+--
+-- GiST's amvalidate requires support functions in a family to have matching
+-- left/right input types, so the catalog additions below are deliberately
+-- pg_amop-only. The existing consistent/distance support functions dispatch
+-- on the subtype OID: same-type queries take the normal path, while mixed-width
+-- integer queries select a cross-type comparison callback that reads the query
+-- and key sides at their own widths (see btree_int{2,4,8}.c).
+
+CREATE FUNCTION int2_int4_dist(int2, int4)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION int4_int2_dist(int4, int2)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION int2_int8_dist(int2, int8)
+RETURNS int8
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION int8_int2_dist(int8, int2)
+RETURNS int8
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION int4_int8_dist(int4, int8)
+RETURNS int8
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION int8_int4_dist(int8, int4)
+RETURNS int8
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE OPERATOR <-> (
+ LEFTARG = int2,
+ RIGHTARG = int4,
+ PROCEDURE = int2_int4_dist,
+ COMMUTATOR = '<->'
+);
+
+CREATE OPERATOR <-> (
+ LEFTARG = int4,
+ RIGHTARG = int2,
+ PROCEDURE = int4_int2_dist,
+ COMMUTATOR = '<->'
+);
+
+CREATE OPERATOR <-> (
+ LEFTARG = int2,
+ RIGHTARG = int8,
+ PROCEDURE = int2_int8_dist,
+ COMMUTATOR = '<->'
+);
+
+CREATE OPERATOR <-> (
+ LEFTARG = int8,
+ RIGHTARG = int2,
+ PROCEDURE = int8_int2_dist,
+ COMMUTATOR = '<->'
+);
+
+CREATE OPERATOR <-> (
+ LEFTARG = int4,
+ RIGHTARG = int8,
+ PROCEDURE = int4_int8_dist,
+ COMMUTATOR = '<->'
+);
+
+CREATE OPERATOR <-> (
+ LEFTARG = int8,
+ RIGHTARG = int4,
+ PROCEDURE = int8_int4_dist,
+ COMMUTATOR = '<->'
+);
+
+ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD
+ OPERATOR 1 < (int2, int4),
+ OPERATOR 2 <= (int2, int4),
+ OPERATOR 3 = (int2, int4),
+ OPERATOR 4 >= (int2, int4),
+ OPERATOR 5 > (int2, int4),
+ OPERATOR 6 <> (int2, int4),
+ OPERATOR 15 <-> (int2, int4) FOR ORDER BY pg_catalog.integer_ops,
+ OPERATOR 1 < (int2, int8),
+ OPERATOR 2 <= (int2, int8),
+ OPERATOR 3 = (int2, int8),
+ OPERATOR 4 >= (int2, int8),
+ OPERATOR 5 > (int2, int8),
+ OPERATOR 6 <> (int2, int8),
+ OPERATOR 15 <-> (int2, int8) FOR ORDER BY pg_catalog.integer_ops;
+
+ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD
+ OPERATOR 1 < (int4, int2),
+ OPERATOR 2 <= (int4, int2),
+ OPERATOR 3 = (int4, int2),
+ OPERATOR 4 >= (int4, int2),
+ OPERATOR 5 > (int4, int2),
+ OPERATOR 6 <> (int4, int2),
+ OPERATOR 15 <-> (int4, int2) FOR ORDER BY pg_catalog.integer_ops,
+ OPERATOR 1 < (int4, int8),
+ OPERATOR 2 <= (int4, int8),
+ OPERATOR 3 = (int4, int8),
+ OPERATOR 4 >= (int4, int8),
+ OPERATOR 5 > (int4, int8),
+ OPERATOR 6 <> (int4, int8),
+ OPERATOR 15 <-> (int4, int8) FOR ORDER BY pg_catalog.integer_ops;
+
+ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD
+ OPERATOR 1 < (int8, int2),
+ OPERATOR 2 <= (int8, int2),
+ OPERATOR 3 = (int8, int2),
+ OPERATOR 4 >= (int8, int2),
+ OPERATOR 5 > (int8, int2),
+ OPERATOR 6 <> (int8, int2),
+ OPERATOR 15 <-> (int8, int2) FOR ORDER BY pg_catalog.integer_ops,
+ OPERATOR 1 < (int8, int4),
+ OPERATOR 2 <= (int8, int4),
+ OPERATOR 3 = (int8, int4),
+ OPERATOR 4 >= (int8, int4),
+ OPERATOR 5 > (int8, int4),
+ OPERATOR 6 <> (int8, int4),
+ OPERATOR 15 <-> (int8, int4) FOR ORDER BY pg_catalog.integer_ops;
diff --git a/contrib/btree_gist/btree_gist.control b/contrib/btree_gist/btree_gist.control
index 69d9341a0ad..e606fa6551d 100644
--- a/contrib/btree_gist/btree_gist.control
+++ b/contrib/btree_gist/btree_gist.control
@@ -1,6 +1,6 @@
# btree_gist extension
comment = 'support for indexing common datatypes in GiST'
-default_version = '1.9'
+default_version = '1.10'
module_pathname = '$libdir/btree_gist'
relocatable = true
trusted = true
diff --git a/contrib/btree_gist/btree_int2.c b/contrib/btree_gist/btree_int2.c
index cc4b33177e3..8d644579b23 100644
--- a/contrib/btree_gist/btree_int2.c
+++ b/contrib/btree_gist/btree_int2.c
@@ -2,12 +2,13 @@
* contrib/btree_gist/btree_int2.c
*/
#include "postgres.h"
#include "btree_gist.h"
#include "btree_utils_num.h"
+#include "catalog/pg_type.h"
#include "common/int.h"
#include "utils/rel.h"
#include "utils/sortsupport.h"
typedef struct int16key
{
@@ -73,13 +74,12 @@ gbt_int2key_cmp(const void *a, const void *b, FmgrInfo *flinfo)
static float8
gbt_int2_dist(const void *a, const void *b, FmgrInfo *flinfo)
{
return GET_FLOAT_DISTANCE(int16, a, b);
}
-
static const gbtree_ninfo tinfo =
{
gbt_t_int2,
sizeof(int16),
4, /* sizeof(gbtreekey4) */
gbt_int2gt,
@@ -88,12 +88,80 @@ static const gbtree_ninfo tinfo =
gbt_int2le,
gbt_int2lt,
gbt_int2key_cmp,
gbt_int2_dist
};
+/*
+ * Cross-type GiST callbacks: the indexed key is int2, the query is int4 or
+ * int8. Both reuse gbt_num_consistent()/gbt_num_distance() via a tinfo whose
+ * comparison/distance callbacks read the query (left) and key (right) sides at
+ * their own widths. f_cmp is unused on these paths and left NULL.
+ */
+GBT_INT_CMP_FNS(gbt_int2_q4_, int32, int16)
+GBT_INT_CMP_FNS(gbt_int2_q8_, int64, int16)
+
+static const gbtree_ninfo tinfo_q4 =
+{
+ gbt_t_int2,
+ sizeof(int16),
+ 4,
+ gbt_int2_q4_gt,
+ gbt_int2_q4_ge,
+ gbt_int2_q4_eq,
+ gbt_int2_q4_le,
+ gbt_int2_q4_lt,
+ NULL,
+ gbt_int2_q4_dist
+};
+
+static const gbtree_ninfo tinfo_q8 =
+{
+ gbt_t_int2,
+ sizeof(int16),
+ 4,
+ gbt_int2_q8_gt,
+ gbt_int2_q8_ge,
+ gbt_int2_q8_eq,
+ gbt_int2_q8_le,
+ gbt_int2_q8_lt,
+ NULL,
+ gbt_int2_q8_dist
+};
+
+/*
+ * Cross-type dispatch shared by gbt_int2_consistent and gbt_int2_distance:
+ * select the tinfo for the query subtype and read the query value at its own
+ * width into caller-owned storage.
+ */
+static const gbtree_ninfo *
+gbt_int2_crosstype(Oid subtype, Datum d, gbt_intkey *q, const void **qp)
+{
+ switch (subtype)
+ {
+ case InvalidOid: /* same-type: exclusion/temporal constraint
+ * checks pass the native type with subtype 0 */
+ case INT2OID:
+ q->i2 = DatumGetInt16(d);
+ *qp = &q->i2;
+ return &tinfo;
+ case INT4OID:
+ q->i4 = DatumGetInt32(d);
+ *qp = &q->i4;
+ return &tinfo_q4;
+ case INT8OID:
+ q->i8 = DatumGetInt64(d);
+ *qp = &q->i8;
+ return &tinfo_q8;
+ default:
+ elog(ERROR, "unrecognized subtype %u for btree_gist int2 cross-type comparison",
+ subtype);
+ return NULL; /* keep compiler quiet */
+ }
+}
+
PG_FUNCTION_INFO_V1(int2_dist);
Datum
int2_dist(PG_FUNCTION_ARGS)
{
int16 a = PG_GETARG_INT16(0);
@@ -109,12 +177,46 @@ int2_dist(PG_FUNCTION_ARGS)
ra = abs(r);
PG_RETURN_INT16(ra);
}
+PG_FUNCTION_INFO_V1(int2_int4_dist);
+Datum
+int2_int4_dist(PG_FUNCTION_ARGS)
+{
+ int32 a = (int32) PG_GETARG_INT16(0);
+ int32 b = PG_GETARG_INT32(1);
+ int32 r;
+
+ if (pg_sub_s32_overflow(a, b, &r) ||
+ r == PG_INT32_MIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("integer out of range")));
+
+ PG_RETURN_INT32(abs(r));
+}
+
+PG_FUNCTION_INFO_V1(int2_int8_dist);
+Datum
+int2_int8_dist(PG_FUNCTION_ARGS)
+{
+ int64 a = (int64) PG_GETARG_INT16(0);
+ int64 b = PG_GETARG_INT64(1);
+ int64 r;
+
+ if (pg_sub_s64_overflow(a, b, &r) ||
+ r == PG_INT64_MIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("bigint out of range")));
+
+ PG_RETURN_INT64(i64abs(r));
+}
+
/**************************************************
* GiST support functions
**************************************************/
Datum
@@ -134,47 +236,53 @@ gbt_int2_fetch(PG_FUNCTION_ARGS)
}
Datum
gbt_int2_consistent(PG_FUNCTION_ARGS)
{
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
- int16 query = PG_GETARG_INT16(1);
+ Datum queryDatum = PG_GETARG_DATUM(1);
StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
-#ifdef NOT_USED
Oid subtype = PG_GETARG_OID(3);
-#endif
bool *recheck = (bool *) PG_GETARG_POINTER(4);
int16KEY *kkk = (int16KEY *) DatumGetPointer(entry->key);
+ const gbtree_ninfo *ti;
+ gbt_intkey query;
+ const void *qp;
GBT_NUMKEY_R key;
/* All cases served by this function are exact */
*recheck = false;
key.lower = (GBT_NUMKEY *) &kkk->lower;
key.upper = (GBT_NUMKEY *) &kkk->upper;
- PG_RETURN_BOOL(gbt_num_consistent(&key, &query, &strategy,
- GIST_LEAF(entry), &tinfo, fcinfo->flinfo));
+ ti = gbt_int2_crosstype(subtype, queryDatum, &query, &qp);
+
+ PG_RETURN_BOOL(gbt_num_consistent(&key, qp, &strategy, GIST_LEAF(entry),
+ ti, fcinfo->flinfo));
}
Datum
gbt_int2_distance(PG_FUNCTION_ARGS)
{
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
- int16 query = PG_GETARG_INT16(1);
-#ifdef NOT_USED
+ Datum queryDatum = PG_GETARG_DATUM(1);
Oid subtype = PG_GETARG_OID(3);
-#endif
int16KEY *kkk = (int16KEY *) DatumGetPointer(entry->key);
+ const gbtree_ninfo *ti;
+ gbt_intkey query;
+ const void *qp;
GBT_NUMKEY_R key;
key.lower = (GBT_NUMKEY *) &kkk->lower;
key.upper = (GBT_NUMKEY *) &kkk->upper;
- PG_RETURN_FLOAT8(gbt_num_distance(&key, &query, GIST_LEAF(entry),
- &tinfo, fcinfo->flinfo));
+ ti = gbt_int2_crosstype(subtype, queryDatum, &query, &qp);
+
+ PG_RETURN_FLOAT8(gbt_num_distance(&key, qp, GIST_LEAF(entry),
+ ti, fcinfo->flinfo));
}
Datum
gbt_int2_union(PG_FUNCTION_ARGS)
{
GistEntryVector *entryvec = (GistEntryVector *) PG_GETARG_POINTER(0);
diff --git a/contrib/btree_gist/btree_int4.c b/contrib/btree_gist/btree_int4.c
index 47790578e6b..325ee78f2ab 100644
--- a/contrib/btree_gist/btree_int4.c
+++ b/contrib/btree_gist/btree_int4.c
@@ -1,12 +1,13 @@
/*
* contrib/btree_gist/btree_int4.c
*/
#include "postgres.h"
#include "btree_gist.h"
#include "btree_utils_num.h"
+#include "catalog/pg_type.h"
#include "common/int.h"
#include "utils/rel.h"
#include "utils/sortsupport.h"
typedef struct int32key
{
@@ -71,13 +72,12 @@ gbt_int4key_cmp(const void *a, const void *b, FmgrInfo *flinfo)
static float8
gbt_int4_dist(const void *a, const void *b, FmgrInfo *flinfo)
{
return GET_FLOAT_DISTANCE(int32, a, b);
}
-
static const gbtree_ninfo tinfo =
{
gbt_t_int4,
sizeof(int32),
8, /* sizeof(gbtreekey8) */
gbt_int4gt,
@@ -86,12 +86,80 @@ static const gbtree_ninfo tinfo =
gbt_int4le,
gbt_int4lt,
gbt_int4key_cmp,
gbt_int4_dist
};
+/*
+ * Cross-type GiST callbacks: the indexed key is int4, the query is int2 or
+ * int8. Both reuse gbt_num_consistent()/gbt_num_distance() via a tinfo whose
+ * comparison/distance callbacks read the query (left) and key (right) sides at
+ * their own widths. f_cmp is unused on these paths and left NULL.
+ */
+GBT_INT_CMP_FNS(gbt_int4_q2_, int16, int32)
+GBT_INT_CMP_FNS(gbt_int4_q8_, int64, int32)
+
+static const gbtree_ninfo tinfo_q2 =
+{
+ gbt_t_int4,
+ sizeof(int32),
+ 8,
+ gbt_int4_q2_gt,
+ gbt_int4_q2_ge,
+ gbt_int4_q2_eq,
+ gbt_int4_q2_le,
+ gbt_int4_q2_lt,
+ NULL,
+ gbt_int4_q2_dist
+};
+
+static const gbtree_ninfo tinfo_q8 =
+{
+ gbt_t_int4,
+ sizeof(int32),
+ 8,
+ gbt_int4_q8_gt,
+ gbt_int4_q8_ge,
+ gbt_int4_q8_eq,
+ gbt_int4_q8_le,
+ gbt_int4_q8_lt,
+ NULL,
+ gbt_int4_q8_dist
+};
+
+/*
+ * Cross-type dispatch shared by gbt_int4_consistent and gbt_int4_distance:
+ * select the tinfo for the query subtype and read the query value at its own
+ * width into caller-owned storage.
+ */
+static const gbtree_ninfo *
+gbt_int4_crosstype(Oid subtype, Datum d, gbt_intkey *q, const void **qp)
+{
+ switch (subtype)
+ {
+ case INT2OID:
+ q->i2 = DatumGetInt16(d);
+ *qp = &q->i2;
+ return &tinfo_q2;
+ case InvalidOid: /* same-type: exclusion/temporal constraint
+ * checks pass the native type with subtype 0 */
+ case INT4OID:
+ q->i4 = DatumGetInt32(d);
+ *qp = &q->i4;
+ return &tinfo;
+ case INT8OID:
+ q->i8 = DatumGetInt64(d);
+ *qp = &q->i8;
+ return &tinfo_q8;
+ default:
+ elog(ERROR, "unrecognized subtype %u for btree_gist int4 cross-type comparison",
+ subtype);
+ return NULL; /* keep compiler quiet */
+ }
+}
+
PG_FUNCTION_INFO_V1(int4_dist);
Datum
int4_dist(PG_FUNCTION_ARGS)
{
int32 a = PG_GETARG_INT32(0);
@@ -107,12 +175,46 @@ int4_dist(PG_FUNCTION_ARGS)
ra = abs(r);
PG_RETURN_INT32(ra);
}
+PG_FUNCTION_INFO_V1(int4_int2_dist);
+Datum
+int4_int2_dist(PG_FUNCTION_ARGS)
+{
+ int32 a = PG_GETARG_INT32(0);
+ int32 b = (int32) PG_GETARG_INT16(1);
+ int32 r;
+
+ if (pg_sub_s32_overflow(a, b, &r) ||
+ r == PG_INT32_MIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("integer out of range")));
+
+ PG_RETURN_INT32(abs(r));
+}
+
+PG_FUNCTION_INFO_V1(int4_int8_dist);
+Datum
+int4_int8_dist(PG_FUNCTION_ARGS)
+{
+ int64 a = (int64) PG_GETARG_INT32(0);
+ int64 b = PG_GETARG_INT64(1);
+ int64 r;
+
+ if (pg_sub_s64_overflow(a, b, &r) ||
+ r == PG_INT64_MIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("bigint out of range")));
+
+ PG_RETURN_INT64(i64abs(r));
+}
+
/**************************************************
* GiST support functions
**************************************************/
Datum
@@ -132,47 +234,53 @@ gbt_int4_fetch(PG_FUNCTION_ARGS)
}
Datum
gbt_int4_consistent(PG_FUNCTION_ARGS)
{
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
- int32 query = PG_GETARG_INT32(1);
+ Datum queryDatum = PG_GETARG_DATUM(1);
StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
-#ifdef NOT_USED
Oid subtype = PG_GETARG_OID(3);
-#endif
bool *recheck = (bool *) PG_GETARG_POINTER(4);
int32KEY *kkk = (int32KEY *) DatumGetPointer(entry->key);
+ const gbtree_ninfo *ti;
+ gbt_intkey query;
+ const void *qp;
GBT_NUMKEY_R key;
/* All cases served by this function are exact */
*recheck = false;
key.lower = (GBT_NUMKEY *) &kkk->lower;
key.upper = (GBT_NUMKEY *) &kkk->upper;
- PG_RETURN_BOOL(gbt_num_consistent(&key, &query, &strategy,
- GIST_LEAF(entry), &tinfo, fcinfo->flinfo));
+ ti = gbt_int4_crosstype(subtype, queryDatum, &query, &qp);
+
+ PG_RETURN_BOOL(gbt_num_consistent(&key, qp, &strategy, GIST_LEAF(entry),
+ ti, fcinfo->flinfo));
}
Datum
gbt_int4_distance(PG_FUNCTION_ARGS)
{
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
- int32 query = PG_GETARG_INT32(1);
-#ifdef NOT_USED
+ Datum queryDatum = PG_GETARG_DATUM(1);
Oid subtype = PG_GETARG_OID(3);
-#endif
int32KEY *kkk = (int32KEY *) DatumGetPointer(entry->key);
+ const gbtree_ninfo *ti;
+ gbt_intkey query;
+ const void *qp;
GBT_NUMKEY_R key;
key.lower = (GBT_NUMKEY *) &kkk->lower;
key.upper = (GBT_NUMKEY *) &kkk->upper;
- PG_RETURN_FLOAT8(gbt_num_distance(&key, &query, GIST_LEAF(entry),
- &tinfo, fcinfo->flinfo));
+ ti = gbt_int4_crosstype(subtype, queryDatum, &query, &qp);
+
+ PG_RETURN_FLOAT8(gbt_num_distance(&key, qp, GIST_LEAF(entry),
+ ti, fcinfo->flinfo));
}
Datum
gbt_int4_union(PG_FUNCTION_ARGS)
{
GistEntryVector *entryvec = (GistEntryVector *) PG_GETARG_POINTER(0);
diff --git a/contrib/btree_gist/btree_int8.c b/contrib/btree_gist/btree_int8.c
index f48122c8d84..62c06345e87 100644
--- a/contrib/btree_gist/btree_int8.c
+++ b/contrib/btree_gist/btree_int8.c
@@ -2,12 +2,13 @@
* contrib/btree_gist/btree_int8.c
*/
#include "postgres.h"
#include "btree_gist.h"
#include "btree_utils_num.h"
+#include "catalog/pg_type.h"
#include "common/int.h"
#include "utils/rel.h"
#include "utils/sortsupport.h"
typedef struct int64key
{
@@ -73,13 +74,12 @@ gbt_int8key_cmp(const void *a, const void *b, FmgrInfo *flinfo)
static float8
gbt_int8_dist(const void *a, const void *b, FmgrInfo *flinfo)
{
return GET_FLOAT_DISTANCE(int64, a, b);
}
-
static const gbtree_ninfo tinfo =
{
gbt_t_int8,
sizeof(int64),
16, /* sizeof(gbtreekey16) */
gbt_int8gt,
@@ -88,12 +88,80 @@ static const gbtree_ninfo tinfo =
gbt_int8le,
gbt_int8lt,
gbt_int8key_cmp,
gbt_int8_dist
};
+/*
+ * Cross-type GiST callbacks: the indexed key is int8, the query is int2 or
+ * int4. Both reuse gbt_num_consistent()/gbt_num_distance() via a tinfo whose
+ * comparison/distance callbacks read the query (left) and key (right) sides at
+ * their own widths. f_cmp is unused on these paths and left NULL.
+ */
+GBT_INT_CMP_FNS(gbt_int8_q2_, int16, int64)
+GBT_INT_CMP_FNS(gbt_int8_q4_, int32, int64)
+
+static const gbtree_ninfo tinfo_q2 =
+{
+ gbt_t_int8,
+ sizeof(int64),
+ 16,
+ gbt_int8_q2_gt,
+ gbt_int8_q2_ge,
+ gbt_int8_q2_eq,
+ gbt_int8_q2_le,
+ gbt_int8_q2_lt,
+ NULL,
+ gbt_int8_q2_dist
+};
+
+static const gbtree_ninfo tinfo_q4 =
+{
+ gbt_t_int8,
+ sizeof(int64),
+ 16,
+ gbt_int8_q4_gt,
+ gbt_int8_q4_ge,
+ gbt_int8_q4_eq,
+ gbt_int8_q4_le,
+ gbt_int8_q4_lt,
+ NULL,
+ gbt_int8_q4_dist
+};
+
+/*
+ * Cross-type dispatch shared by gbt_int8_consistent and gbt_int8_distance:
+ * select the tinfo for the query subtype and read the query value at its own
+ * width into caller-owned storage.
+ */
+static const gbtree_ninfo *
+gbt_int8_crosstype(Oid subtype, Datum d, gbt_intkey *q, const void **qp)
+{
+ switch (subtype)
+ {
+ case INT2OID:
+ q->i2 = DatumGetInt16(d);
+ *qp = &q->i2;
+ return &tinfo_q2;
+ case INT4OID:
+ q->i4 = DatumGetInt32(d);
+ *qp = &q->i4;
+ return &tinfo_q4;
+ case InvalidOid: /* same-type: exclusion/temporal constraint
+ * checks pass the native type with subtype 0 */
+ case INT8OID:
+ q->i8 = DatumGetInt64(d);
+ *qp = &q->i8;
+ return &tinfo;
+ default:
+ elog(ERROR, "unrecognized subtype %u for btree_gist int8 cross-type comparison",
+ subtype);
+ return NULL; /* keep compiler quiet */
+ }
+}
+
PG_FUNCTION_INFO_V1(int8_dist);
Datum
int8_dist(PG_FUNCTION_ARGS)
{
int64 a = PG_GETARG_INT64(0);
@@ -109,12 +177,46 @@ int8_dist(PG_FUNCTION_ARGS)
ra = i64abs(r);
PG_RETURN_INT64(ra);
}
+PG_FUNCTION_INFO_V1(int8_int2_dist);
+Datum
+int8_int2_dist(PG_FUNCTION_ARGS)
+{
+ int64 a = PG_GETARG_INT64(0);
+ int64 b = (int64) PG_GETARG_INT16(1);
+ int64 r;
+
+ if (pg_sub_s64_overflow(a, b, &r) ||
+ r == PG_INT64_MIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("bigint out of range")));
+
+ PG_RETURN_INT64(i64abs(r));
+}
+
+PG_FUNCTION_INFO_V1(int8_int4_dist);
+Datum
+int8_int4_dist(PG_FUNCTION_ARGS)
+{
+ int64 a = PG_GETARG_INT64(0);
+ int64 b = (int64) PG_GETARG_INT32(1);
+ int64 r;
+
+ if (pg_sub_s64_overflow(a, b, &r) ||
+ r == PG_INT64_MIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("bigint out of range")));
+
+ PG_RETURN_INT64(i64abs(r));
+}
+
/**************************************************
* GiST support functions
**************************************************/
Datum
@@ -134,47 +236,53 @@ gbt_int8_fetch(PG_FUNCTION_ARGS)
}
Datum
gbt_int8_consistent(PG_FUNCTION_ARGS)
{
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
- int64 query = PG_GETARG_INT64(1);
+ Datum queryDatum = PG_GETARG_DATUM(1);
StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
-#ifdef NOT_USED
Oid subtype = PG_GETARG_OID(3);
-#endif
bool *recheck = (bool *) PG_GETARG_POINTER(4);
int64KEY *kkk = (int64KEY *) DatumGetPointer(entry->key);
+ const gbtree_ninfo *ti;
+ gbt_intkey query;
+ const void *qp;
GBT_NUMKEY_R key;
/* All cases served by this function are exact */
*recheck = false;
key.lower = (GBT_NUMKEY *) &kkk->lower;
key.upper = (GBT_NUMKEY *) &kkk->upper;
- PG_RETURN_BOOL(gbt_num_consistent(&key, &query, &strategy,
- GIST_LEAF(entry), &tinfo, fcinfo->flinfo));
+ ti = gbt_int8_crosstype(subtype, queryDatum, &query, &qp);
+
+ PG_RETURN_BOOL(gbt_num_consistent(&key, qp, &strategy, GIST_LEAF(entry),
+ ti, fcinfo->flinfo));
}
Datum
gbt_int8_distance(PG_FUNCTION_ARGS)
{
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
- int64 query = PG_GETARG_INT64(1);
-#ifdef NOT_USED
+ Datum queryDatum = PG_GETARG_DATUM(1);
Oid subtype = PG_GETARG_OID(3);
-#endif
int64KEY *kkk = (int64KEY *) DatumGetPointer(entry->key);
+ const gbtree_ninfo *ti;
+ gbt_intkey query;
+ const void *qp;
GBT_NUMKEY_R key;
key.lower = (GBT_NUMKEY *) &kkk->lower;
key.upper = (GBT_NUMKEY *) &kkk->upper;
- PG_RETURN_FLOAT8(gbt_num_distance(&key, &query, GIST_LEAF(entry),
- &tinfo, fcinfo->flinfo));
+ ti = gbt_int8_crosstype(subtype, queryDatum, &query, &qp);
+
+ PG_RETURN_FLOAT8(gbt_num_distance(&key, qp, GIST_LEAF(entry),
+ ti, fcinfo->flinfo));
}
Datum
gbt_int8_union(PG_FUNCTION_ARGS)
{
GistEntryVector *entryvec = (GistEntryVector *) PG_GETARG_POINTER(0);
diff --git a/contrib/btree_gist/btree_utils_num.c b/contrib/btree_gist/btree_utils_num.c
index 3affe4c2c46..0d6d578062a 100644
--- a/contrib/btree_gist/btree_utils_num.c
+++ b/contrib/btree_gist/btree_utils_num.c
@@ -266,12 +266,18 @@ gbt_num_consistent(const GBT_NUMKEY_R *key,
bool is_leaf,
const gbtree_ninfo *tinfo,
FmgrInfo *flinfo)
{
bool retval;
+ /*
+ * Every comparison callback is invoked as f_xx(query, key): the query
+ * value is always the left argument and the indexed key bound the right.
+ * The integer opclasses rely on this fixed order so their cross-type
+ * callbacks can read each side at its own width.
+ */
switch (*strategy)
{
case BTLessEqualStrategyNumber:
retval = tinfo->f_ge(query, key->lower, flinfo);
break;
case BTLessStrategyNumber:
@@ -281,13 +287,13 @@ gbt_num_consistent(const GBT_NUMKEY_R *key,
retval = tinfo->f_ge(query, key->lower, flinfo);
break;
case BTEqualStrategyNumber:
if (is_leaf)
retval = tinfo->f_eq(query, key->lower, flinfo);
else
- retval = (tinfo->f_le(key->lower, query, flinfo) &&
+ retval = (tinfo->f_ge(query, key->lower, flinfo) &&
tinfo->f_le(query, key->upper, flinfo));
break;
case BTGreaterStrategyNumber:
if (is_leaf)
retval = tinfo->f_lt(query, key->upper, flinfo);
else
@@ -304,13 +310,12 @@ gbt_num_consistent(const GBT_NUMKEY_R *key,
retval = false;
}
return retval;
}
-
/*
* The GiST distance method (for KNN-Gist)
*/
float8
gbt_num_distance(const GBT_NUMKEY_R *key,
@@ -321,12 +326,19 @@ gbt_num_distance(const GBT_NUMKEY_R *key,
{
float8 retval;
if (tinfo->f_dist == NULL)
elog(ERROR, "KNN search is not supported for btree_gist type %d",
(int) tinfo->t);
+
+ /*
+ * As in gbt_num_consistent(), every callback is invoked as f_xx(query, key):
+ * the query value is the left argument and the indexed key bound the right.
+ * The integer opclasses' cross-type callbacks read each side at its own
+ * width, so keep this argument order if you ever touch the calls below.
+ */
if (tinfo->f_le(query, key->lower, flinfo))
retval = tinfo->f_dist(query, key->lower, flinfo);
else if (tinfo->f_ge(query, key->upper, flinfo))
retval = tinfo->f_dist(query, key->upper, flinfo);
else
retval = 0.0;
diff --git a/contrib/btree_gist/btree_utils_num.h b/contrib/btree_gist/btree_utils_num.h
index 53e477d8b1e..217b362c169 100644
--- a/contrib/btree_gist/btree_utils_num.h
+++ b/contrib/btree_gist/btree_utils_num.h
@@ -24,12 +24,24 @@ typedef struct
typedef struct
{
int i;
GBT_NUMKEY *t;
} Nsrt;
+/*
+ * Query-value storage for the integer opclasses' cross-type path. The caller
+ * owns one of these and passes its address to the per-type cross-type helper,
+ * which fills the right width and points the query pointer at it.
+ */
+typedef union
+{
+ int16 i2;
+ int32 i4;
+ int64 i8;
+} gbt_intkey;
+
/* type description */
typedef struct
{
@@ -83,13 +95,38 @@ typedef struct
*/
#define INTERVAL_TO_SEC(ivp) \
(((double) (ivp)->time) / ((double) USECS_PER_SEC) + \
(ivp)->day * (24.0 * SECS_PER_HOUR) + \
(ivp)->month * (30.0 * SECS_PER_DAY))
-#define GET_FLOAT_DISTANCE(t, arg1, arg2) fabs( ((float8) *((const t *) (arg1))) - ((float8) *((const t *) (arg2))) )
+#define GET_FLOAT_DISTANCE2(t1, t2, arg1, arg2) fabs( ((float8) *((const t1 *) (arg1))) - ((float8) *((const t2 *) (arg2))) )
+#define GET_FLOAT_DISTANCE(t, arg1, arg2) GET_FLOAT_DISTANCE2(t, t, (arg1), (arg2))
+
+/*
+ * Generate the comparison/distance callbacks for a gbtree_ninfo whose query
+ * and key sides may be different (integer) types. gbt_num_consistent() and
+ * gbt_num_distance() always invoke the callbacks as f_xx(query, key), so the
+ * first argument has the query type QT (the operator's right-hand subtype) and
+ * the second has the indexed key type KT. Integer widening is value-preserving,
+ * so the comparisons need no explicit cast; the distance widens to float8 to
+ * avoid overflow in the subtraction. Invoked with QT == KT this also generates
+ * the ordinary same-type callbacks.
+ */
+#define GBT_INT_CMP_FNS(prefix, QT, KT) \
+static bool prefix##gt(const void *a, const void *b, FmgrInfo *flinfo) \
+{ return *((const QT *) a) > *((const KT *) b); } \
+static bool prefix##ge(const void *a, const void *b, FmgrInfo *flinfo) \
+{ return *((const QT *) a) >= *((const KT *) b); } \
+static bool prefix##eq(const void *a, const void *b, FmgrInfo *flinfo) \
+{ return *((const QT *) a) == *((const KT *) b); } \
+static bool prefix##le(const void *a, const void *b, FmgrInfo *flinfo) \
+{ return *((const QT *) a) <= *((const KT *) b); } \
+static bool prefix##lt(const void *a, const void *b, FmgrInfo *flinfo) \
+{ return *((const QT *) a) < *((const KT *) b); } \
+static float8 prefix##dist(const void *a, const void *b, FmgrInfo *flinfo) \
+{ return GET_FLOAT_DISTANCE2(QT, KT, a, b); }
extern Interval *abs_interval(Interval *a);
extern bool gbt_num_consistent(const GBT_NUMKEY_R *key, const void *query,
const StrategyNumber *strategy, bool is_leaf,
diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build
index 2b1a5463289..43ba130e8e4 100644
--- a/contrib/btree_gist/meson.build
+++ b/contrib/btree_gist/meson.build
@@ -49,12 +49,13 @@ install_data(
'btree_gist--1.4--1.5.sql',
'btree_gist--1.5--1.6.sql',
'btree_gist--1.6--1.7.sql',
'btree_gist--1.7--1.8.sql',
'btree_gist--1.8--1.9.sql',
'btree_gist--1.9.sql',
+ 'btree_gist--1.9--1.10.sql',
kwargs: contrib_data_args,
)
tests += {
'name': 'btree_gist',
'sd': meson.current_source_dir(),
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c5db6ca6705..e29f76b6feb 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3808,12 +3808,13 @@ floating_decimal_32
floating_decimal_64
fmgr_hook_type
foreign_glob_cxt
foreign_loc_cxt
freefunc
fsec_t
+gbt_intkey
gbt_vsrt_arg
gbtree_ninfo
gbtree_vinfo
generate_series_fctx
generate_series_numeric_fctx
generate_series_timestamp_fctx
--
2.51.0
[application/octet-stream] v5-0002-Add-tests-for-cross-type-operators-for-GiST-indexes.patch (25.8K, 4-v5-0002-Add-tests-for-cross-type-operators-for-GiST-indexes.patch)
download | inline diff:
From bdce40e8a75eea4f1df778376042dbcf8246d3a4 Mon Sep 17 00:00:00 2001
From: Alexander Nestorov <[email protected]>
Date: Thu, 4 Jun 2026 00:08:27 +0200
Subject: [PATCH] Add tests for cross-type operators for GiST indexes
Add a new int_crosstype regression test that drives the cross-type
comparison and <-> distance operators for every int2/int4/int8
combination through the index (with seqscan and bitmapscan disabled),
plus a multi-column index, query constants outside the indexed column's
range, and the standalone distance functions including their
overflow-detection paths.
Include a width-sensitivity guard: the constants are chosen so their low
16 bits collide with a value present in the data, so that reading either
side at the wrong width (a dispatch entry returning the wrong-width
callback, a truncated key, or the query/key arguments swapped in
gbt_num_consistent()/gbt_num_distance()) changes the result and fails
the test.
Also assert that the operator families contain no cross-type support
functions, matching the pg_amop-only design.
Co-authored-by: Maxime Schoemans <[email protected]>
---
contrib/btree_gist/Makefile | 2 +-
contrib/btree_gist/expected/int_crosstype.out | 577 ++++++++++++++++++
contrib/btree_gist/meson.build | 1 +
contrib/btree_gist/sql/int_crosstype.sql | 230 +++++++
4 files changed, 809 insertions(+), 1 deletion(-)
create mode 100644 contrib/btree_gist/expected/int_crosstype.out
create mode 100644 contrib/btree_gist/sql/int_crosstype.sql
diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile
index 380c7642fde..1d0668d97ab 100644
--- a/contrib/btree_gist/Makefile
+++ b/contrib/btree_gist/Makefile
@@ -38,13 +38,13 @@ DATA = btree_gist--1.0--1.1.sql \
btree_gist--1.9.sql btree_gist--1.9--1.10.sql
PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes"
REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \
time timetz date interval macaddr macaddr8 inet cidr text varchar char \
bytea bit varbit numeric uuid not_equal enum bool partitions \
- stratnum without_overlaps
+ stratnum int_crosstype without_overlaps
SHLIB_LINK += $(filter -lm, $(LIBS))
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/btree_gist/expected/int_crosstype.out b/contrib/btree_gist/expected/int_crosstype.out
new file mode 100644
index 00000000000..b891abdf25e
--- /dev/null
+++ b/contrib/btree_gist/expected/int_crosstype.out
@@ -0,0 +1,577 @@
+-- Cross-type operator support for int2/int4/int8 in GiST.
+--
+-- Verifies that (a) the cross-type B-tree-style operators registered in
+-- gist_int{2,4,8}_ops match the results of seqscans using the same operator
+-- expressions, (b) the KNN <-> operator works across types and uses the
+-- index, and (c) values outside the smaller subtype's range are handled
+-- according to normal comparison semantics, without narrowing or erroring.
+--
+-- The integer cross-type support is handled inside the existing
+-- consistent/distance support functions (which dispatch on the subtype OID),
+-- so the operator families must not contain any cross-type (different
+-- left/right input type) support-function entries.
+--
+SELECT opf.opfname AS opfamily,
+ amproc.amproclefttype::regtype AS lefttype,
+ amproc.amprocrighttype::regtype AS righttype,
+ amproc.amprocnum AS procnum,
+ amproc.amproc::regproc AS proc
+FROM pg_amproc amproc
+ JOIN pg_opfamily opf ON opf.oid = amproc.amprocfamily
+ JOIN pg_am am ON am.oid = opf.opfmethod
+WHERE am.amname = 'gist'
+ AND opf.opfname IN ('gist_int2_ops', 'gist_int4_ops', 'gist_int8_ops')
+ AND amproc.amproclefttype <> amproc.amprocrighttype
+ORDER BY opf.opfname,
+ amproc.amproclefttype::regtype::text,
+ amproc.amprocrighttype::regtype::text,
+ amproc.amprocnum,
+ amproc.amproc::regproc::text;
+ opfamily | lefttype | righttype | procnum | proc
+----------+----------+-----------+---------+------
+(0 rows)
+
+CREATE TABLE ct_i2 (a int2);
+CREATE TABLE ct_i4 (a int4);
+CREATE TABLE ct_i8 (a int8);
+INSERT INTO ct_i2 SELECT g::int2 FROM generate_series(-100, 100) g;
+INSERT INTO ct_i4 SELECT g FROM generate_series(-100, 100) g;
+INSERT INTO ct_i8 SELECT g::int8 FROM generate_series(-100, 100) g;
+-- Add some values that are representable only in wider types, to exercise
+-- the path where the cross-type query constant is out of range of the key
+-- type.
+INSERT INTO ct_i4 VALUES (100000), (-100000);
+INSERT INTO ct_i8 VALUES (5000000000), (-5000000000);
+CREATE INDEX ct_i2_idx ON ct_i2 USING gist (a);
+CREATE INDEX ct_i4_idx ON ct_i4 USING gist (a);
+CREATE INDEX ct_i8_idx ON ct_i8 USING gist (a);
+ANALYZE ct_i2;
+ANALYZE ct_i4;
+ANALYZE ct_i8;
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+-- int2 key x int4 query
+SELECT count(*) FROM ct_i2 WHERE a < 50::int4;
+ count
+-------
+ 150
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a <= 50::int4;
+ count
+-------
+ 151
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a = 50::int4;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a >= 50::int4;
+ count
+-------
+ 51
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a > 50::int4;
+ count
+-------
+ 50
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a <> 50::int4;
+ count
+-------
+ 200
+(1 row)
+
+-- query out of int2 range: matches nothing for =, everything for <>
+SELECT count(*) FROM ct_i2 WHERE a = 100000::int4;
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a <> 100000::int4;
+ count
+-------
+ 201
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a < 100000::int4;
+ count
+-------
+ 201
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a > 100000::int4;
+ count
+-------
+ 0
+(1 row)
+
+-- int2 key x int8 query
+SELECT count(*) FROM ct_i2 WHERE a < 50::int8;
+ count
+-------
+ 150
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a <= 50::int8;
+ count
+-------
+ 151
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a = 50::int8;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a >= 50::int8;
+ count
+-------
+ 51
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a > 50::int8;
+ count
+-------
+ 50
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a <> 50::int8;
+ count
+-------
+ 200
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a = 5000000000::int8;
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM ct_i2 WHERE a < 5000000000::int8;
+ count
+-------
+ 201
+(1 row)
+
+-- int4 key x int2 query
+SELECT count(*) FROM ct_i4 WHERE a < 50::int2;
+ count
+-------
+ 151
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a <= 50::int2;
+ count
+-------
+ 152
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a = 50::int2;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a >= 50::int2;
+ count
+-------
+ 52
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a > 50::int2;
+ count
+-------
+ 51
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a <> 50::int2;
+ count
+-------
+ 202
+(1 row)
+
+-- int4 key x int8 query
+SELECT count(*) FROM ct_i4 WHERE a < 50::int8;
+ count
+-------
+ 151
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a <= 50::int8;
+ count
+-------
+ 152
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a = 50::int8;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a >= 50::int8;
+ count
+-------
+ 52
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a > 50::int8;
+ count
+-------
+ 51
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a <> 50::int8;
+ count
+-------
+ 202
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a = 5000000000::int8;
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM ct_i4 WHERE a < 5000000000::int8;
+ count
+-------
+ 203
+(1 row)
+
+-- int8 key x int2 query
+SELECT count(*) FROM ct_i8 WHERE a < 50::int2;
+ count
+-------
+ 151
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a <= 50::int2;
+ count
+-------
+ 152
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a = 50::int2;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a >= 50::int2;
+ count
+-------
+ 52
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a > 50::int2;
+ count
+-------
+ 51
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a <> 50::int2;
+ count
+-------
+ 202
+(1 row)
+
+-- int8 key x int4 query
+SELECT count(*) FROM ct_i8 WHERE a < 50::int4;
+ count
+-------
+ 151
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a <= 50::int4;
+ count
+-------
+ 152
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a = 50::int4;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a >= 50::int4;
+ count
+-------
+ 52
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a > 50::int4;
+ count
+-------
+ 51
+(1 row)
+
+SELECT count(*) FROM ct_i8 WHERE a <> 50::int4;
+ count
+-------
+ 202
+(1 row)
+
+-- Confirm the index is actually used for a cross-type predicate.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM ct_i4 WHERE a = 50::int8;
+ QUERY PLAN
+------------------------------------------------
+ Aggregate
+ -> Index Only Scan using ct_i4_idx on ct_i4
+ Index Cond: (a = '50'::bigint)
+(3 rows)
+
+-- Cross-type KNN: int4 key ordered by int2 / int8 queries.
+EXPLAIN (COSTS OFF)
+SELECT a FROM ct_i4 ORDER BY a <-> '-100'::int2 LIMIT 3;
+ QUERY PLAN
+------------------------------------------------
+ Limit
+ -> Index Only Scan using ct_i4_idx on ct_i4
+ Order By: (a <-> '-100'::smallint)
+(3 rows)
+
+SELECT a FROM ct_i4 ORDER BY a <-> '-100'::int2 LIMIT 3;
+ a
+------
+ -100
+ -99
+ -98
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT a FROM ct_i4 ORDER BY a <-> '-100'::int8 LIMIT 3;
+ QUERY PLAN
+------------------------------------------------
+ Limit
+ -> Index Only Scan using ct_i4_idx on ct_i4
+ Order By: (a <-> '-100'::bigint)
+(3 rows)
+
+SELECT a FROM ct_i4 ORDER BY a <-> '-100'::int8 LIMIT 3;
+ a
+------
+ -100
+ -99
+ -98
+(3 rows)
+
+-- Cross-type KNN: int2 key ordered by int4 / int8 queries.
+SELECT a FROM ct_i2 ORDER BY a <-> '-100'::int4 LIMIT 3;
+ a
+------
+ -100
+ -99
+ -98
+(3 rows)
+
+SELECT a FROM ct_i2 ORDER BY a <-> '-100'::int8 LIMIT 3;
+ a
+------
+ -100
+ -99
+ -98
+(3 rows)
+
+-- Cross-type KNN: int8 key ordered by int2 / int4 queries.
+SELECT a FROM ct_i8 ORDER BY a <-> '-100'::int2 LIMIT 3;
+ a
+------
+ -100
+ -99
+ -98
+(3 rows)
+
+SELECT a FROM ct_i8 ORDER BY a <-> '-100'::int4 LIMIT 3;
+ a
+------
+ -100
+ -99
+ -98
+(3 rows)
+
+-- Combined: cross-type WHERE + cross-type ORDER BY on the same index.
+EXPLAIN (COSTS OFF)
+SELECT a FROM ct_i4 WHERE a < 80::int8 ORDER BY a <-> '-100'::int8 LIMIT 3;
+ QUERY PLAN
+------------------------------------------------
+ Limit
+ -> Index Only Scan using ct_i4_idx on ct_i4
+ Index Cond: (a < '80'::bigint)
+ Order By: (a <-> '-100'::bigint)
+(4 rows)
+
+SELECT a FROM ct_i4 WHERE a < 80::int8 ORDER BY a <-> '-100'::int8 LIMIT 3;
+ a
+------
+ -100
+ -99
+ -98
+(3 rows)
+
+-- Standalone distance-function smoke tests (not going through the index),
+-- including the overflow-detection paths.
+SELECT int2_int4_dist(3::int2, 10::int4);
+ int2_int4_dist
+----------------
+ 7
+(1 row)
+
+SELECT int4_int2_dist(-5::int4, 5::int2);
+ int4_int2_dist
+----------------
+ 10
+(1 row)
+
+SELECT int2_int8_dist(3::int2, 10::int8);
+ int2_int8_dist
+----------------
+ 7
+(1 row)
+
+SELECT int8_int2_dist(100::int8, -5::int2);
+ int8_int2_dist
+----------------
+ 105
+(1 row)
+
+SELECT int4_int8_dist(100::int4, 5000000000::int8);
+ int4_int8_dist
+----------------
+ 4999999900
+(1 row)
+
+SELECT int8_int4_dist(5000000000::int8, 100::int4);
+ int8_int4_dist
+----------------
+ 4999999900
+(1 row)
+
+-- Overflow detection: INT32_MIN distance from a positive int2 can't fit
+-- in int32, should error.
+SELECT int2_int4_dist(1::int2, -2147483648::int4);
+ERROR: integer out of range
+-- Likewise INT64_MIN distance from a positive int4 can't fit in int64.
+SELECT int4_int8_dist(1::int4, -9223372036854775808::int8);
+ERROR: bigint out of range
+--
+-- Multi-column GiST index with mixed-type predicates. This is the
+-- original motivating case: without cross-type operator support the
+-- planner can only use one column as an Index Cond and applies the
+-- other(s) as a Filter post-scan. Here both columns should appear as
+-- Index Cond.
+--
+CREATE TABLE ct_multi (a int4, b int8);
+INSERT INTO ct_multi
+ SELECT g, (g * 2)::int8 FROM generate_series(-50, 50) g;
+CREATE INDEX ct_multi_idx ON ct_multi USING gist (a, b);
+ANALYZE ct_multi;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM ct_multi WHERE a = 25::int8 AND b = 50::int4;
+ QUERY PLAN
+-------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using ct_multi_idx on ct_multi
+ Index Cond: ((a = '25'::bigint) AND (b = 50))
+(3 rows)
+
+SELECT count(*) FROM ct_multi WHERE a = 25::int8 AND b = 50::int4;
+ count
+-------
+ 1
+(1 row)
+
+-- Mixed cross-type ranges across both columns.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM ct_multi WHERE a < 10::int8 AND b > 0::int2;
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using ct_multi_idx on ct_multi
+ Index Cond: ((a < '10'::bigint) AND (b > '0'::smallint))
+(3 rows)
+
+SELECT count(*) FROM ct_multi WHERE a < 10::int8 AND b > 0::int2;
+ count
+-------
+ 9
+(1 row)
+
+DROP TABLE ct_multi;
+--
+-- Width-sensitivity guard (int16 vs int32).
+--
+-- The cross-type callbacks must read each side at its OWN width: the query at
+-- the subtype width and the key at the indexed-column width, never narrowing
+-- one to the other. These cases are constructed so that reading either side at
+-- the wrong width changes the answer -- a dispatch entry returning the
+-- wrong-width tinfo, an int4 key truncated to int16, or the query/key arguments
+-- being swapped in gbt_num_consistent()/gbt_num_distance() would all flip the
+-- result, because each wide constant shares its low 16 bits with a value present
+-- in the data. enable_seqscan/bitmapscan are already off, so the GiST
+-- consistent/distance path is exercised.
+--
+CREATE TABLE ct_w4 (k int4);
+INSERT INTO ct_w4 VALUES (1), (65538); -- 65538 = 0x10002, low 16 bits = 2
+CREATE INDEX ct_w4_idx ON ct_w4 USING gist (k);
+ANALYZE ct_w4;
+-- int4 key vs int2 query: 65538 must compare as 65538, not as 2.
+-- correct: {65538}; an int4 key truncated to int16 (65538 -> 2) would give {}.
+SELECT k FROM ct_w4 WHERE k > 1000::int2 ORDER BY k;
+ k
+-------
+ 65538
+(1 row)
+
+-- correct: {} (no key equals 2); a truncated key (65538 -> 2) would match.
+SELECT k FROM ct_w4 WHERE k = 2::int2 ORDER BY k;
+ k
+---
+(0 rows)
+
+-- KNN: nearest to 2 is 1 (distance 1). enable_sort=off steers the planner to
+-- the GiST KNN distance path rather than a Sort on the operator. A truncated
+-- key (65538 -> 2, distance 0) would wrongly sort 65538 first. (We assert the
+-- result, not the plan shape, to stay portable across visibility-map state.)
+SET enable_sort = off;
+SELECT k FROM ct_w4 ORDER BY k <-> 2::int2 LIMIT 1;
+ k
+---
+ 1
+(1 row)
+
+RESET enable_sort;
+CREATE TABLE ct_w2 (k int2);
+INSERT INTO ct_w2 VALUES (1), (50);
+CREATE INDEX ct_w2_idx ON ct_w2 USING gist (k);
+ANALYZE ct_w2;
+-- int2 key vs int4 query: 65537 must compare as 65537, not as 1 (0x10001).
+-- correct: 0; an int4 query truncated to int16 (65537 -> 1) would match k=1.
+SELECT count(*) FROM ct_w2 WHERE k = 65537::int4;
+ count
+-------
+ 0
+(1 row)
+
+-- correct: 0; a truncated query (65537 -> 1) would match k=50.
+SELECT count(*) FROM ct_w2 WHERE k > 65537::int4;
+ count
+-------
+ 0
+(1 row)
+
+DROP TABLE ct_w4;
+DROP TABLE ct_w2;
+DROP TABLE ct_i2;
+DROP TABLE ct_i4;
+DROP TABLE ct_i8;
diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build
index 43ba130e8e4..c640cb7d8d2 100644
--- a/contrib/btree_gist/meson.build
+++ b/contrib/btree_gist/meson.build
@@ -90,10 +90,11 @@ tests += {
'uuid',
'not_equal',
'enum',
'bool',
'partitions',
'stratnum',
+ 'int_crosstype',
'without_overlaps',
],
},
}
diff --git a/contrib/btree_gist/sql/int_crosstype.sql b/contrib/btree_gist/sql/int_crosstype.sql
new file mode 100644
index 00000000000..5a388a6b99b
--- /dev/null
+++ b/contrib/btree_gist/sql/int_crosstype.sql
@@ -0,0 +1,230 @@
+-- Cross-type operator support for int2/int4/int8 in GiST.
+--
+-- Verifies that (a) the cross-type B-tree-style operators registered in
+-- gist_int{2,4,8}_ops match the results of seqscans using the same operator
+-- expressions, (b) the KNN <-> operator works across types and uses the
+-- index, and (c) values outside the smaller subtype's range are handled
+-- according to normal comparison semantics, without narrowing or erroring.
+
+--
+-- The integer cross-type support is handled inside the existing
+-- consistent/distance support functions (which dispatch on the subtype OID),
+-- so the operator families must not contain any cross-type (different
+-- left/right input type) support-function entries.
+--
+SELECT opf.opfname AS opfamily,
+ amproc.amproclefttype::regtype AS lefttype,
+ amproc.amprocrighttype::regtype AS righttype,
+ amproc.amprocnum AS procnum,
+ amproc.amproc::regproc AS proc
+FROM pg_amproc amproc
+ JOIN pg_opfamily opf ON opf.oid = amproc.amprocfamily
+ JOIN pg_am am ON am.oid = opf.opfmethod
+WHERE am.amname = 'gist'
+ AND opf.opfname IN ('gist_int2_ops', 'gist_int4_ops', 'gist_int8_ops')
+ AND amproc.amproclefttype <> amproc.amprocrighttype
+ORDER BY opf.opfname,
+ amproc.amproclefttype::regtype::text,
+ amproc.amprocrighttype::regtype::text,
+ amproc.amprocnum,
+ amproc.amproc::regproc::text;
+
+CREATE TABLE ct_i2 (a int2);
+CREATE TABLE ct_i4 (a int4);
+CREATE TABLE ct_i8 (a int8);
+
+INSERT INTO ct_i2 SELECT g::int2 FROM generate_series(-100, 100) g;
+INSERT INTO ct_i4 SELECT g FROM generate_series(-100, 100) g;
+INSERT INTO ct_i8 SELECT g::int8 FROM generate_series(-100, 100) g;
+
+-- Add some values that are representable only in wider types, to exercise
+-- the path where the cross-type query constant is out of range of the key
+-- type.
+INSERT INTO ct_i4 VALUES (100000), (-100000);
+INSERT INTO ct_i8 VALUES (5000000000), (-5000000000);
+
+CREATE INDEX ct_i2_idx ON ct_i2 USING gist (a);
+CREATE INDEX ct_i4_idx ON ct_i4 USING gist (a);
+CREATE INDEX ct_i8_idx ON ct_i8 USING gist (a);
+
+ANALYZE ct_i2;
+ANALYZE ct_i4;
+ANALYZE ct_i8;
+
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+
+-- int2 key x int4 query
+SELECT count(*) FROM ct_i2 WHERE a < 50::int4;
+SELECT count(*) FROM ct_i2 WHERE a <= 50::int4;
+SELECT count(*) FROM ct_i2 WHERE a = 50::int4;
+SELECT count(*) FROM ct_i2 WHERE a >= 50::int4;
+SELECT count(*) FROM ct_i2 WHERE a > 50::int4;
+SELECT count(*) FROM ct_i2 WHERE a <> 50::int4;
+
+-- query out of int2 range: matches nothing for =, everything for <>
+SELECT count(*) FROM ct_i2 WHERE a = 100000::int4;
+SELECT count(*) FROM ct_i2 WHERE a <> 100000::int4;
+SELECT count(*) FROM ct_i2 WHERE a < 100000::int4;
+SELECT count(*) FROM ct_i2 WHERE a > 100000::int4;
+
+-- int2 key x int8 query
+SELECT count(*) FROM ct_i2 WHERE a < 50::int8;
+SELECT count(*) FROM ct_i2 WHERE a <= 50::int8;
+SELECT count(*) FROM ct_i2 WHERE a = 50::int8;
+SELECT count(*) FROM ct_i2 WHERE a >= 50::int8;
+SELECT count(*) FROM ct_i2 WHERE a > 50::int8;
+SELECT count(*) FROM ct_i2 WHERE a <> 50::int8;
+SELECT count(*) FROM ct_i2 WHERE a = 5000000000::int8;
+SELECT count(*) FROM ct_i2 WHERE a < 5000000000::int8;
+
+-- int4 key x int2 query
+SELECT count(*) FROM ct_i4 WHERE a < 50::int2;
+SELECT count(*) FROM ct_i4 WHERE a <= 50::int2;
+SELECT count(*) FROM ct_i4 WHERE a = 50::int2;
+SELECT count(*) FROM ct_i4 WHERE a >= 50::int2;
+SELECT count(*) FROM ct_i4 WHERE a > 50::int2;
+SELECT count(*) FROM ct_i4 WHERE a <> 50::int2;
+
+-- int4 key x int8 query
+SELECT count(*) FROM ct_i4 WHERE a < 50::int8;
+SELECT count(*) FROM ct_i4 WHERE a <= 50::int8;
+SELECT count(*) FROM ct_i4 WHERE a = 50::int8;
+SELECT count(*) FROM ct_i4 WHERE a >= 50::int8;
+SELECT count(*) FROM ct_i4 WHERE a > 50::int8;
+SELECT count(*) FROM ct_i4 WHERE a <> 50::int8;
+SELECT count(*) FROM ct_i4 WHERE a = 5000000000::int8;
+SELECT count(*) FROM ct_i4 WHERE a < 5000000000::int8;
+
+-- int8 key x int2 query
+SELECT count(*) FROM ct_i8 WHERE a < 50::int2;
+SELECT count(*) FROM ct_i8 WHERE a <= 50::int2;
+SELECT count(*) FROM ct_i8 WHERE a = 50::int2;
+SELECT count(*) FROM ct_i8 WHERE a >= 50::int2;
+SELECT count(*) FROM ct_i8 WHERE a > 50::int2;
+SELECT count(*) FROM ct_i8 WHERE a <> 50::int2;
+
+-- int8 key x int4 query
+SELECT count(*) FROM ct_i8 WHERE a < 50::int4;
+SELECT count(*) FROM ct_i8 WHERE a <= 50::int4;
+SELECT count(*) FROM ct_i8 WHERE a = 50::int4;
+SELECT count(*) FROM ct_i8 WHERE a >= 50::int4;
+SELECT count(*) FROM ct_i8 WHERE a > 50::int4;
+SELECT count(*) FROM ct_i8 WHERE a <> 50::int4;
+
+-- Confirm the index is actually used for a cross-type predicate.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM ct_i4 WHERE a = 50::int8;
+
+-- Cross-type KNN: int4 key ordered by int2 / int8 queries.
+EXPLAIN (COSTS OFF)
+SELECT a FROM ct_i4 ORDER BY a <-> '-100'::int2 LIMIT 3;
+SELECT a FROM ct_i4 ORDER BY a <-> '-100'::int2 LIMIT 3;
+
+EXPLAIN (COSTS OFF)
+SELECT a FROM ct_i4 ORDER BY a <-> '-100'::int8 LIMIT 3;
+SELECT a FROM ct_i4 ORDER BY a <-> '-100'::int8 LIMIT 3;
+
+-- Cross-type KNN: int2 key ordered by int4 / int8 queries.
+SELECT a FROM ct_i2 ORDER BY a <-> '-100'::int4 LIMIT 3;
+SELECT a FROM ct_i2 ORDER BY a <-> '-100'::int8 LIMIT 3;
+
+-- Cross-type KNN: int8 key ordered by int2 / int4 queries.
+SELECT a FROM ct_i8 ORDER BY a <-> '-100'::int2 LIMIT 3;
+SELECT a FROM ct_i8 ORDER BY a <-> '-100'::int4 LIMIT 3;
+
+-- Combined: cross-type WHERE + cross-type ORDER BY on the same index.
+EXPLAIN (COSTS OFF)
+SELECT a FROM ct_i4 WHERE a < 80::int8 ORDER BY a <-> '-100'::int8 LIMIT 3;
+SELECT a FROM ct_i4 WHERE a < 80::int8 ORDER BY a <-> '-100'::int8 LIMIT 3;
+
+-- Standalone distance-function smoke tests (not going through the index),
+-- including the overflow-detection paths.
+SELECT int2_int4_dist(3::int2, 10::int4);
+SELECT int4_int2_dist(-5::int4, 5::int2);
+SELECT int2_int8_dist(3::int2, 10::int8);
+SELECT int8_int2_dist(100::int8, -5::int2);
+SELECT int4_int8_dist(100::int4, 5000000000::int8);
+SELECT int8_int4_dist(5000000000::int8, 100::int4);
+
+-- Overflow detection: INT32_MIN distance from a positive int2 can't fit
+-- in int32, should error.
+SELECT int2_int4_dist(1::int2, -2147483648::int4);
+-- Likewise INT64_MIN distance from a positive int4 can't fit in int64.
+SELECT int4_int8_dist(1::int4, -9223372036854775808::int8);
+
+--
+-- Multi-column GiST index with mixed-type predicates. This is the
+-- original motivating case: without cross-type operator support the
+-- planner can only use one column as an Index Cond and applies the
+-- other(s) as a Filter post-scan. Here both columns should appear as
+-- Index Cond.
+--
+CREATE TABLE ct_multi (a int4, b int8);
+INSERT INTO ct_multi
+ SELECT g, (g * 2)::int8 FROM generate_series(-50, 50) g;
+CREATE INDEX ct_multi_idx ON ct_multi USING gist (a, b);
+ANALYZE ct_multi;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM ct_multi WHERE a = 25::int8 AND b = 50::int4;
+
+SELECT count(*) FROM ct_multi WHERE a = 25::int8 AND b = 50::int4;
+
+-- Mixed cross-type ranges across both columns.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM ct_multi WHERE a < 10::int8 AND b > 0::int2;
+
+SELECT count(*) FROM ct_multi WHERE a < 10::int8 AND b > 0::int2;
+
+DROP TABLE ct_multi;
+
+--
+-- Width-sensitivity guard (int16 vs int32).
+--
+-- The cross-type callbacks must read each side at its OWN width: the query at
+-- the subtype width and the key at the indexed-column width, never narrowing
+-- one to the other. These cases are constructed so that reading either side at
+-- the wrong width changes the answer -- a dispatch entry returning the
+-- wrong-width tinfo, an int4 key truncated to int16, or the query/key arguments
+-- being swapped in gbt_num_consistent()/gbt_num_distance() would all flip the
+-- result, because each wide constant shares its low 16 bits with a value present
+-- in the data. enable_seqscan/bitmapscan are already off, so the GiST
+-- consistent/distance path is exercised.
+--
+CREATE TABLE ct_w4 (k int4);
+INSERT INTO ct_w4 VALUES (1), (65538); -- 65538 = 0x10002, low 16 bits = 2
+CREATE INDEX ct_w4_idx ON ct_w4 USING gist (k);
+ANALYZE ct_w4;
+
+-- int4 key vs int2 query: 65538 must compare as 65538, not as 2.
+-- correct: {65538}; an int4 key truncated to int16 (65538 -> 2) would give {}.
+SELECT k FROM ct_w4 WHERE k > 1000::int2 ORDER BY k;
+-- correct: {} (no key equals 2); a truncated key (65538 -> 2) would match.
+SELECT k FROM ct_w4 WHERE k = 2::int2 ORDER BY k;
+
+-- KNN: nearest to 2 is 1 (distance 1). enable_sort=off steers the planner to
+-- the GiST KNN distance path rather than a Sort on the operator. A truncated
+-- key (65538 -> 2, distance 0) would wrongly sort 65538 first. (We assert the
+-- result, not the plan shape, to stay portable across visibility-map state.)
+SET enable_sort = off;
+SELECT k FROM ct_w4 ORDER BY k <-> 2::int2 LIMIT 1;
+RESET enable_sort;
+
+CREATE TABLE ct_w2 (k int2);
+INSERT INTO ct_w2 VALUES (1), (50);
+CREATE INDEX ct_w2_idx ON ct_w2 USING gist (k);
+ANALYZE ct_w2;
+
+-- int2 key vs int4 query: 65537 must compare as 65537, not as 1 (0x10001).
+-- correct: 0; an int4 query truncated to int16 (65537 -> 1) would match k=1.
+SELECT count(*) FROM ct_w2 WHERE k = 65537::int4;
+-- correct: 0; a truncated query (65537 -> 1) would match k=50.
+SELECT count(*) FROM ct_w2 WHERE k > 65537::int4;
+
+DROP TABLE ct_w4;
+DROP TABLE ct_w2;
+
+DROP TABLE ct_i2;
+DROP TABLE ct_i4;
+DROP TABLE ct_i8;
--
2.51.0
[application/octet-stream] v5-0003-doc-Document-cross-type-operator-support-in-btree_gi.patch (2.9K, 5-v5-0003-doc-Document-cross-type-operator-support-in-btree_gi.patch)
download | inline diff:
From efe64c8d1ffbc0729de850c79a47c9d1ce62d54e Mon Sep 17 00:00:00 2001
From: Alexander Nestorov <[email protected]>
Date: Thu, 4 Jun 2026 00:09:08 +0200
Subject: [PATCH] doc: Document cross-type operator support in btree_gist
Note that as of btree_gist 1.10 the int2, int4 and int8 operator
classes accept cross-type query operators (the B-tree comparison
operators and the <-> distance operator) against the other two
integer types without an explicit cast, and that out-of-range query
constants are compared by normal integer semantics.
Also document that the integer <-> distance is computed in float8, so
for int8 distances beyond 2^53 the result loses precision and may
reorder nearly-equidistant matches; it never changes which rows match.
---
doc/src/sgml/btree-gist.sgml | 24 ++++++++++++++++++++++++
1 file changed, 24 insertions(+)
diff --git a/doc/src/sgml/btree-gist.sgml b/doc/src/sgml/btree-gist.sgml
index cc09ec83733..1b1dce707cf 100644
--- a/doc/src/sgml/btree-gist.sgml
+++ b/doc/src/sgml/btree-gist.sgml
@@ -49,12 +49,36 @@
<type>float8</type>, <type>timestamp with time zone</type>,
<type>timestamp without time zone</type>,
<type>time without time zone</type>, <type>date</type>, <type>interval</type>,
<type>oid</type>, and <type>money</type>.
</para>
+ <para>
+ As of version 1.10, the operator classes for <type>int2</type>,
+ <type>int4</type> and <type>int8</type> additionally support cross-type
+ query operators. An index on any one of these integer types can be used
+ with a B-tree-style comparison (<literal><</literal>,
+ <literal><=</literal>, <literal>=</literal>, <literal>>=</literal>,
+ <literal>></literal>, <literal><></literal>) or with the
+ distance operator (<literal><-></literal>) against a value of any
+ of the other two integer types, without an explicit cast. Query
+ constants outside the indexed column's range are compared according to
+ normal integer comparison semantics, without narrowing the query value
+ to the column type first.
+ </para>
+
+ <para>
+ The integer distance operator (<literal><-></literal>) used for
+ nearest-neighbor (<literal>ORDER BY</literal>) searches computes the
+ distance in <type>float8</type>. For <type>int8</type> values whose
+ distance from the query exceeds 2^53, the result loses precision and
+ may reorder results that are nearly equidistant; it never changes which
+ rows match. This applies equally to same-type and cross-type
+ <type>int8</type> distance searches.
+ </para>
+
<para>
By default <filename>btree_gist</filename> builds <acronym>GiST</acronym> index with
<function>sortsupport</function> in <firstterm>sorted</firstterm> mode. This usually results in
much faster index built speed. It is still possible to revert to buffered built strategy
by using the <literal>buffering</literal> parameter when creating the index.
</para>
--
2.51.0
view thread (9+ messages)
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: [PATCH] btree_gist: add cross-type integer operator support for GiST
In-Reply-To: <aea734bb-fd8d-4642-8972-2a57e5643a03@Spark>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox