Re: Cross-type index comparison support in contrib/btree_gin - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: Cross-type index comparison support in contrib/btree_gin |
| Date | |
| Msg-id | 1733979.1738968092@sss.pgh.pa.us Whole thread Raw |
| In response to | Cross-type index comparison support in contrib/btree_gin (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
I wrote:
> I forgot to mention a couple of questions for review:
> Should we adjust the documentation of comparePartial() to promise
> explicitly that partial_key is the same datum returned by extractQuery?
> By my reading, it kind of implies that, but it's not quite black and
> white.
> In the 0005 patch, I relied on date2timestamp_opt_overflow and
> its siblings where available. But some of the conversions such
> as timestamptz-to-timestamp don't have one of those, so I was
> forced to copy-and-paste some fairly low-level code. Would it
> make sense to refactor the related core routines to expose
> xxx2yyy_opt_overflow interfaces, extending what 5bc450629 and
> 52ad1e659 did?
After further review it seems like both of those things would be
improvements, so here's a v2 that does it like that. This also
adds a PG_USED_FOR_ASSERTS_ONLY marker whose lack was pointed
out by the cfbot; no other meaningful changes.
regards, tom lane
From 9ff000246affd6b9063168a876bbcc0db4f78dd8 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 7 Feb 2025 15:37:30 -0500
Subject: [PATCH v2 1/6] Break out xxx2yyy_opt_overflow APIs for more datetime
conversions.
Previous commits invented timestamp2timestamptz_opt_overflow,
date2timestamp_opt_overflow, and date2timestamptz_opt_overflow
functions to perform non-error-throwing conversions between
datetime types. This patch completes the set by adding
timestamp2date_opt_overflow, timestamptz2date_opt_overflow,
and timestamptz2timestamp_opt_overflow.
In addition, adjust timestamp2timestamptz_opt_overflow so that it
doesn't throw error if timestamp2tm fails, but treats that as an
overflow case. The situation probably can't arise except with an
invalid timestamp value, and I can't think of a way that that would
happen except data corruption. However, it's pretty silly to have a
function whose entire reason for existence is to not throw errors for
out-of-range inputs nonetheless throw an error for out-of-range input.
The new APIs are not used in this patch, but will be needed by
btree_gin.
Discussion: https://postgr.es/m/262624.1738460652@sss.pgh.pa.us
---
src/backend/utils/adt/date.c | 86 ++++++++++++++++++++++++++++++-
src/backend/utils/adt/timestamp.c | 81 ++++++++++++++++++++++++-----
src/include/utils/date.h | 2 +
src/include/utils/timestamp.h | 3 ++
4 files changed, 156 insertions(+), 16 deletions(-)
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index f279853deb..07f40eba69 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -1317,10 +1317,35 @@ timestamp_date(PG_FUNCTION_ARGS)
{
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
DateADT result;
+
+ result = timestamp2date_opt_overflow(timestamp, NULL);
+ PG_RETURN_DATEADT(result);
+}
+
+/*
+ * Convert timestamp to date.
+ *
+ * On successful conversion, *overflow is set to zero if it's not NULL.
+ *
+ * If the timestamp is finite but out of the valid range for date, then:
+ * if overflow is NULL, we throw an out-of-range error.
+ * if overflow is not NULL, we store +1 or -1 there to indicate the sign
+ * of the overflow, and return the appropriate date infinity.
+ *
+ * Note: given the ranges of the types, overflow is only possible at
+ * the minimum end of the range, but we don't assume that in this code.
+ */
+DateADT
+timestamp2date_opt_overflow(Timestamp timestamp, int *overflow)
+{
+ DateADT result;
struct pg_tm tt,
*tm = &tt;
fsec_t fsec;
+ if (overflow)
+ *overflow = 0;
+
if (TIMESTAMP_IS_NOBEGIN(timestamp))
DATE_NOBEGIN(result);
else if (TIMESTAMP_IS_NOEND(timestamp))
@@ -1328,14 +1353,30 @@ timestamp_date(PG_FUNCTION_ARGS)
else
{
if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (overflow)
+ {
+ if (timestamp < 0)
+ {
+ *overflow = -1;
+ DATE_NOBEGIN(result);
+ }
+ else
+ {
+ *overflow = 1; /* not actually reachable */
+ DATE_NOEND(result);
+ }
+ return result;
+ }
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
}
- PG_RETURN_DATEADT(result);
+ return result;
}
@@ -1362,11 +1403,36 @@ timestamptz_date(PG_FUNCTION_ARGS)
{
TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
DateADT result;
+
+ result = timestamptz2date_opt_overflow(timestamp, NULL);
+ PG_RETURN_DATEADT(result);
+}
+
+/*
+ * Convert timestamptz to date.
+ *
+ * On successful conversion, *overflow is set to zero if it's not NULL.
+ *
+ * If the timestamptz is finite but out of the valid range for date, then:
+ * if overflow is NULL, we throw an out-of-range error.
+ * if overflow is not NULL, we store +1 or -1 there to indicate the sign
+ * of the overflow, and return the appropriate date infinity.
+ *
+ * Note: given the ranges of the types, overflow is only possible at
+ * the minimum end of the range, but we don't assume that in this code.
+ */
+DateADT
+timestamptz2date_opt_overflow(TimestampTz timestamp, int *overflow)
+{
+ DateADT result;
struct pg_tm tt,
*tm = &tt;
fsec_t fsec;
int tz;
+ if (overflow)
+ *overflow = 0;
+
if (TIMESTAMP_IS_NOBEGIN(timestamp))
DATE_NOBEGIN(result);
else if (TIMESTAMP_IS_NOEND(timestamp))
@@ -1374,14 +1440,30 @@ timestamptz_date(PG_FUNCTION_ARGS)
else
{
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (overflow)
+ {
+ if (timestamp < 0)
+ {
+ *overflow = -1;
+ DATE_NOBEGIN(result);
+ }
+ else
+ {
+ *overflow = 1; /* not actually reachable */
+ DATE_NOEND(result);
+ }
+ return result;
+ }
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
}
- PG_RETURN_DATEADT(result);
+ return result;
}
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index ba9bae0506..d1b68dd2fa 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -6411,7 +6411,7 @@ timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
if (TIMESTAMP_NOT_FINITE(timestamp))
return timestamp;
- /* We don't expect this to fail, but check it pro forma */
+ /* timestamp2tm should not fail on valid timestamps, but cope */
if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
{
tz = DetermineTimeZoneOffset(tm, session_timezone);
@@ -6419,23 +6419,22 @@ timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
result = dt2local(timestamp, -tz);
if (IS_VALID_TIMESTAMP(result))
- {
return result;
+ }
+
+ if (overflow)
+ {
+ if (timestamp < 0)
+ {
+ *overflow = -1;
+ TIMESTAMP_NOBEGIN(result);
}
- else if (overflow)
+ else
{
- if (result < MIN_TIMESTAMP)
- {
- *overflow = -1;
- TIMESTAMP_NOBEGIN(result);
- }
- else
- {
- *overflow = 1;
- TIMESTAMP_NOEND(result);
- }
- return result;
+ *overflow = 1;
+ TIMESTAMP_NOEND(result);
}
+ return result;
}
ereport(ERROR,
@@ -6465,8 +6464,27 @@ timestamptz_timestamp(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMP(timestamptz2timestamp(timestamp));
}
+/*
+ * Convert timestamptz to timestamp, throwing error for overflow.
+ */
static Timestamp
timestamptz2timestamp(TimestampTz timestamp)
+{
+ return timestamptz2timestamp_opt_overflow(timestamp, NULL);
+}
+
+/*
+ * Convert timestamp with time zone to timestamp.
+ *
+ * On successful conversion, *overflow is set to zero if it's not NULL.
+ *
+ * If the timestamptz is finite but out of the valid range for timestamp, then:
+ * if overflow is NULL, we throw an out-of-range error.
+ * if overflow is not NULL, we store +1 or -1 there to indicate the sign
+ * of the overflow, and return the appropriate timestamp infinity.
+ */
+Timestamp
+timestamptz2timestamp_opt_overflow(TimestampTz timestamp, int *overflow)
{
Timestamp result;
struct pg_tm tt,
@@ -6474,18 +6492,53 @@ timestamptz2timestamp(TimestampTz timestamp)
fsec_t fsec;
int tz;
+ if (overflow)
+ *overflow = 0;
+
if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ {
+ if (overflow)
+ {
+ if (timestamp < 0)
+ {
+ *overflow = -1;
+ TIMESTAMP_NOBEGIN(result);
+ }
+ else
+ {
+ *overflow = 1;
+ TIMESTAMP_NOEND(result);
+ }
+ return result;
+ }
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
if (tm2timestamp(tm, fsec, NULL, &result) != 0)
+ {
+ if (overflow)
+ {
+ if (timestamp < 0)
+ {
+ *overflow = -1;
+ TIMESTAMP_NOBEGIN(result);
+ }
+ else
+ {
+ *overflow = 1;
+ TIMESTAMP_NOEND(result);
+ }
+ return result;
+ }
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
}
return result;
}
diff --git a/src/include/utils/date.h b/src/include/utils/date.h
index bb5c1e57b0..abfda0b1ae 100644
--- a/src/include/utils/date.h
+++ b/src/include/utils/date.h
@@ -100,6 +100,8 @@ extern int32 anytime_typmod_check(bool istz, int32 typmod);
extern double date2timestamp_no_overflow(DateADT dateVal);
extern Timestamp date2timestamp_opt_overflow(DateADT dateVal, int *overflow);
extern TimestampTz date2timestamptz_opt_overflow(DateADT dateVal, int *overflow);
+extern DateADT timestamp2date_opt_overflow(Timestamp timestamp, int *overflow);
+extern DateADT timestamptz2date_opt_overflow(TimestampTz timestamp, int *overflow);
extern int32 date_cmp_timestamp_internal(DateADT dateVal, Timestamp dt2);
extern int32 date_cmp_timestamptz_internal(DateADT dateVal, TimestampTz dt2);
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index d26f023fb8..4a88df4c6e 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -132,6 +132,9 @@ extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2);
extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp,
int *overflow);
+extern Timestamp timestamptz2timestamp_opt_overflow(TimestampTz timestamp,
+ int *overflow);
+
extern int32 timestamp_cmp_timestamptz_internal(Timestamp timestampVal,
TimestampTz dt2);
--
2.43.5
From b587446f5e715b56aa3df7c28b621aa86099339b Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 7 Feb 2025 17:01:19 -0500
Subject: [PATCH v2 2/6] Preliminary refactoring.
This step doesn't change any behavior. It cleans the code up
slightly and documents it better. In particular, the test
being used by gin_btree_compare_prefix is better explained (IMO)
and there's now an Assert backing up the assumption it has to make.
Discussion: https://postgr.es/m/262624.1738460652@sss.pgh.pa.us
---
contrib/btree_gin/btree_gin.c | 90 +++++++++++++++++++-------------
doc/src/sgml/gin.sgml | 6 ++-
src/tools/pgindent/typedefs.list | 1 +
3 files changed, 60 insertions(+), 37 deletions(-)
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 533c55e9ea..56383e2786 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -16,14 +16,18 @@
PG_MODULE_MAGIC;
+/* extra data passed from gin_btree_extract_query to gin_btree_compare_prefix */
typedef struct QueryInfo
{
- StrategyNumber strategy;
- Datum datum;
- bool is_varlena;
- Datum (*typecmp) (FunctionCallInfo);
+ StrategyNumber strategy; /* operator strategy number */
+ Datum orig_datum; /* original query (comparison) datum */
+ Datum entry_datum; /* datum we reported as the entry value */
+ PGFunction typecmp; /* appropriate btree comparison function */
} QueryInfo;
+typedef Datum (*btree_gin_leftmost_function) (void);
+
+
/*** GIN support functions shared by all datatypes ***/
static Datum
@@ -33,6 +37,7 @@ gin_btree_extract_value(FunctionCallInfo fcinfo, bool is_varlena)
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
Datum *entries = (Datum *) palloc(sizeof(Datum));
+ /* Ensure that values stored in the index are not toasted */
if (is_varlena)
datum = PointerGetDatum(PG_DETOAST_DATUM(datum));
entries[0] = datum;
@@ -41,19 +46,11 @@ gin_btree_extract_value(FunctionCallInfo fcinfo, bool is_varlena)
PG_RETURN_POINTER(entries);
}
-/*
- * For BTGreaterEqualStrategyNumber, BTGreaterStrategyNumber, and
- * BTEqualStrategyNumber we want to start the index scan at the
- * supplied query datum, and work forward. For BTLessStrategyNumber
- * and BTLessEqualStrategyNumber, we need to start at the leftmost
- * key, and work forward until the supplied query datum (which must be
- * sent along inside the QueryInfo structure).
- */
static Datum
gin_btree_extract_query(FunctionCallInfo fcinfo,
bool is_varlena,
- Datum (*leftmostvalue) (void),
- Datum (*typecmp) (FunctionCallInfo))
+ btree_gin_leftmost_function leftmostvalue,
+ PGFunction typecmp)
{
Datum datum = PG_GETARG_DATUM(0);
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
@@ -62,20 +59,29 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
Pointer **extra_data = (Pointer **) PG_GETARG_POINTER(4);
Datum *entries = (Datum *) palloc(sizeof(Datum));
QueryInfo *data = (QueryInfo *) palloc(sizeof(QueryInfo));
- bool *ptr_partialmatch;
+ bool *ptr_partialmatch = (bool *) palloc(sizeof(bool));
- *nentries = 1;
- ptr_partialmatch = *partialmatch = (bool *) palloc(sizeof(bool));
- *ptr_partialmatch = false;
+ /*
+ * Detoast the comparison datum. This isn't necessary for correctness,
+ * but it can save repeat detoastings within the comparison function.
+ */
if (is_varlena)
datum = PointerGetDatum(PG_DETOAST_DATUM(datum));
- data->strategy = strategy;
- data->datum = datum;
- data->is_varlena = is_varlena;
- data->typecmp = typecmp;
- *extra_data = (Pointer *) palloc(sizeof(Pointer));
- **extra_data = (Pointer) data;
+ /* Prep single comparison key with possible partial-match flag */
+ *nentries = 1;
+ *partialmatch = ptr_partialmatch;
+ *ptr_partialmatch = false;
+
+ /*
+ * For BTGreaterEqualStrategyNumber, BTGreaterStrategyNumber, and
+ * BTEqualStrategyNumber we want to start the index scan at the supplied
+ * query datum, and work forward. For BTLessStrategyNumber and
+ * BTLessEqualStrategyNumber, we need to start at the leftmost key, and
+ * work forward until the supplied query datum (which we'll send along
+ * inside the QueryInfo structure). Use partial match rules except for
+ * BTEqualStrategyNumber.
+ */
switch (strategy)
{
case BTLessStrategyNumber:
@@ -94,30 +100,42 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
elog(ERROR, "unrecognized strategy number: %d", strategy);
}
+ /* Fill "extra" data */
+ data->strategy = strategy;
+ data->orig_datum = datum;
+ data->entry_datum = entries[0];
+ data->typecmp = typecmp;
+ *extra_data = (Pointer *) palloc(sizeof(Pointer));
+ **extra_data = (Pointer) data;
+
PG_RETURN_POINTER(entries);
}
-/*
- * Datum a is a value from extract_query method and for BTLess*
- * strategy it is a left-most value. So, use original datum from QueryInfo
- * to decide to stop scanning or not. Datum b is always from index.
- */
static Datum
gin_btree_compare_prefix(FunctionCallInfo fcinfo)
{
- Datum a = PG_GETARG_DATUM(0);
- Datum b = PG_GETARG_DATUM(1);
+ Datum partial_key PG_USED_FOR_ASSERTS_ONLY = PG_GETARG_DATUM(0);
+ Datum key = PG_GETARG_DATUM(1);
QueryInfo *data = (QueryInfo *) PG_GETARG_POINTER(3);
int32 res,
cmp;
+ /*
+ * partial_key is only an approximation to the real comparison value,
+ * especially if it's a leftmost value. We can get an accurate answer by
+ * doing a possibly-cross-type comparison to the real comparison value.
+ * But just to be sure that things are what we expect, let's assert that
+ * partial_key is indeed what gin_btree_extract_query reported, so that
+ * we'll notice if anyone ever changes the core code in a way that breaks
+ * our assumptions.
+ */
+ Assert(partial_key == data->entry_datum);
+
cmp = DatumGetInt32(CallerFInfoFunctionCall2(data->typecmp,
fcinfo->flinfo,
PG_GET_COLLATION(),
- (data->strategy == BTLessStrategyNumber ||
- data->strategy == BTLessEqualStrategyNumber)
- ? data->datum : a,
- b));
+ data->orig_datum,
+ key));
switch (data->strategy)
{
@@ -149,7 +167,7 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo)
res = 1;
break;
case BTGreaterStrategyNumber:
- /* If original datum <= indexed one then return match */
+ /* If original datum < indexed one then return match */
/* If original datum == indexed one then continue scan */
if (cmp < 0)
res = 0;
diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml
index 46e87e0132..82410b1fbd 100644
--- a/doc/src/sgml/gin.sgml
+++ b/doc/src/sgml/gin.sgml
@@ -394,7 +394,11 @@
Pointer extra_data)</function></term>
<listitem>
<para>
- Compare a partial-match query key to an index key. Returns an integer
+ Compare a partial-match query key to an index key.
+ <literal>partial_key</literal> is a query key that was returned
+ by <function>extractQuery</function> with an indication that it
+ requires partial match, and <literal>key</literal> is an index entry.
+ Returns an integer
whose sign indicates the result: less than zero means the index key
does not match the query, but the index scan should continue; zero
means that the index key does match the query; greater than zero
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9a3bee93de..e3daab0905 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3379,6 +3379,7 @@ bloom_filter
boolKEY
brin_column_state
brin_serialize_callback_type
+btree_gin_leftmost_function
bytea
cached_re_str
canonicalize_state
--
2.43.5
From fa763b9c0b386d1ea5cace8edbdd21d3ddabf1ff Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 7 Feb 2025 17:12:56 -0500
Subject: [PATCH v2 3/6] Add cross-type comparisons for integer types.
Extend the infrastructure in btree_gin.c to permit cross-type
operators, and add the code to support them for the int2, int4,
and int8 opclasses. (To keep this patch digestible, I left
the other datatypes for later.)
Discussion: https://postgr.es/m/262624.1738460652@sss.pgh.pa.us
---
contrib/btree_gin/Makefile | 2 +-
contrib/btree_gin/btree_gin--1.3--1.4.sql | 63 ++++
contrib/btree_gin/btree_gin.c | 387 +++++++++++++++++++---
contrib/btree_gin/btree_gin.control | 2 +-
contrib/btree_gin/expected/int2.out | 184 ++++++++++
contrib/btree_gin/expected/int4.out | 100 ++++++
contrib/btree_gin/expected/int8.out | 100 ++++++
contrib/btree_gin/meson.build | 1 +
contrib/btree_gin/sql/int2.sql | 34 ++
contrib/btree_gin/sql/int4.sql | 18 +
contrib/btree_gin/sql/int8.sql | 18 +
src/tools/pgindent/typedefs.list | 1 +
12 files changed, 859 insertions(+), 51 deletions(-)
create mode 100644 contrib/btree_gin/btree_gin--1.3--1.4.sql
diff --git a/contrib/btree_gin/Makefile b/contrib/btree_gin/Makefile
index 0a15811516..ad054598db 100644
--- a/contrib/btree_gin/Makefile
+++ b/contrib/btree_gin/Makefile
@@ -7,7 +7,7 @@ OBJS = \
EXTENSION = btree_gin
DATA = btree_gin--1.0.sql btree_gin--1.0--1.1.sql btree_gin--1.1--1.2.sql \
- btree_gin--1.2--1.3.sql
+ btree_gin--1.2--1.3.sql btree_gin--1.3--1.4.sql
PGFILEDESC = "btree_gin - B-tree equivalent GIN operator classes"
REGRESS = install_btree_gin int2 int4 int8 float4 float8 money oid \
diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql
new file mode 100644
index 0000000000..71e577de2d
--- /dev/null
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -0,0 +1,63 @@
+/* contrib/btree_gin/btree_gin--1.3--1.4.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "ALTER EXTENSION btree_gin UPDATE TO '1.4'" to load this file. \quit
+
+--
+-- Cross-type operator support is new in 1.4. We only need to worry
+-- about this for cross-type operators that exist in core.
+--
+-- Because the opclass extractQuery and consistent methods don't directly
+-- get any information about the datatype of the RHS value, we have to
+-- encode that in the operator strategy numbers. The strategy numbers
+-- are the operator's normal btree strategy (1-5) plus 8 times a code
+-- for the RHS datatype.
+--
+
+ALTER OPERATOR FAMILY int2_ops USING gin
+ADD
+ -- Code 1: RHS is int4
+ OPERATOR 9 < (int2, int4),
+ OPERATOR 10 <= (int2, int4),
+ OPERATOR 11 = (int2, int4),
+ OPERATOR 12 >= (int2, int4),
+ OPERATOR 13 > (int2, int4),
+ -- Code 2: RHS is int8
+ OPERATOR 17 < (int2, int8),
+ OPERATOR 18 <= (int2, int8),
+ OPERATOR 19 = (int2, int8),
+ OPERATOR 20 >= (int2, int8),
+ OPERATOR 21 > (int2, int8)
+;
+
+ALTER OPERATOR FAMILY int4_ops USING gin
+ADD
+ -- Code 1: RHS is int2
+ OPERATOR 9 < (int4, int2),
+ OPERATOR 10 <= (int4, int2),
+ OPERATOR 11 = (int4, int2),
+ OPERATOR 12 >= (int4, int2),
+ OPERATOR 13 > (int4, int2),
+ -- Code 2: RHS is int8
+ OPERATOR 17 < (int4, int8),
+ OPERATOR 18 <= (int4, int8),
+ OPERATOR 19 = (int4, int8),
+ OPERATOR 20 >= (int4, int8),
+ OPERATOR 21 > (int4, int8)
+;
+
+ALTER OPERATOR FAMILY int8_ops USING gin
+ADD
+ -- Code 1: RHS is int2
+ OPERATOR 9 < (int8, int2),
+ OPERATOR 10 <= (int8, int2),
+ OPERATOR 11 = (int8, int2),
+ OPERATOR 12 >= (int8, int2),
+ OPERATOR 13 > (int8, int2),
+ -- Code 2: RHS is int4
+ OPERATOR 17 < (int8, int4),
+ OPERATOR 18 <= (int8, int4),
+ OPERATOR 19 = (int8, int4),
+ OPERATOR 20 >= (int8, int4),
+ OPERATOR 21 > (int8, int4)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 56383e2786..558675cf76 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -25,6 +25,8 @@ typedef struct QueryInfo
PGFunction typecmp; /* appropriate btree comparison function */
} QueryInfo;
+typedef Datum (*btree_gin_convert_function) (Datum input);
+
typedef Datum (*btree_gin_leftmost_function) (void);
@@ -48,9 +50,10 @@ gin_btree_extract_value(FunctionCallInfo fcinfo, bool is_varlena)
static Datum
gin_btree_extract_query(FunctionCallInfo fcinfo,
- bool is_varlena,
btree_gin_leftmost_function leftmostvalue,
- PGFunction typecmp)
+ const bool *rhs_is_varlena,
+ const btree_gin_convert_function *cvt_fns,
+ const PGFunction *cmp_fns)
{
Datum datum = PG_GETARG_DATUM(0);
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
@@ -60,12 +63,23 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
Datum *entries = (Datum *) palloc(sizeof(Datum));
QueryInfo *data = (QueryInfo *) palloc(sizeof(QueryInfo));
bool *ptr_partialmatch = (bool *) palloc(sizeof(bool));
+ int btree_strat,
+ rhs_code;
+
+ /*
+ * "strategy & 7" extracts the basic btree strategy code from the given
+ * strategy number, while "strategy >> 3" extracts the code identifying
+ * the RHS data type, which we use as an index into the given function
+ * pointer arrays.
+ */
+ btree_strat = strategy & 7;
+ rhs_code = strategy >> 3;
/*
* Detoast the comparison datum. This isn't necessary for correctness,
* but it can save repeat detoastings within the comparison function.
*/
- if (is_varlena)
+ if (rhs_is_varlena[rhs_code])
datum = PointerGetDatum(PG_DETOAST_DATUM(datum));
/* Prep single comparison key with possible partial-match flag */
@@ -80,9 +94,10 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
* BTLessEqualStrategyNumber, we need to start at the leftmost key, and
* work forward until the supplied query datum (which we'll send along
* inside the QueryInfo structure). Use partial match rules except for
- * BTEqualStrategyNumber.
+ * BTEqualStrategyNumber without a conversion function. (If there is a
+ * conversion function, comparison to the entry value is not trustworthy.)
*/
- switch (strategy)
+ switch (btree_strat)
{
case BTLessStrategyNumber:
case BTLessEqualStrategyNumber:
@@ -94,7 +109,14 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
*ptr_partialmatch = true;
/* FALLTHROUGH */
case BTEqualStrategyNumber:
- entries[0] = datum;
+ /* If we have a conversion function, apply it */
+ if (cvt_fns && cvt_fns[rhs_code])
+ {
+ entries[0] = (*cvt_fns[rhs_code]) (datum);
+ *ptr_partialmatch = true;
+ }
+ else
+ entries[0] = datum;
break;
default:
elog(ERROR, "unrecognized strategy number: %d", strategy);
@@ -104,7 +126,7 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
data->strategy = strategy;
data->orig_datum = datum;
data->entry_datum = entries[0];
- data->typecmp = typecmp;
+ data->typecmp = cmp_fns[rhs_code];
*extra_data = (Pointer *) palloc(sizeof(Pointer));
**extra_data = (Pointer) data;
@@ -124,6 +146,9 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo)
* partial_key is only an approximation to the real comparison value,
* especially if it's a leftmost value. We can get an accurate answer by
* doing a possibly-cross-type comparison to the real comparison value.
+ * (Note that partial_key and key are of the indexed datatype while
+ * orig_datum is of the query operator's RHS datatype.)
+ *
* But just to be sure that things are what we expect, let's assert that
* partial_key is indeed what gin_btree_extract_query reported, so that
* we'll notice if anyone ever changes the core code in a way that breaks
@@ -137,44 +162,52 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo)
data->orig_datum,
key));
- switch (data->strategy)
+ /*
+ * Convert the comparison result to the correct thing for the search
+ * operator strategy. When dealing with cross-type comparisons, an
+ * imprecise entry datum could lead GIN to start the scan just before the
+ * first possible match, so we must continue the scan if the current index
+ * entry doesn't satisfy the search condition for =, >= and > cases.
+ *
+ * As above, we want just the low 3 bits of the strategy code here.
+ */
+ switch (data->strategy & 7)
{
case BTLessStrategyNumber:
/* If original datum > indexed one then return match */
if (cmp > 0)
res = 0;
else
- res = 1;
+ res = 1; /* end scan */
break;
case BTLessEqualStrategyNumber:
/* The same except equality */
if (cmp >= 0)
res = 0;
else
- res = 1;
+ res = 1; /* end scan */
break;
case BTEqualStrategyNumber:
- if (cmp != 0)
- res = 1;
- else
+ if (cmp > 0)
+ res = -1; /* keep scanning */
+ else if (cmp == 0)
res = 0;
+ else
+ res = 1; /* end scan */
break;
case BTGreaterEqualStrategyNumber:
/* If original datum <= indexed one then return match */
if (cmp <= 0)
res = 0;
else
- res = 1;
+ res = -1; /* keep scanning */
break;
case BTGreaterStrategyNumber:
/* If original datum < indexed one then return match */
- /* If original datum == indexed one then continue scan */
if (cmp < 0)
res = 0;
- else if (cmp == 0)
- res = -1;
else
- res = 1;
+ res = -1; /* keep scanning */
break;
default:
elog(ERROR, "unrecognized strategy number: %d",
@@ -197,19 +230,20 @@ gin_btree_consistent(PG_FUNCTION_ARGS)
/*** GIN_SUPPORT macro defines the datatype specific functions ***/
-#define GIN_SUPPORT(type, is_varlena, leftmostvalue, typecmp) \
+#define GIN_SUPPORT(type, leftmostvalue, is_varlena, cvtfns, cmpfns) \
PG_FUNCTION_INFO_V1(gin_extract_value_##type); \
Datum \
gin_extract_value_##type(PG_FUNCTION_ARGS) \
{ \
- return gin_btree_extract_value(fcinfo, is_varlena); \
+ return gin_btree_extract_value(fcinfo, is_varlena[0]); \
} \
PG_FUNCTION_INFO_V1(gin_extract_query_##type); \
Datum \
gin_extract_query_##type(PG_FUNCTION_ARGS) \
{ \
return gin_btree_extract_query(fcinfo, \
- is_varlena, leftmostvalue, typecmp); \
+ leftmostvalue, is_varlena, \
+ cvtfns, cmpfns); \
} \
PG_FUNCTION_INFO_V1(gin_compare_prefix_##type); \
Datum \
@@ -221,13 +255,66 @@ gin_compare_prefix_##type(PG_FUNCTION_ARGS) \
/*** Datatype specifications ***/
+/* Function to produce the least possible value of the indexed datatype */
static Datum
leftmostvalue_int2(void)
{
return Int16GetDatum(SHRT_MIN);
}
-GIN_SUPPORT(int2, false, leftmostvalue_int2, btint2cmp)
+/*
+ * For cross-type support, we must provide conversion functions that produce
+ * a Datum of the indexed datatype, since GIN requires the "entry" datums to
+ * be of that type. If an exact conversion is not possible, produce a value
+ * that will lead GIN to find the first index entry that is greater than
+ * or equal to the actual comparison value. (But rounding down is OK, so
+ * sometimes we might find an index entry that's just less than the
+ * comparison value.)
+ *
+ * For integer values, it's sufficient to clamp the input to be in-range.
+ *
+ * Note: for out-of-range input values, we could in theory detect that the
+ * search condition matches all or none of the index, and avoid a useless
+ * index descent in the latter case. Such searches are probably rare though,
+ * so we don't contort this code enough to do that.
+ */
+static Datum
+cvt_int4_int2(Datum input)
+{
+ int32 val = DatumGetInt32(input);
+
+ val = Max(val, SHRT_MIN);
+ val = Min(val, SHRT_MAX);
+ return Int16GetDatum((int16) val);
+}
+
+static Datum
+cvt_int8_int2(Datum input)
+{
+ int64 val = DatumGetInt64(input);
+
+ val = Max(val, SHRT_MIN);
+ val = Min(val, SHRT_MAX);
+ return Int16GetDatum((int16) val);
+}
+
+/*
+ * RHS-type-is-varlena flags, conversion and comparison function arrays,
+ * indexed by high bits of the operator strategy number. A NULL in the
+ * conversion function array indicates that no conversion is needed, which
+ * will always be the case for the zero'th entry. Note that the cross-type
+ * comparison functions should be the ones with the indexed datatype second.
+ */
+static const bool int2_rhs_is_varlena[] =
+{false, false, false};
+
+static const btree_gin_convert_function int2_cvt_fns[] =
+{NULL, cvt_int4_int2, cvt_int8_int2};
+
+static const PGFunction int2_cmp_fns[] =
+{btint2cmp, btint42cmp, btint82cmp};
+
+GIN_SUPPORT(int2, leftmostvalue_int2, int2_rhs_is_varlena, int2_cvt_fns, int2_cmp_fns)
static Datum
leftmostvalue_int4(void)
@@ -235,7 +322,34 @@ leftmostvalue_int4(void)
return Int32GetDatum(INT_MIN);
}
-GIN_SUPPORT(int4, false, leftmostvalue_int4, btint4cmp)
+static Datum
+cvt_int2_int4(Datum input)
+{
+ int16 val = DatumGetInt16(input);
+
+ return Int32GetDatum((int32) val);
+}
+
+static Datum
+cvt_int8_int4(Datum input)
+{
+ int64 val = DatumGetInt64(input);
+
+ val = Max(val, INT_MIN);
+ val = Min(val, INT_MAX);
+ return Int32GetDatum((int32) val);
+}
+
+static const bool int4_rhs_is_varlena[] =
+{false, false, false};
+
+static const btree_gin_convert_function int4_cvt_fns[] =
+{NULL, cvt_int2_int4, cvt_int8_int4};
+
+static const PGFunction int4_cmp_fns[] =
+{btint4cmp, btint24cmp, btint84cmp};
+
+GIN_SUPPORT(int4, leftmostvalue_int4, int4_rhs_is_varlena, int4_cvt_fns, int4_cmp_fns)
static Datum
leftmostvalue_int8(void)
@@ -243,7 +357,32 @@ leftmostvalue_int8(void)
return Int64GetDatum(PG_INT64_MIN);
}
-GIN_SUPPORT(int8, false, leftmostvalue_int8, btint8cmp)
+static Datum
+cvt_int2_int8(Datum input)
+{
+ int16 val = DatumGetInt16(input);
+
+ return Int64GetDatum((int64) val);
+}
+
+static Datum
+cvt_int4_int8(Datum input)
+{
+ int32 val = DatumGetInt32(input);
+
+ return Int64GetDatum((int64) val);
+}
+
+static const bool int8_rhs_is_varlena[] =
+{false, false, false};
+
+static const btree_gin_convert_function int8_cvt_fns[] =
+{NULL, cvt_int2_int8, cvt_int4_int8};
+
+static const PGFunction int8_cmp_fns[] =
+{btint8cmp, btint28cmp, btint48cmp};
+
+GIN_SUPPORT(int8, leftmostvalue_int8, int8_rhs_is_varlena, int8_cvt_fns, int8_cmp_fns)
static Datum
leftmostvalue_float4(void)
@@ -251,7 +390,13 @@ leftmostvalue_float4(void)
return Float4GetDatum(-get_float4_infinity());
}
-GIN_SUPPORT(float4, false, leftmostvalue_float4, btfloat4cmp)
+static const bool float4_rhs_is_varlena[] =
+{false};
+
+static const PGFunction float4_cmp_fns[] =
+{btfloat4cmp};
+
+GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, NULL, float4_cmp_fns)
static Datum
leftmostvalue_float8(void)
@@ -259,7 +404,13 @@ leftmostvalue_float8(void)
return Float8GetDatum(-get_float8_infinity());
}
-GIN_SUPPORT(float8, false, leftmostvalue_float8, btfloat8cmp)
+static const bool float8_rhs_is_varlena[] =
+{false};
+
+static const PGFunction float8_cmp_fns[] =
+{btfloat8cmp};
+
+GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, NULL, float8_cmp_fns)
static Datum
leftmostvalue_money(void)
@@ -267,7 +418,13 @@ leftmostvalue_money(void)
return Int64GetDatum(PG_INT64_MIN);
}
-GIN_SUPPORT(money, false, leftmostvalue_money, cash_cmp)
+static const bool money_rhs_is_varlena[] =
+{false};
+
+static const PGFunction money_cmp_fns[] =
+{cash_cmp};
+
+GIN_SUPPORT(money, leftmostvalue_money, money_rhs_is_varlena, NULL, money_cmp_fns)
static Datum
leftmostvalue_oid(void)
@@ -275,7 +432,13 @@ leftmostvalue_oid(void)
return ObjectIdGetDatum(0);
}
-GIN_SUPPORT(oid, false, leftmostvalue_oid, btoidcmp)
+static const bool oid_rhs_is_varlena[] =
+{false};
+
+static const PGFunction oid_cmp_fns[] =
+{btoidcmp};
+
+GIN_SUPPORT(oid, leftmostvalue_oid, oid_rhs_is_varlena, NULL, oid_cmp_fns)
static Datum
leftmostvalue_timestamp(void)
@@ -283,9 +446,21 @@ leftmostvalue_timestamp(void)
return TimestampGetDatum(DT_NOBEGIN);
}
-GIN_SUPPORT(timestamp, false, leftmostvalue_timestamp, timestamp_cmp)
+static const bool timestamp_rhs_is_varlena[] =
+{false};
+
+static const PGFunction timestamp_cmp_fns[] =
+{timestamp_cmp};
+
+GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, NULL, timestamp_cmp_fns)
-GIN_SUPPORT(timestamptz, false, leftmostvalue_timestamp, timestamp_cmp)
+static const bool timestamptz_rhs_is_varlena[] =
+{false};
+
+static const PGFunction timestamptz_cmp_fns[] =
+{timestamp_cmp};
+
+GIN_SUPPORT(timestamptz, leftmostvalue_timestamp, timestamptz_rhs_is_varlena, NULL, timestamptz_cmp_fns)
static Datum
leftmostvalue_time(void)
@@ -293,7 +468,13 @@ leftmostvalue_time(void)
return TimeADTGetDatum(0);
}
-GIN_SUPPORT(time, false, leftmostvalue_time, time_cmp)
+static const bool time_rhs_is_varlena[] =
+{false};
+
+static const PGFunction time_cmp_fns[] =
+{time_cmp};
+
+GIN_SUPPORT(time, leftmostvalue_time, time_rhs_is_varlena, NULL, time_cmp_fns)
static Datum
leftmostvalue_timetz(void)
@@ -306,7 +487,13 @@ leftmostvalue_timetz(void)
return TimeTzADTPGetDatum(v);
}
-GIN_SUPPORT(timetz, false, leftmostvalue_timetz, timetz_cmp)
+static const bool timetz_rhs_is_varlena[] =
+{false};
+
+static const PGFunction timetz_cmp_fns[] =
+{timetz_cmp};
+
+GIN_SUPPORT(timetz, leftmostvalue_timetz, timetz_rhs_is_varlena, NULL, timetz_cmp_fns)
static Datum
leftmostvalue_date(void)
@@ -314,7 +501,13 @@ leftmostvalue_date(void)
return DateADTGetDatum(DATEVAL_NOBEGIN);
}
-GIN_SUPPORT(date, false, leftmostvalue_date, date_cmp)
+static const bool date_rhs_is_varlena[] =
+{false};
+
+static const PGFunction date_cmp_fns[] =
+{date_cmp};
+
+GIN_SUPPORT(date, leftmostvalue_date, date_rhs_is_varlena, NULL, date_cmp_fns)
static Datum
leftmostvalue_interval(void)
@@ -326,7 +519,13 @@ leftmostvalue_interval(void)
return IntervalPGetDatum(v);
}
-GIN_SUPPORT(interval, false, leftmostvalue_interval, interval_cmp)
+static const bool interval_rhs_is_varlena[] =
+{false};
+
+static const PGFunction interval_cmp_fns[] =
+{interval_cmp};
+
+GIN_SUPPORT(interval, leftmostvalue_interval, interval_rhs_is_varlena, NULL, interval_cmp_fns)
static Datum
leftmostvalue_macaddr(void)
@@ -336,7 +535,13 @@ leftmostvalue_macaddr(void)
return MacaddrPGetDatum(v);
}
-GIN_SUPPORT(macaddr, false, leftmostvalue_macaddr, macaddr_cmp)
+static const bool macaddr_rhs_is_varlena[] =
+{false};
+
+static const PGFunction macaddr_cmp_fns[] =
+{macaddr_cmp};
+
+GIN_SUPPORT(macaddr, leftmostvalue_macaddr, macaddr_rhs_is_varlena, NULL, macaddr_cmp_fns)
static Datum
leftmostvalue_macaddr8(void)
@@ -346,7 +551,13 @@ leftmostvalue_macaddr8(void)
return Macaddr8PGetDatum(v);
}
-GIN_SUPPORT(macaddr8, false, leftmostvalue_macaddr8, macaddr8_cmp)
+static const bool macaddr8_rhs_is_varlena[] =
+{false};
+
+static const PGFunction macaddr8_cmp_fns[] =
+{macaddr8_cmp};
+
+GIN_SUPPORT(macaddr8, leftmostvalue_macaddr8, macaddr8_rhs_is_varlena, NULL, macaddr8_cmp_fns)
static Datum
leftmostvalue_inet(void)
@@ -354,9 +565,21 @@ leftmostvalue_inet(void)
return DirectFunctionCall1(inet_in, CStringGetDatum("0.0.0.0/0"));
}
-GIN_SUPPORT(inet, true, leftmostvalue_inet, network_cmp)
+static const bool inet_rhs_is_varlena[] =
+{true};
+
+static const PGFunction inet_cmp_fns[] =
+{network_cmp};
+
+GIN_SUPPORT(inet, leftmostvalue_inet, inet_rhs_is_varlena, NULL, inet_cmp_fns)
+
+static const bool cidr_rhs_is_varlena[] =
+{true};
-GIN_SUPPORT(cidr, true, leftmostvalue_inet, network_cmp)
+static const PGFunction cidr_cmp_fns[] =
+{network_cmp};
+
+GIN_SUPPORT(cidr, leftmostvalue_inet, cidr_rhs_is_varlena, NULL, cidr_cmp_fns)
static Datum
leftmostvalue_text(void)
@@ -364,9 +587,21 @@ leftmostvalue_text(void)
return PointerGetDatum(cstring_to_text_with_len("", 0));
}
-GIN_SUPPORT(text, true, leftmostvalue_text, bttextcmp)
+static const bool text_rhs_is_varlena[] =
+{true};
+
+static const PGFunction text_cmp_fns[] =
+{bttextcmp};
+
+GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, NULL, text_cmp_fns)
-GIN_SUPPORT(bpchar, true, leftmostvalue_text, bpcharcmp)
+static const bool bpchar_rhs_is_varlena[] =
+{true};
+
+static const PGFunction bpchar_cmp_fns[] =
+{bpcharcmp};
+
+GIN_SUPPORT(bpchar, leftmostvalue_text, bpchar_rhs_is_varlena, NULL, bpchar_cmp_fns)
static Datum
leftmostvalue_char(void)
@@ -374,9 +609,21 @@ leftmostvalue_char(void)
return CharGetDatum(0);
}
-GIN_SUPPORT(char, false, leftmostvalue_char, btcharcmp)
+static const bool char_rhs_is_varlena[] =
+{false};
+
+static const PGFunction char_cmp_fns[] =
+{btcharcmp};
+
+GIN_SUPPORT(char, leftmostvalue_char, char_rhs_is_varlena, NULL, char_cmp_fns)
-GIN_SUPPORT(bytea, true, leftmostvalue_text, byteacmp)
+static const bool bytea_rhs_is_varlena[] =
+{true};
+
+static const PGFunction bytea_cmp_fns[] =
+{byteacmp};
+
+GIN_SUPPORT(bytea, leftmostvalue_text, bytea_rhs_is_varlena, NULL, bytea_cmp_fns)
static Datum
leftmostvalue_bit(void)
@@ -387,7 +634,13 @@ leftmostvalue_bit(void)
Int32GetDatum(-1));
}
-GIN_SUPPORT(bit, true, leftmostvalue_bit, bitcmp)
+static const bool bit_rhs_is_varlena[] =
+{true};
+
+static const PGFunction bit_cmp_fns[] =
+{bitcmp};
+
+GIN_SUPPORT(bit, leftmostvalue_bit, bit_rhs_is_varlena, NULL, bit_cmp_fns)
static Datum
leftmostvalue_varbit(void)
@@ -398,7 +651,13 @@ leftmostvalue_varbit(void)
Int32GetDatum(-1));
}
-GIN_SUPPORT(varbit, true, leftmostvalue_varbit, bitcmp)
+static const bool varbit_rhs_is_varlena[] =
+{true};
+
+static const PGFunction varbit_cmp_fns[] =
+{bitcmp};
+
+GIN_SUPPORT(varbit, leftmostvalue_varbit, varbit_rhs_is_varlena, NULL, varbit_cmp_fns)
/*
* Numeric type hasn't a real left-most value, so we use PointerGetDatum(NULL)
@@ -443,7 +702,13 @@ leftmostvalue_numeric(void)
return PointerGetDatum(NULL);
}
-GIN_SUPPORT(numeric, true, leftmostvalue_numeric, gin_numeric_cmp)
+static const bool numeric_rhs_is_varlena[] =
+{true};
+
+static const PGFunction numeric_cmp_fns[] =
+{gin_numeric_cmp};
+
+GIN_SUPPORT(numeric, leftmostvalue_numeric, numeric_rhs_is_varlena, NULL, numeric_cmp_fns)
/*
* Use a similar trick to that used for numeric for enums, since we don't
@@ -492,7 +757,13 @@ leftmostvalue_enum(void)
return ObjectIdGetDatum(InvalidOid);
}
-GIN_SUPPORT(anyenum, false, leftmostvalue_enum, gin_enum_cmp)
+static const bool enum_rhs_is_varlena[] =
+{false};
+
+static const PGFunction enum_cmp_fns[] =
+{gin_enum_cmp};
+
+GIN_SUPPORT(anyenum, leftmostvalue_enum, enum_rhs_is_varlena, NULL, enum_cmp_fns)
static Datum
leftmostvalue_uuid(void)
@@ -506,7 +777,13 @@ leftmostvalue_uuid(void)
return UUIDPGetDatum(retval);
}
-GIN_SUPPORT(uuid, false, leftmostvalue_uuid, uuid_cmp)
+static const bool uuid_rhs_is_varlena[] =
+{false};
+
+static const PGFunction uuid_cmp_fns[] =
+{uuid_cmp};
+
+GIN_SUPPORT(uuid, leftmostvalue_uuid, uuid_rhs_is_varlena, NULL, uuid_cmp_fns)
static Datum
leftmostvalue_name(void)
@@ -516,7 +793,13 @@ leftmostvalue_name(void)
return NameGetDatum(result);
}
-GIN_SUPPORT(name, false, leftmostvalue_name, btnamecmp)
+static const bool name_rhs_is_varlena[] =
+{false};
+
+static const PGFunction name_cmp_fns[] =
+{btnamecmp};
+
+GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, NULL, name_cmp_fns)
static Datum
leftmostvalue_bool(void)
@@ -524,4 +807,10 @@ leftmostvalue_bool(void)
return BoolGetDatum(false);
}
-GIN_SUPPORT(bool, false, leftmostvalue_bool, btboolcmp)
+static const bool bool_rhs_is_varlena[] =
+{false};
+
+static const PGFunction bool_cmp_fns[] =
+{btboolcmp};
+
+GIN_SUPPORT(bool, leftmostvalue_bool, bool_rhs_is_varlena, NULL, bool_cmp_fns)
diff --git a/contrib/btree_gin/btree_gin.control b/contrib/btree_gin/btree_gin.control
index 67d0c997d8..0c77c81727 100644
--- a/contrib/btree_gin/btree_gin.control
+++ b/contrib/btree_gin/btree_gin.control
@@ -1,6 +1,6 @@
# btree_gin extension
comment = 'support for indexing common datatypes in GIN'
-default_version = '1.3'
+default_version = '1.4'
module_pathname = '$libdir/btree_gin'
relocatable = true
trusted = true
diff --git a/contrib/btree_gin/expected/int2.out b/contrib/btree_gin/expected/int2.out
index 20d66a1b05..855292d53e 100644
--- a/contrib/btree_gin/expected/int2.out
+++ b/contrib/btree_gin/expected/int2.out
@@ -42,3 +42,187 @@ SELECT * FROM test_int2 WHERE i>1::int2 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_int2 WHERE i<1::int4 ORDER BY i;
+ QUERY PLAN
+-------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int2
+ Recheck Cond: (i < 1)
+ -> Bitmap Index Scan on idx_int2
+ Index Cond: (i < 1)
+(6 rows)
+
+SELECT * FROM test_int2 WHERE i<1::int4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int2 WHERE i<=1::int4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int2 WHERE i=1::int4 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int2 WHERE i>=1::int4 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int2 WHERE i>1::int4 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_int2 WHERE i<1::int8 ORDER BY i;
+ QUERY PLAN
+---------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int2
+ Recheck Cond: (i < '1'::bigint)
+ -> Bitmap Index Scan on idx_int2
+ Index Cond: (i < '1'::bigint)
+(6 rows)
+
+SELECT * FROM test_int2 WHERE i<1::int8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int2 WHERE i<=1::int8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int2 WHERE i=1::int8 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int2 WHERE i>=1::int8 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int2 WHERE i>1::int8 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_int2 VALUES ((-32768)::int2),(32767);
+SELECT * FROM test_int2 WHERE i<(-32769)::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i<=(-32769)::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i=(-32769)::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i>=(-32769)::int4 ORDER BY i;
+ i
+--------
+ -32768
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 32767
+(8 rows)
+
+SELECT * FROM test_int2 WHERE i>(-32769)::int4 ORDER BY i;
+ i
+--------
+ -32768
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 32767
+(8 rows)
+
+SELECT * FROM test_int2 WHERE i<32768::int4 ORDER BY i;
+ i
+--------
+ -32768
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 32767
+(8 rows)
+
+SELECT * FROM test_int2 WHERE i<=32768::int4 ORDER BY i;
+ i
+--------
+ -32768
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 32767
+(8 rows)
+
+SELECT * FROM test_int2 WHERE i=32768::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i>=32768::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i>32768::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
diff --git a/contrib/btree_gin/expected/int4.out b/contrib/btree_gin/expected/int4.out
index 0f0122c6f5..e62791e18b 100644
--- a/contrib/btree_gin/expected/int4.out
+++ b/contrib/btree_gin/expected/int4.out
@@ -42,3 +42,103 @@ SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_int4 WHERE i<1::int2 ORDER BY i;
+ QUERY PLAN
+-----------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int4
+ Recheck Cond: (i < '1'::smallint)
+ -> Bitmap Index Scan on idx_int4
+ Index Cond: (i < '1'::smallint)
+(6 rows)
+
+SELECT * FROM test_int4 WHERE i<1::int2 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int4 WHERE i<=1::int2 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int4 WHERE i=1::int2 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int4 WHERE i>=1::int2 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int4 WHERE i>1::int2 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_int4 WHERE i<1::int8 ORDER BY i;
+ QUERY PLAN
+---------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int4
+ Recheck Cond: (i < '1'::bigint)
+ -> Bitmap Index Scan on idx_int4
+ Index Cond: (i < '1'::bigint)
+(6 rows)
+
+SELECT * FROM test_int4 WHERE i<1::int8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int4 WHERE i<=1::int8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int4 WHERE i=1::int8 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int4 WHERE i>=1::int8 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int4 WHERE i>1::int8 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
diff --git a/contrib/btree_gin/expected/int8.out b/contrib/btree_gin/expected/int8.out
index 307e19e7a0..c9aceb9d35 100644
--- a/contrib/btree_gin/expected/int8.out
+++ b/contrib/btree_gin/expected/int8.out
@@ -42,3 +42,103 @@ SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_int8 WHERE i<1::int2 ORDER BY i;
+ QUERY PLAN
+-----------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int8
+ Recheck Cond: (i < '1'::smallint)
+ -> Bitmap Index Scan on idx_int8
+ Index Cond: (i < '1'::smallint)
+(6 rows)
+
+SELECT * FROM test_int8 WHERE i<1::int2 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int8 WHERE i<=1::int2 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int8 WHERE i=1::int2 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int8 WHERE i>=1::int2 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int8 WHERE i>1::int2 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_int8 WHERE i<1::int4 ORDER BY i;
+ QUERY PLAN
+-------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int8
+ Recheck Cond: (i < 1)
+ -> Bitmap Index Scan on idx_int8
+ Index Cond: (i < 1)
+(6 rows)
+
+SELECT * FROM test_int8 WHERE i<1::int4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int8 WHERE i<=1::int4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int8 WHERE i=1::int4 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int8 WHERE i>=1::int4 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int8 WHERE i>1::int4 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
diff --git a/contrib/btree_gin/meson.build b/contrib/btree_gin/meson.build
index b2749f6e66..ece0a71697 100644
--- a/contrib/btree_gin/meson.build
+++ b/contrib/btree_gin/meson.build
@@ -22,6 +22,7 @@ install_data(
'btree_gin--1.0--1.1.sql',
'btree_gin--1.1--1.2.sql',
'btree_gin--1.2--1.3.sql',
+ 'btree_gin--1.3--1.4.sql',
kwargs: contrib_data_args,
)
diff --git a/contrib/btree_gin/sql/int2.sql b/contrib/btree_gin/sql/int2.sql
index f06f11702f..660785ad55 100644
--- a/contrib/btree_gin/sql/int2.sql
+++ b/contrib/btree_gin/sql/int2.sql
@@ -13,3 +13,37 @@ SELECT * FROM test_int2 WHERE i<=1::int2 ORDER BY i;
SELECT * FROM test_int2 WHERE i=1::int2 ORDER BY i;
SELECT * FROM test_int2 WHERE i>=1::int2 ORDER BY i;
SELECT * FROM test_int2 WHERE i>1::int2 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int2 WHERE i<1::int4 ORDER BY i;
+
+SELECT * FROM test_int2 WHERE i<1::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i<=1::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i=1::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>=1::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>1::int4 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int2 WHERE i<1::int8 ORDER BY i;
+
+SELECT * FROM test_int2 WHERE i<1::int8 ORDER BY i;
+SELECT * FROM test_int2 WHERE i<=1::int8 ORDER BY i;
+SELECT * FROM test_int2 WHERE i=1::int8 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>=1::int8 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>1::int8 ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_int2 VALUES ((-32768)::int2),(32767);
+
+SELECT * FROM test_int2 WHERE i<(-32769)::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i<=(-32769)::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i=(-32769)::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>=(-32769)::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>(-32769)::int4 ORDER BY i;
+
+SELECT * FROM test_int2 WHERE i<32768::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i<=32768::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i=32768::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>=32768::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>32768::int4 ORDER BY i;
diff --git a/contrib/btree_gin/sql/int4.sql b/contrib/btree_gin/sql/int4.sql
index 6499c29630..9a45530b63 100644
--- a/contrib/btree_gin/sql/int4.sql
+++ b/contrib/btree_gin/sql/int4.sql
@@ -13,3 +13,21 @@ SELECT * FROM test_int4 WHERE i<=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i>=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int4 WHERE i<1::int2 ORDER BY i;
+
+SELECT * FROM test_int4 WHERE i<1::int2 ORDER BY i;
+SELECT * FROM test_int4 WHERE i<=1::int2 ORDER BY i;
+SELECT * FROM test_int4 WHERE i=1::int2 ORDER BY i;
+SELECT * FROM test_int4 WHERE i>=1::int2 ORDER BY i;
+SELECT * FROM test_int4 WHERE i>1::int2 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int4 WHERE i<1::int8 ORDER BY i;
+
+SELECT * FROM test_int4 WHERE i<1::int8 ORDER BY i;
+SELECT * FROM test_int4 WHERE i<=1::int8 ORDER BY i;
+SELECT * FROM test_int4 WHERE i=1::int8 ORDER BY i;
+SELECT * FROM test_int4 WHERE i>=1::int8 ORDER BY i;
+SELECT * FROM test_int4 WHERE i>1::int8 ORDER BY i;
diff --git a/contrib/btree_gin/sql/int8.sql b/contrib/btree_gin/sql/int8.sql
index 4d9c287181..b31f27c69b 100644
--- a/contrib/btree_gin/sql/int8.sql
+++ b/contrib/btree_gin/sql/int8.sql
@@ -13,3 +13,21 @@ SELECT * FROM test_int8 WHERE i<=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i>=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int8 WHERE i<1::int2 ORDER BY i;
+
+SELECT * FROM test_int8 WHERE i<1::int2 ORDER BY i;
+SELECT * FROM test_int8 WHERE i<=1::int2 ORDER BY i;
+SELECT * FROM test_int8 WHERE i=1::int2 ORDER BY i;
+SELECT * FROM test_int8 WHERE i>=1::int2 ORDER BY i;
+SELECT * FROM test_int8 WHERE i>1::int2 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int8 WHERE i<1::int4 ORDER BY i;
+
+SELECT * FROM test_int8 WHERE i<1::int4 ORDER BY i;
+SELECT * FROM test_int8 WHERE i<=1::int4 ORDER BY i;
+SELECT * FROM test_int8 WHERE i=1::int4 ORDER BY i;
+SELECT * FROM test_int8 WHERE i>=1::int4 ORDER BY i;
+SELECT * FROM test_int8 WHERE i>1::int4 ORDER BY i;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e3daab0905..4e5548d734 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3379,6 +3379,7 @@ bloom_filter
boolKEY
brin_column_state
brin_serialize_callback_type
+btree_gin_convert_function
btree_gin_leftmost_function
bytea
cached_re_str
--
2.43.5
From 94469b323f5119c1815862eb0ca26d2c0335952b Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 7 Feb 2025 17:14:17 -0500
Subject: [PATCH v2 4/6] Add cross-type comparisons for float types.
---
contrib/btree_gin/btree_gin--1.3--1.4.sql | 20 ++
contrib/btree_gin/btree_gin.c | 44 ++++-
contrib/btree_gin/expected/float4.out | 227 ++++++++++++++++++++++
contrib/btree_gin/expected/float8.out | 50 +++++
contrib/btree_gin/sql/float4.sql | 37 ++++
contrib/btree_gin/sql/float8.sql | 9 +
6 files changed, 381 insertions(+), 6 deletions(-)
diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql
index 71e577de2d..88ed5d5b3e 100644
--- a/contrib/btree_gin/btree_gin--1.3--1.4.sql
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -61,3 +61,23 @@ ADD
OPERATOR 20 >= (int8, int4),
OPERATOR 21 > (int8, int4)
;
+
+ALTER OPERATOR FAMILY float4_ops USING gin
+ADD
+ -- Code 1: RHS is float8
+ OPERATOR 9 < (float4, float8),
+ OPERATOR 10 <= (float4, float8),
+ OPERATOR 11 = (float4, float8),
+ OPERATOR 12 >= (float4, float8),
+ OPERATOR 13 > (float4, float8)
+;
+
+ALTER OPERATOR FAMILY float8_ops USING gin
+ADD
+ -- Code 1: RHS is float4
+ OPERATOR 9 < (float8, float4),
+ OPERATOR 10 <= (float8, float4),
+ OPERATOR 11 = (float8, float4),
+ OPERATOR 12 >= (float8, float4),
+ OPERATOR 13 > (float8, float4)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 558675cf76..1070b3ac2f 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -390,13 +390,34 @@ leftmostvalue_float4(void)
return Float4GetDatum(-get_float4_infinity());
}
+static Datum
+cvt_float8_float4(Datum input)
+{
+ float8 val = DatumGetFloat8(input);
+ float4 result;
+
+ /*
+ * Assume that ordinary C conversion will produce a usable result.
+ * (Compare dtof(), which raises error conditions that we don't need.)
+ * Note that for inputs that aren't exactly representable as float4, it
+ * doesn't matter whether the conversion rounds up or down. That might
+ * cause us to scan a few index entries that we'll reject as not matching,
+ * but we won't miss any that should match.
+ */
+ result = (float4) val;
+ return Float4GetDatum(result);
+}
+
static const bool float4_rhs_is_varlena[] =
-{false};
+{false, false};
+
+static const btree_gin_convert_function float4_cvt_fns[] =
+{NULL, cvt_float8_float4};
static const PGFunction float4_cmp_fns[] =
-{btfloat4cmp};
+{btfloat4cmp, btfloat84cmp};
-GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, NULL, float4_cmp_fns)
+GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, float4_cvt_fns, float4_cmp_fns)
static Datum
leftmostvalue_float8(void)
@@ -404,13 +425,24 @@ leftmostvalue_float8(void)
return Float8GetDatum(-get_float8_infinity());
}
+static Datum
+cvt_float4_float8(Datum input)
+{
+ float4 val = DatumGetFloat4(input);
+
+ return Float8GetDatum((float8) val);
+}
+
static const bool float8_rhs_is_varlena[] =
-{false};
+{false, false};
+
+static const btree_gin_convert_function float8_cvt_fns[] =
+{NULL, cvt_float4_float8};
static const PGFunction float8_cmp_fns[] =
-{btfloat8cmp};
+{btfloat8cmp, btfloat48cmp};
-GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, NULL, float8_cmp_fns)
+GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, float8_cvt_fns, float8_cmp_fns)
static Datum
leftmostvalue_money(void)
diff --git a/contrib/btree_gin/expected/float4.out b/contrib/btree_gin/expected/float4.out
index 7b9134fcd4..02c88e4007 100644
--- a/contrib/btree_gin/expected/float4.out
+++ b/contrib/btree_gin/expected/float4.out
@@ -42,3 +42,230 @@ SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+ QUERY PLAN
+-------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_float4
+ Recheck Cond: (i < '1'::double precision)
+ -> Bitmap Index Scan on idx_float4
+ Index Cond: (i < '1'::double precision)
+(6 rows)
+
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_float4 WHERE i<=1::float8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_float4 WHERE i=1::float8 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>=1::float8 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_float4 WHERE i>1::float8 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_float4 VALUES ('NaN'), ('Inf'), ('-Inf');
+SELECT * FROM test_float4 WHERE i<'-Inf'::float8 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_float4 WHERE i<='-Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i='-Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='-Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+ NaN
+(9 rows)
+
+SELECT * FROM test_float4 WHERE i>'-Inf'::float8 ORDER BY i;
+ i
+----------
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+ NaN
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i<'Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i<='Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i='Inf'::float8 ORDER BY i;
+ i
+----------
+ Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='Inf'::float8 ORDER BY i;
+ i
+----------
+ Infinity
+ NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i>'Inf'::float8 ORDER BY i;
+ i
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i<'1e300'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i<='1e300'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i='1e300'::float8 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_float4 WHERE i>='1e300'::float8 ORDER BY i;
+ i
+----------
+ Infinity
+ NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i>'1e300'::float8 ORDER BY i;
+ i
+----------
+ Infinity
+ NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i<'NaN'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i<='NaN'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+ NaN
+(9 rows)
+
+SELECT * FROM test_float4 WHERE i='NaN'::float8 ORDER BY i;
+ i
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='NaN'::float8 ORDER BY i;
+ i
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>'NaN'::float8 ORDER BY i;
+ i
+---
+(0 rows)
+
diff --git a/contrib/btree_gin/expected/float8.out b/contrib/btree_gin/expected/float8.out
index a41d4f9f6b..b2877dfa3c 100644
--- a/contrib/btree_gin/expected/float8.out
+++ b/contrib/btree_gin/expected/float8.out
@@ -42,3 +42,53 @@ SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+ QUERY PLAN
+---------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_float8
+ Recheck Cond: (i < '1'::real)
+ -> Bitmap Index Scan on idx_float8
+ Index Cond: (i < '1'::real)
+(6 rows)
+
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_float8 WHERE i<=1::float4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_float8 WHERE i=1::float4 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_float8 WHERE i>=1::float4 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_float8 WHERE i>1::float4 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
diff --git a/contrib/btree_gin/sql/float4.sql b/contrib/btree_gin/sql/float4.sql
index 759778ad3c..e850e1a058 100644
--- a/contrib/btree_gin/sql/float4.sql
+++ b/contrib/btree_gin/sql/float4.sql
@@ -13,3 +13,40 @@ SELECT * FROM test_float4 WHERE i<=1::float4 ORDER BY i;
SELECT * FROM test_float4 WHERE i=1::float4 ORDER BY i;
SELECT * FROM test_float4 WHERE i>=1::float4 ORDER BY i;
SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>1::float8 ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_float4 VALUES ('NaN'), ('Inf'), ('-Inf');
+
+SELECT * FROM test_float4 WHERE i<'-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'-Inf'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'Inf'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'1e300'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'NaN'::float8 ORDER BY i;
diff --git a/contrib/btree_gin/sql/float8.sql b/contrib/btree_gin/sql/float8.sql
index b046ac4e6c..5f39314708 100644
--- a/contrib/btree_gin/sql/float8.sql
+++ b/contrib/btree_gin/sql/float8.sql
@@ -13,3 +13,12 @@ SELECT * FROM test_float8 WHERE i<=1::float8 ORDER BY i;
SELECT * FROM test_float8 WHERE i=1::float8 ORDER BY i;
SELECT * FROM test_float8 WHERE i>=1::float8 ORDER BY i;
SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i<=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i>=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i>1::float4 ORDER BY i;
--
2.43.5
From de09d6caf57f048be87ad0eab5c8a127f7339a8c Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 7 Feb 2025 17:15:07 -0500
Subject: [PATCH v2 5/6] Add cross-type comparisons for string types.
(Only these two cases appear in the catalogs.)
---
contrib/btree_gin/btree_gin--1.3--1.4.sql | 20 ++++++++
contrib/btree_gin/btree_gin.c | 49 ++++++++++++++++---
contrib/btree_gin/expected/name.out | 59 +++++++++++++++++++++++
contrib/btree_gin/expected/text.out | 50 +++++++++++++++++++
contrib/btree_gin/sql/name.sql | 11 +++++
contrib/btree_gin/sql/text.sql | 9 ++++
6 files changed, 192 insertions(+), 6 deletions(-)
diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql
index 88ed5d5b3e..13c84ad667 100644
--- a/contrib/btree_gin/btree_gin--1.3--1.4.sql
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -81,3 +81,23 @@ ADD
OPERATOR 12 >= (float8, float4),
OPERATOR 13 > (float8, float4)
;
+
+ALTER OPERATOR FAMILY text_ops USING gin
+ADD
+ -- Code 1: RHS is name
+ OPERATOR 9 < (text, name),
+ OPERATOR 10 <= (text, name),
+ OPERATOR 11 = (text, name),
+ OPERATOR 12 >= (text, name),
+ OPERATOR 13 > (text, name)
+;
+
+ALTER OPERATOR FAMILY name_ops USING gin
+ADD
+ -- Code 1: RHS is text
+ OPERATOR 9 < (name, text),
+ OPERATOR 10 <= (name, text),
+ OPERATOR 11 = (name, text),
+ OPERATOR 12 >= (name, text),
+ OPERATOR 13 > (name, text)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 1070b3ac2f..5b2d8795ad 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -6,6 +6,7 @@
#include <limits.h>
#include "access/stratnum.h"
+#include "mb/pg_wchar.h"
#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/float.h"
@@ -13,6 +14,7 @@
#include "utils/numeric.h"
#include "utils/timestamp.h"
#include "utils/uuid.h"
+#include "varatt.h"
PG_MODULE_MAGIC;
@@ -619,13 +621,24 @@ leftmostvalue_text(void)
return PointerGetDatum(cstring_to_text_with_len("", 0));
}
+static Datum
+cvt_name_text(Datum input)
+{
+ Name val = DatumGetName(input);
+
+ return PointerGetDatum(cstring_to_text(NameStr(*val)));
+}
+
static const bool text_rhs_is_varlena[] =
-{true};
+{true, false};
+
+static const btree_gin_convert_function text_cvt_fns[] =
+{NULL, cvt_name_text};
static const PGFunction text_cmp_fns[] =
-{bttextcmp};
+{bttextcmp, btnametextcmp};
-GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, NULL, text_cmp_fns)
+GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, text_cvt_fns, text_cmp_fns)
static const bool bpchar_rhs_is_varlena[] =
{true};
@@ -825,13 +838,37 @@ leftmostvalue_name(void)
return NameGetDatum(result);
}
+static Datum
+cvt_text_name(Datum input)
+{
+ text *val = DatumGetTextPP(input);
+ NameData *result = (NameData *) palloc0(NAMEDATALEN);
+ int len = VARSIZE_ANY_EXHDR(val);
+
+ /*
+ * Truncate oversize input. We're assuming this will produce a result
+ * considered less than the original. That could be a bad assumption in
+ * some collations, but fortunately an index on "name" is generally going
+ * to use C collation.
+ */
+ if (len >= NAMEDATALEN)
+ len = pg_mbcliplen(VARDATA_ANY(val), len, NAMEDATALEN - 1);
+
+ memcpy(NameStr(*result), VARDATA_ANY(val), len);
+
+ return NameGetDatum(result);
+}
+
static const bool name_rhs_is_varlena[] =
-{false};
+{false, true};
+
+static const btree_gin_convert_function name_cvt_fns[] =
+{NULL, cvt_text_name};
static const PGFunction name_cmp_fns[] =
-{btnamecmp};
+{btnamecmp, bttextnamecmp};
-GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, NULL, name_cmp_fns)
+GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, name_cvt_fns, name_cmp_fns)
static Datum
leftmostvalue_bool(void)
diff --git a/contrib/btree_gin/expected/name.out b/contrib/btree_gin/expected/name.out
index 174de6576f..3a30f62519 100644
--- a/contrib/btree_gin/expected/name.out
+++ b/contrib/btree_gin/expected/name.out
@@ -95,3 +95,62 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i;
Index Cond: (i > 'abc'::name)
(6 rows)
+explain (costs off)
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+ QUERY PLAN
+---------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_name
+ Recheck Cond: (i < 'abc'::text)
+ -> Bitmap Index Scan on idx_name
+ Index Cond: (i < 'abc'::text)
+(6 rows)
+
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+(3 rows)
+
+SELECT * FROM test_name WHERE i<='abc'::text ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
+SELECT * FROM test_name WHERE i='abc'::text ORDER BY i;
+ i
+-----
+ abc
+(1 row)
+
+SELECT * FROM test_name WHERE i>='abc'::text ORDER BY i;
+ i
+-----
+ abc
+ axy
+ xyz
+(3 rows)
+
+SELECT * FROM test_name WHERE i>'abc'::text ORDER BY i;
+ i
+-----
+ axy
+ xyz
+(2 rows)
+
+SELECT * FROM test_name WHERE i<=repeat('abc', 100) ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
diff --git a/contrib/btree_gin/expected/text.out b/contrib/btree_gin/expected/text.out
index 3e31ad744d..7f52f3db7b 100644
--- a/contrib/btree_gin/expected/text.out
+++ b/contrib/btree_gin/expected/text.out
@@ -42,3 +42,53 @@ SELECT * FROM test_text WHERE i>'abc' ORDER BY i;
xyz
(2 rows)
+explain (costs off)
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_text
+ Recheck Cond: (i < 'abc'::name COLLATE "default")
+ -> Bitmap Index Scan on idx_text
+ Index Cond: (i < 'abc'::name COLLATE "default")
+(6 rows)
+
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+(3 rows)
+
+SELECT * FROM test_text WHERE i<='abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
+SELECT * FROM test_text WHERE i='abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ abc
+(1 row)
+
+SELECT * FROM test_text WHERE i>='abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ abc
+ axy
+ xyz
+(3 rows)
+
+SELECT * FROM test_text WHERE i>'abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ axy
+ xyz
+(2 rows)
+
diff --git a/contrib/btree_gin/sql/name.sql b/contrib/btree_gin/sql/name.sql
index c11580cdf9..551d928940 100644
--- a/contrib/btree_gin/sql/name.sql
+++ b/contrib/btree_gin/sql/name.sql
@@ -19,3 +19,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i<='abc' ORDER BY i;
EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i='abc' ORDER BY i;
EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>='abc' ORDER BY i;
EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i<='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i>='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i>'abc'::text ORDER BY i;
+
+SELECT * FROM test_name WHERE i<=repeat('abc', 100) ORDER BY i;
diff --git a/contrib/btree_gin/sql/text.sql b/contrib/btree_gin/sql/text.sql
index d5b3b39898..978b21376f 100644
--- a/contrib/btree_gin/sql/text.sql
+++ b/contrib/btree_gin/sql/text.sql
@@ -13,3 +13,12 @@ SELECT * FROM test_text WHERE i<='abc' ORDER BY i;
SELECT * FROM test_text WHERE i='abc' ORDER BY i;
SELECT * FROM test_text WHERE i>='abc' ORDER BY i;
SELECT * FROM test_text WHERE i>'abc' ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i<='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i>='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i>'abc'::name COLLATE "default" ORDER BY i;
--
2.43.5
From 17d34ac9de042568b4d60301f4f16c4c380d688b Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 7 Feb 2025 17:34:54 -0500
Subject: [PATCH v2 6/6] Add cross-type comparisons for datetime types.
Discussion: https://postgr.es/m/262624.1738460652@sss.pgh.pa.us
---
contrib/btree_gin/btree_gin--1.3--1.4.sql | 48 ++++
contrib/btree_gin/btree_gin.c | 99 ++++++-
contrib/btree_gin/expected/date.out | 270 +++++++++++++++++++
contrib/btree_gin/expected/timestamp.out | 300 ++++++++++++++++++++-
contrib/btree_gin/expected/timestamptz.out | 111 +++++++-
contrib/btree_gin/sql/date.sql | 46 ++++
contrib/btree_gin/sql/timestamp.sql | 54 +++-
contrib/btree_gin/sql/timestamptz.sql | 22 +-
8 files changed, 925 insertions(+), 25 deletions(-)
diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql
index 13c84ad667..b8483c3b9a 100644
--- a/contrib/btree_gin/btree_gin--1.3--1.4.sql
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -101,3 +101,51 @@ ADD
OPERATOR 12 >= (name, text),
OPERATOR 13 > (name, text)
;
+
+ALTER OPERATOR FAMILY date_ops USING gin
+ADD
+ -- Code 1: RHS is timestamp
+ OPERATOR 9 < (date, timestamp),
+ OPERATOR 10 <= (date, timestamp),
+ OPERATOR 11 = (date, timestamp),
+ OPERATOR 12 >= (date, timestamp),
+ OPERATOR 13 > (date, timestamp),
+ -- Code 2: RHS is timestamptz
+ OPERATOR 17 < (date, timestamptz),
+ OPERATOR 18 <= (date, timestamptz),
+ OPERATOR 19 = (date, timestamptz),
+ OPERATOR 20 >= (date, timestamptz),
+ OPERATOR 21 > (date, timestamptz)
+;
+
+ALTER OPERATOR FAMILY timestamp_ops USING gin
+ADD
+ -- Code 1: RHS is date
+ OPERATOR 9 < (timestamp, date),
+ OPERATOR 10 <= (timestamp, date),
+ OPERATOR 11 = (timestamp, date),
+ OPERATOR 12 >= (timestamp, date),
+ OPERATOR 13 > (timestamp, date),
+ -- Code 2: RHS is timestamptz
+ OPERATOR 17 < (timestamp, timestamptz),
+ OPERATOR 18 <= (timestamp, timestamptz),
+ OPERATOR 19 = (timestamp, timestamptz),
+ OPERATOR 20 >= (timestamp, timestamptz),
+ OPERATOR 21 > (timestamp, timestamptz)
+;
+
+ALTER OPERATOR FAMILY timestamptz_ops USING gin
+ADD
+ -- Code 1: RHS is date
+ OPERATOR 9 < (timestamptz, date),
+ OPERATOR 10 <= (timestamptz, date),
+ OPERATOR 11 = (timestamptz, date),
+ OPERATOR 12 >= (timestamptz, date),
+ OPERATOR 13 > (timestamptz, date),
+ -- Code 2: RHS is timestamp
+ OPERATOR 17 < (timestamptz, timestamp),
+ OPERATOR 18 <= (timestamptz, timestamp),
+ OPERATOR 19 = (timestamptz, timestamp),
+ OPERATOR 20 >= (timestamptz, timestamp),
+ OPERATOR 21 > (timestamptz, timestamp)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 5b2d8795ad..3bbde065b6 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -480,21 +480,75 @@ leftmostvalue_timestamp(void)
return TimestampGetDatum(DT_NOBEGIN);
}
+static Datum
+cvt_date_timestamp(Datum input)
+{
+ DateADT val = DatumGetDateADT(input);
+ Timestamp result;
+ int overflow;
+
+ result = date2timestamp_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return TimestampGetDatum(result);
+}
+
+static Datum
+cvt_timestamptz_timestamp(Datum input)
+{
+ TimestampTz val = DatumGetTimestampTz(input);
+ Timestamp result;
+ int overflow;
+
+ result = timestamptz2timestamp_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return TimestampGetDatum(result);
+}
+
static const bool timestamp_rhs_is_varlena[] =
-{false};
+{false, false, false};
+
+static const btree_gin_convert_function timestamp_cvt_fns[] =
+{NULL, cvt_date_timestamp, cvt_timestamptz_timestamp};
static const PGFunction timestamp_cmp_fns[] =
-{timestamp_cmp};
+{timestamp_cmp, date_cmp_timestamp, timestamptz_cmp_timestamp};
+
+GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, timestamp_cvt_fns, timestamp_cmp_fns)
+
+static Datum
+cvt_date_timestamptz(Datum input)
+{
+ DateADT val = DatumGetDateADT(input);
+ TimestampTz result;
+ int overflow;
-GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, NULL, timestamp_cmp_fns)
+ result = date2timestamptz_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return TimestampTzGetDatum(result);
+}
+
+static Datum
+cvt_timestamp_timestamptz(Datum input)
+{
+ Timestamp val = DatumGetTimestamp(input);
+ TimestampTz result;
+ int overflow;
+
+ result = timestamp2timestamptz_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return TimestampTzGetDatum(result);
+}
static const bool timestamptz_rhs_is_varlena[] =
-{false};
+{false, false, false};
+
+static const btree_gin_convert_function timestamptz_cvt_fns[] =
+{NULL, cvt_date_timestamptz, cvt_timestamp_timestamptz};
static const PGFunction timestamptz_cmp_fns[] =
-{timestamp_cmp};
+{timestamp_cmp, date_cmp_timestamptz, timestamp_cmp_timestamptz};
-GIN_SUPPORT(timestamptz, leftmostvalue_timestamp, timestamptz_rhs_is_varlena, NULL, timestamptz_cmp_fns)
+GIN_SUPPORT(timestamptz, leftmostvalue_timestamp, timestamptz_rhs_is_varlena, timestamptz_cvt_fns,
timestamptz_cmp_fns)
static Datum
leftmostvalue_time(void)
@@ -535,13 +589,40 @@ leftmostvalue_date(void)
return DateADTGetDatum(DATEVAL_NOBEGIN);
}
+static Datum
+cvt_timestamp_date(Datum input)
+{
+ Timestamp val = DatumGetTimestamp(input);
+ DateADT result;
+ int overflow;
+
+ result = timestamp2date_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return DateADTGetDatum(result);
+}
+
+static Datum
+cvt_timestamptz_date(Datum input)
+{
+ TimestampTz val = DatumGetTimestampTz(input);
+ DateADT result;
+ int overflow;
+
+ result = timestamptz2date_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return DateADTGetDatum(result);
+}
+
static const bool date_rhs_is_varlena[] =
-{false};
+{false, false, false};
+
+static const btree_gin_convert_function date_cvt_fns[] =
+{NULL, cvt_timestamp_date, cvt_timestamptz_date};
static const PGFunction date_cmp_fns[] =
-{date_cmp};
+{date_cmp, timestamp_cmp_date, timestamptz_cmp_date};
-GIN_SUPPORT(date, leftmostvalue_date, date_rhs_is_varlena, NULL, date_cmp_fns)
+GIN_SUPPORT(date, leftmostvalue_date, date_rhs_is_varlena, date_cvt_fns, date_cmp_fns)
static Datum
leftmostvalue_interval(void)
diff --git a/contrib/btree_gin/expected/date.out b/contrib/btree_gin/expected/date.out
index 40dfa308cf..d638299b21 100644
--- a/contrib/btree_gin/expected/date.out
+++ b/contrib/btree_gin/expected/date.out
@@ -49,3 +49,273 @@ SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i;
10-28-2004
(2 rows)
+explain (costs off)
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_date
+ Recheck Cond: (i < 'Tue Oct 26 00:00:00 2004'::timestamp without time zone)
+ -> Bitmap Index Scan on idx_date
+ Index Cond: (i < 'Tue Oct 26 00:00:00 2004'::timestamp without time zone)
+(6 rows)
+
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+(3 rows)
+
+SELECT * FROM test_date WHERE i<='2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+(4 rows)
+
+SELECT * FROM test_date WHERE i='2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-26-2004
+(1 row)
+
+SELECT * FROM test_date WHERE i>='2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+(3 rows)
+
+SELECT * FROM test_date WHERE i>'2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-27-2004
+ 10-28-2004
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_date
+ Recheck Cond: (i < 'Tue Oct 26 00:00:00 2004 PDT'::timestamp with time zone)
+ -> Bitmap Index Scan on idx_date
+ Index Cond: (i < 'Tue Oct 26 00:00:00 2004 PDT'::timestamp with time zone)
+(6 rows)
+
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+(3 rows)
+
+SELECT * FROM test_date WHERE i<='2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+(4 rows)
+
+SELECT * FROM test_date WHERE i='2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-26-2004
+(1 row)
+
+SELECT * FROM test_date WHERE i>='2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+(3 rows)
+
+SELECT * FROM test_date WHERE i>'2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-27-2004
+ 10-28-2004
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_date VALUES ('-infinity'), ('infinity');
+SELECT * FROM test_date WHERE i<'-infinity'::timestamp ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_date WHERE i<='-infinity'::timestamp ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i='-infinity'::timestamp ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>='-infinity'::timestamp ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_date WHERE i>'-infinity'::timestamp ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(7 rows)
+
+SELECT * FROM test_date WHERE i<'infinity'::timestamp ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+(7 rows)
+
+SELECT * FROM test_date WHERE i<='infinity'::timestamp ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_date WHERE i='infinity'::timestamp ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>='infinity'::timestamp ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>'infinity'::timestamp ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_date WHERE i<'-infinity'::timestamptz ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_date WHERE i<='-infinity'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i='-infinity'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>='-infinity'::timestamptz ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_date WHERE i>'-infinity'::timestamptz ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(7 rows)
+
+SELECT * FROM test_date WHERE i<'infinity'::timestamptz ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+(7 rows)
+
+SELECT * FROM test_date WHERE i<='infinity'::timestamptz ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_date WHERE i='infinity'::timestamptz ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>='infinity'::timestamptz ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>'infinity'::timestamptz ORDER BY i;
+ i
+---
+(0 rows)
+
diff --git a/contrib/btree_gin/expected/timestamp.out b/contrib/btree_gin/expected/timestamp.out
index a236cdc94a..9f823a3c74 100644
--- a/contrib/btree_gin/expected/timestamp.out
+++ b/contrib/btree_gin/expected/timestamp.out
@@ -7,8 +7,8 @@ INSERT INTO test_timestamp VALUES
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
- ( '2004-10-26 09:55:08' ),
- ( '2004-10-26 10:55:08' )
+ ( '2004-10-27 09:55:08' ),
+ ( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamp ON test_timestamp USING gin (i);
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
@@ -38,14 +38,302 @@ SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY
i
--------------------------
Tue Oct 26 08:55:08 2004
- Tue Oct 26 09:55:08 2004
- Tue Oct 26 10:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
(3 rows)
SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
i
--------------------------
- Tue Oct 26 09:55:08 2004
- Tue Oct 26 10:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
(2 rows)
+explain (costs off)
+SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i;
+ QUERY PLAN
+----------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_timestamp
+ Recheck Cond: (i < '10-27-2004'::date)
+ -> Bitmap Index Scan on idx_timestamp
+ Index Cond: (i < '10-27-2004'::date)
+(6 rows)
+
+SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+(4 rows)
+
+SELECT * FROM test_timestamp WHERE i<='2004-10-27'::date ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+(4 rows)
+
+SELECT * FROM test_timestamp WHERE i='2004-10-27'::date ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamp WHERE i>='2004-10-27'::date ORDER BY i;
+ i
+--------------------------
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(2 rows)
+
+SELECT * FROM test_timestamp WHERE i>'2004-10-27'::date ORDER BY i;
+ i
+--------------------------
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_timestamp
+ Recheck Cond: (i < 'Tue Oct 26 08:55:08 2004 PDT'::timestamp with time zone)
+ -> Bitmap Index Scan on idx_timestamp
+ Index Cond: (i < 'Tue Oct 26 08:55:08 2004 PDT'::timestamp with time zone)
+(6 rows)
+
+SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+(3 rows)
+
+SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+(4 rows)
+
+SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 08:55:08 2004
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(3 rows)
+
+SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_timestamp VALUES ('-infinity'), ('infinity');
+SELECT * FROM test_timestamp WHERE i<'-infinity'::date ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamp WHERE i<='-infinity'::date ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i='-infinity'::date ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='-infinity'::date ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_timestamp WHERE i>'-infinity'::date ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(7 rows)
+
+SELECT * FROM test_timestamp WHERE i<'infinity'::date ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(7 rows)
+
+SELECT * FROM test_timestamp WHERE i<='infinity'::date ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_timestamp WHERE i='infinity'::date ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='infinity'::date ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>'infinity'::date ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamp WHERE i<'-infinity'::timestamptz ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamp WHERE i<='-infinity'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i='-infinity'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='-infinity'::timestamptz ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_timestamp WHERE i>'-infinity'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(7 rows)
+
+SELECT * FROM test_timestamp WHERE i<'infinity'::timestamptz ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(7 rows)
+
+SELECT * FROM test_timestamp WHERE i<='infinity'::timestamptz ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_timestamp WHERE i='infinity'::timestamptz ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='infinity'::timestamptz ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>'infinity'::timestamptz ORDER BY i;
+ i
+---
+(0 rows)
+
+-- This PST timestamptz will underflow if converted to timestamp
+SELECT * FROM test_timestamp WHERE i<='4714-11-23 17:00 BC'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>'4714-11-23 17:00 BC'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(7 rows)
+
diff --git a/contrib/btree_gin/expected/timestamptz.out b/contrib/btree_gin/expected/timestamptz.out
index d53963d2a0..0dada0b662 100644
--- a/contrib/btree_gin/expected/timestamptz.out
+++ b/contrib/btree_gin/expected/timestamptz.out
@@ -7,8 +7,8 @@ INSERT INTO test_timestamptz VALUES
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
- ( '2004-10-26 09:55:08' ),
- ( '2004-10-26 10:55:08' )
+ ( '2004-10-27 09:55:08' ),
+ ( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamptz ON test_timestamptz USING gin (i);
SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
@@ -38,14 +38,113 @@ SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER
i
------------------------------
Tue Oct 26 08:55:08 2004 PDT
- Tue Oct 26 09:55:08 2004 PDT
- Tue Oct 26 10:55:08 2004 PDT
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
(3 rows)
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
i
------------------------------
- Tue Oct 26 09:55:08 2004 PDT
- Tue Oct 26 10:55:08 2004 PDT
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
+ QUERY PLAN
+----------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_timestamptz
+ Recheck Cond: (i < '10-27-2004'::date)
+ -> Bitmap Index Scan on idx_timestamptz
+ Index Cond: (i < '10-27-2004'::date)
+(6 rows)
+
+SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 03:55:08 2004 PDT
+ Tue Oct 26 04:55:08 2004 PDT
+ Tue Oct 26 05:55:08 2004 PDT
+ Tue Oct 26 08:55:08 2004 PDT
+(4 rows)
+
+SELECT * FROM test_timestamptz WHERE i<='2004-10-27'::date ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 03:55:08 2004 PDT
+ Tue Oct 26 04:55:08 2004 PDT
+ Tue Oct 26 05:55:08 2004 PDT
+ Tue Oct 26 08:55:08 2004 PDT
+(4 rows)
+
+SELECT * FROM test_timestamptz WHERE i='2004-10-27'::date ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamptz WHERE i>='2004-10-27'::date ORDER BY i;
+ i
+------------------------------
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
+(2 rows)
+
+SELECT * FROM test_timestamptz WHERE i>'2004-10-27'::date ORDER BY i;
+ i
+------------------------------
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_timestamptz
+ Recheck Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
+ -> Bitmap Index Scan on idx_timestamptz
+ Index Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
+(6 rows)
+
+SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 03:55:08 2004 PDT
+ Tue Oct 26 04:55:08 2004 PDT
+ Tue Oct 26 05:55:08 2004 PDT
+(3 rows)
+
+SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 03:55:08 2004 PDT
+ Tue Oct 26 04:55:08 2004 PDT
+ Tue Oct 26 05:55:08 2004 PDT
+ Tue Oct 26 08:55:08 2004 PDT
+(4 rows)
+
+SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 08:55:08 2004 PDT
+(1 row)
+
+SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 08:55:08 2004 PDT
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
+(3 rows)
+
+SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
(2 rows)
diff --git a/contrib/btree_gin/sql/date.sql b/contrib/btree_gin/sql/date.sql
index 35086f6b81..6bd6a08da3 100644
--- a/contrib/btree_gin/sql/date.sql
+++ b/contrib/btree_gin/sql/date.sql
@@ -20,3 +20,49 @@ SELECT * FROM test_date WHERE i<='2004-10-26'::date ORDER BY i;
SELECT * FROM test_date WHERE i='2004-10-26'::date ORDER BY i;
SELECT * FROM test_date WHERE i>='2004-10-26'::date ORDER BY i;
SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i<='2004-10-26'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i='2004-10-26'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>='2004-10-26'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>'2004-10-26'::timestamp ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i<='2004-10-26'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i='2004-10-26'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>='2004-10-26'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>'2004-10-26'::timestamptz ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_date VALUES ('-infinity'), ('infinity');
+
+SELECT * FROM test_date WHERE i<'-infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i<='-infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i='-infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>='-infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>'-infinity'::timestamp ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i<='infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i='infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>='infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>'infinity'::timestamp ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i<='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>'-infinity'::timestamptz ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i<='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>'infinity'::timestamptz ORDER BY i;
diff --git a/contrib/btree_gin/sql/timestamp.sql b/contrib/btree_gin/sql/timestamp.sql
index 56727e81c4..9f830bcf69 100644
--- a/contrib/btree_gin/sql/timestamp.sql
+++ b/contrib/btree_gin/sql/timestamp.sql
@@ -9,8 +9,8 @@ INSERT INTO test_timestamp VALUES
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
- ( '2004-10-26 09:55:08' ),
- ( '2004-10-26 10:55:08' )
+ ( '2004-10-27 09:55:08' ),
+ ( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamp ON test_timestamp USING gin (i);
@@ -20,3 +20,53 @@ SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY
SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'2004-10-27'::date ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_timestamp VALUES ('-infinity'), ('infinity');
+
+SELECT * FROM test_timestamp WHERE i<'-infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='-infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='-infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='-infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'-infinity'::date ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'infinity'::date ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'-infinity'::timestamptz ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'infinity'::timestamptz ORDER BY i;
+
+-- This PST timestamptz will underflow if converted to timestamp
+SELECT * FROM test_timestamp WHERE i<='4714-11-23 17:00 BC'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'4714-11-23 17:00 BC'::timestamptz ORDER BY i;
diff --git a/contrib/btree_gin/sql/timestamptz.sql b/contrib/btree_gin/sql/timestamptz.sql
index e6cfdb1b07..40d2d7ed32 100644
--- a/contrib/btree_gin/sql/timestamptz.sql
+++ b/contrib/btree_gin/sql/timestamptz.sql
@@ -9,8 +9,8 @@ INSERT INTO test_timestamptz VALUES
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
- ( '2004-10-26 09:55:08' ),
- ( '2004-10-26 10:55:08' )
+ ( '2004-10-27 09:55:08' ),
+ ( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamptz ON test_timestamptz USING gin (i);
@@ -20,3 +20,21 @@ SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER
SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i;
SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i;
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
+
+SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i<='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i>='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i>'2004-10-27'::date ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
+
+SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
--
2.43.5
pgsql-hackers by date: