>> I think we should change that. It's very counter intuitive that a query
>> can change behavior when the planner flips from using e.g. a Seq Scan to
>> a Bitmap Index Scan or the other way around. There's already a patch for
>> that, see [1].
>>
>> [1]
>> https://www.postgresql.org/message-id/flat/db087c3e-230e-4119-8a03-8b5d74956bc2%40gmail.com
>
>
> That's not only unintuitive, it is a clear bug.
> An index is not allowed to change the semantics of a query.
>
> Does your patch fix the bug, that is, will the query with "WHERE t = 'right'"
> return both results? That's the case that is mostly in need of fixing.
> I am not sure if the behavior for the % operator should also be considered
> a bug.
Not yet :).
>>> I don't know what the correct fix would be. Perhaps just refusing to use
>>> the index for equality comparisons with non-deterministic collations.
>>
>> If we merge [1], then not only = but also LIKE would be incorrect. How
>> about disabling CREATE INDEX USING gin on columns with non-deterministic
>> collations?
>
> Oh, I see. So your patch won't fix the bug.
Indeed.
> I am not sure if refusing to *create* the index is the best solution.
> Perhaps a warning will be better:
>
> WARNING: GIN indexes won't be used columns with non-deterministic collations
At this point we could more generally say
WARNING: pg_trgm does not use inferred column collations but always
uses the default database collation.
But I'm hoping the patch in [1] gets merged and we can also use the
inferred collation to fix the bug you found.
>> Or is there maybe a way to make these cases work correctly for
>> non-deterministic collations by applying the collation when extracting
>> the search trigrams? I take a look into that.
Attached patch makes your case work, including the % case. It builds on
top of the other patches from [1] that makes pg_trgm use the inferred
collation trigram extraction.
Instead of using btint4cmp() to compare trigrams, the patch uses a
collation-aware string comparison function.
This is just a PoC. I haven't given much thought to the details but e.g.
when three consecutive characters exceed 3 bytes then compact_trigram()
uses a truncated 32-bit hash value as trigram instead. Such trigrams
won't work in all cases. We could omit them from the query string but
for languages where the majority of trigrams are hashed or where the
query string consists of only a few trigrams, the look-up performance
would suffer.
I guess better would be using a collation-aware hash function that maps
different values that compare equal to the same hash value. hashtext()
does that already. The new comparison function would then have to
distinguish between plain text trigrams and hash trigrams.
Alternatively, we could store all trigrams as hashes but that would
break functions such as show_trgm().
--
David Geier
From 5c86bfd3e83b2bde7706746c59ac148b1553e717 Mon Sep 17 00:00:00 2001
From: David Geier <[email protected]>
Date: Thu, 23 Apr 2026 11:08:33 +0200
Subject: [PATCH v1 3/3] Use correct collation for comparison
---
contrib/pg_trgm/Makefile | 6 +--
contrib/pg_trgm/meson.build | 1 +
contrib/pg_trgm/pg_trgm--1.6--1.7.sql | 20 +++++++++
contrib/pg_trgm/pg_trgm.control | 2 +-
contrib/pg_trgm/trgm_gin.c | 65 +++++++++++++++++++++++++++
5 files changed, 90 insertions(+), 4 deletions(-)
create mode 100644 contrib/pg_trgm/pg_trgm--1.6--1.7.sql
diff --git a/contrib/pg_trgm/Makefile b/contrib/pg_trgm/Makefile
index 26b3028b75e..556b76f49f2 100644
--- a/contrib/pg_trgm/Makefile
+++ b/contrib/pg_trgm/Makefile
@@ -9,9 +9,9 @@ OBJS = \
trgm_regexp.o
EXTENSION = pg_trgm
-DATA = pg_trgm--1.5--1.6.sql pg_trgm--1.4--1.5.sql pg_trgm--1.3--1.4.sql \
- pg_trgm--1.3.sql pg_trgm--1.2--1.3.sql pg_trgm--1.1--1.2.sql \
- pg_trgm--1.0--1.1.sql
+DATA = pg_trgm--1.6--1.7.sql pg_trgm--1.5--1.6.sql pg_trgm--1.4--1.5.sql \
+ pg_trgm--1.3--1.4.sql pg_trgm--1.3.sql pg_trgm--1.2--1.3.sql \
+ pg_trgm--1.1--1.2.sql pg_trgm--1.0--1.1.sql
PGFILEDESC = "pg_trgm - trigram matching"
REGRESS = pg_trgm pg_utf8_trgm pg_word_trgm pg_strict_word_trgm pg_trgm_collation
diff --git a/contrib/pg_trgm/meson.build b/contrib/pg_trgm/meson.build
index 5eafa774435..9fdf8c2d07e 100644
--- a/contrib/pg_trgm/meson.build
+++ b/contrib/pg_trgm/meson.build
@@ -28,6 +28,7 @@ install_data(
'pg_trgm--1.3.sql',
'pg_trgm--1.4--1.5.sql',
'pg_trgm--1.5--1.6.sql',
+ 'pg_trgm--1.6--1.7.sql',
'pg_trgm.control',
kwargs: contrib_data_args,
)
diff --git a/contrib/pg_trgm/pg_trgm--1.6--1.7.sql b/contrib/pg_trgm/pg_trgm--1.6--1.7.sql
new file mode 100644
index 00000000000..dc5552e375e
--- /dev/null
+++ b/contrib/pg_trgm/pg_trgm--1.6--1.7.sql
@@ -0,0 +1,20 @@
+/* contrib/pg_trgm/pg_trgm--1.6--1.7.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_trgm UPDATE TO '1.7'" to load this file. \quit
+
+-- Create collation-aware comparison function for trigrams
+CREATE FUNCTION gin_compare_value_trgm(int4, int4)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+-- Replace btint4cmp with gin_compare_value_trgm in the operator family
+-- This ensures trigram comparisons respect collation settings
+-- First drop the old function, then add the new one
+ALTER OPERATOR FAMILY gin_trgm_ops USING gin DROP
+ FUNCTION 1 (text, text);
+
+ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
+ FUNCTION 1 (text, text) gin_compare_value_trgm (int4, int4);
+
diff --git a/contrib/pg_trgm/pg_trgm.control b/contrib/pg_trgm/pg_trgm.control
index 1d6a9ddf259..6e3ee43c510 100644
--- a/contrib/pg_trgm/pg_trgm.control
+++ b/contrib/pg_trgm/pg_trgm.control
@@ -1,6 +1,6 @@
# pg_trgm extension
comment = 'text similarity measurement and index searching based on trigrams'
-default_version = '1.6'
+default_version = '1.7'
module_pathname = '$libdir/pg_trgm'
relocatable = true
trusted = true
diff --git a/contrib/pg_trgm/trgm_gin.c b/contrib/pg_trgm/trgm_gin.c
index 14a892c657d..dadbc789349 100644
--- a/contrib/pg_trgm/trgm_gin.c
+++ b/contrib/pg_trgm/trgm_gin.c
@@ -5,8 +5,11 @@
#include "access/gin.h"
#include "access/stratnum.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_type.h"
#include "fmgr.h"
#include "trgm.h"
+#include "utils/pg_locale.h"
#include "varatt.h"
PG_FUNCTION_INFO_V1(gin_extract_trgm);
@@ -14,6 +17,7 @@ PG_FUNCTION_INFO_V1(gin_extract_value_trgm);
PG_FUNCTION_INFO_V1(gin_extract_query_trgm);
PG_FUNCTION_INFO_V1(gin_trgm_consistent);
PG_FUNCTION_INFO_V1(gin_trgm_triconsistent);
+PG_FUNCTION_INFO_V1(gin_compare_value_trgm);
/*
* This function can only be called if a pre-9.1 version of the GIN operator
@@ -169,6 +173,67 @@ gin_extract_query_trgm(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(entries);
}
+/*
+ * Compare two trigram values for GIN index.
+ * This function considers the active collation when comparing trigrams,
+ * unlike btint4cmp which treats them as plain integers.
+ */
+Datum
+gin_compare_value_trgm(PG_FUNCTION_ARGS)
+{
+ int32 a = PG_GETARG_INT32(0);
+ int32 b = PG_GETARG_INT32(1);
+ Oid collid = PG_GET_COLLATION();
+ pg_locale_t locale = 0;
+
+ /*
+ * If a non-default collation is specified, we need to compare the
+ * trigrams character-by-character using the collation's rules.
+ */
+ if (collid != DEFAULT_COLLATION_OID)
+ locale = pg_newlocale_from_collation(collid);
+
+ if (locale && locale->collate_is_c)
+ locale = 0; /* C collation can use simple comparison */
+
+ if (locale && locale->collate)
+ {
+ /*
+ * For non-C collations, extract the three bytes from each trigram
+ * and compare them using the collation's comparison function.
+ */
+ char str_a[3];
+ char str_b[3];
+ int result;
+
+ /* Extract bytes from the packed integer representation */
+ str_a[0] = (a >> 16) & 0xFF;
+ str_a[1] = (a >> 8) & 0xFF;
+ str_a[2] = a & 0xFF;
+
+ str_b[0] = (b >> 16) & 0xFF;
+ str_b[1] = (b >> 8) & 0xFF;
+ str_b[2] = b & 0xFF;
+
+ /* Use collation-aware comparison */
+ result = pg_strncoll(str_a, 3, str_b, 3, locale);
+ PG_RETURN_INT32(result);
+ }
+ else
+ {
+ /*
+ * For C collation or default collation, simple integer comparison
+ * is sufficient and faster.
+ */
+ if (a < b)
+ PG_RETURN_INT32(-1);
+ else if (a > b)
+ PG_RETURN_INT32(1);
+ else
+ PG_RETURN_INT32(0);
+ }
+}
+
Datum
gin_trgm_consistent(PG_FUNCTION_ARGS)
{
--
2.51.0
From a62959930214cee04e9f190f908746067d8d1a62 Mon Sep 17 00:00:00 2001
From: David Geier <[email protected]>
Date: Fri, 23 Jan 2026 15:39:06 +0100
Subject: [PATCH v1 2/3] Use correct collation for finding word boundaries
pg_trgm finds all words in the input string and creates trigrams for
them. Word characters are alpha-numeric characters. What qualifies as
alpha-numeric character depends on the collation. Previously, pg_trgm
always used the default collation. Now the specified collation is used
instead.
---
.../pg_trgm/expected/pg_trgm_collation.out | 13 ++++++++++++
contrib/pg_trgm/sql/pg_trgm_collation.sql | 5 +++++
contrib/pg_trgm/trgm.h | 6 +++---
contrib/pg_trgm/trgm_op.c | 20 +++++++++----------
contrib/pg_trgm/trgm_regexp.c | 2 +-
src/backend/tsearch/ts_locale.c | 8 ++++----
src/include/tsearch/ts_locale.h | 3 ++-
7 files changed, 38 insertions(+), 19 deletions(-)
diff --git a/contrib/pg_trgm/expected/pg_trgm_collation.out b/contrib/pg_trgm/expected/pg_trgm_collation.out
index 472ce867665..0cc53edd821 100644
--- a/contrib/pg_trgm/expected/pg_trgm_collation.out
+++ b/contrib/pg_trgm/expected/pg_trgm_collation.out
@@ -41,3 +41,16 @@ SELECT similarity('ıstanbul' COLLATE "C", 'ISTANBUL' COLLATE "C");
0.54545456
(1 row)
+-- Test that word boundary identification uses specified collation
+SELECT show_trgm('helloıtestIworldİcode' COLLATE "tr-x-icu");
+ show_trgm
+-------------------------------------------------------------------------------------------------------------------------------
+ {0x8fc0a2,0x93dfbf,0x1bf43c," h"," he",0x22d44f,0x4398ff,cod,"de ",dic,ell,est,hel,ico,ldi,llo,ode,orl,0x71b8f5,rld,tes,wor}
+(1 row)
+
+SELECT show_trgm('helloıtestIworldİcode' COLLATE "C");
+ show_trgm
+-------------------------------------------------------------------------------------------------------------
+ {" c"," h"," t"," co"," he"," te",cod,"de ",ell,est,hel,iwo,"ld ",llo,"lo ",ode,orl,rld,sti,tes,tiw,wor}
+(1 row)
+
diff --git a/contrib/pg_trgm/sql/pg_trgm_collation.sql b/contrib/pg_trgm/sql/pg_trgm_collation.sql
index afb3973a8b3..e1a5c7c5fa8 100644
--- a/contrib/pg_trgm/sql/pg_trgm_collation.sql
+++ b/contrib/pg_trgm/sql/pg_trgm_collation.sql
@@ -22,3 +22,8 @@ SELECT show_trgm('ISTANBUL' COLLATE "C");
SELECT similarity('ıstanbul' COLLATE "tr-x-icu", 'ISTANBUL' COLLATE "tr-x-icu");
SELECT similarity('ıstanbul' COLLATE "C", 'ISTANBUL' COLLATE "C");
+-- Test that word boundary identification uses specified collation
+
+SELECT show_trgm('helloıtestIworldİcode' COLLATE "tr-x-icu");
+SELECT show_trgm('helloıtestIworldİcode' COLLATE "C");
+
diff --git a/contrib/pg_trgm/trgm.h b/contrib/pg_trgm/trgm.h
index b6911e91458..3c4db129e20 100644
--- a/contrib/pg_trgm/trgm.h
+++ b/contrib/pg_trgm/trgm.h
@@ -47,9 +47,9 @@ typedef char trgm[3];
} while(0)
extern int (*CMPTRGM) (const void *a, const void *b);
-#define ISWORDCHR(c, len) (t_isalnum_with_len(c, len))
-#define ISPRINTABLECHAR(a) ( isascii( *(unsigned char*)(a) ) && (isalnum( *(unsigned char*)(a) ) || *(unsigned char*)(a)==' ') )
-#define ISPRINTABLETRGM(t) ( ISPRINTABLECHAR( ((char*)(t)) ) && ISPRINTABLECHAR( ((char*)(t))+1 ) && ISPRINTABLECHAR( ((char*)(t))+2 ) )
+#define ISWORDCHR(c, len, collation) (t_isalnum_with_len_collation(c, len, collation))
+#define ISPRINTABLECHAR(a) ( isascii( *(unsigned char*)(a) ) && (isalnum( *(unsigned char*)(a) ) || *(unsigned char*)(a)==' ') )
+#define ISPRINTABLETRGM(t) ( ISPRINTABLECHAR( ((char*)(t)) ) && ISPRINTABLECHAR( ((char*)(t))+1 ) && ISPRINTABLECHAR( ((char*)(t))+2 ) )
#define ISESCAPECHAR(x) (*(x) == '\\') /* Wildcard escape character */
#define ISWILDCARDCHAR(x) (*(x) == '_' || *(x) == '%') /* Wildcard
diff --git a/contrib/pg_trgm/trgm_op.c b/contrib/pg_trgm/trgm_op.c
index c04d7265153..dd88b1fddf9 100644
--- a/contrib/pg_trgm/trgm_op.c
+++ b/contrib/pg_trgm/trgm_op.c
@@ -335,7 +335,7 @@ show_limit(PG_FUNCTION_ARGS)
* endword points to the character after word
*/
static char *
-find_word(char *str, int lenstr, char **endword)
+find_word(char *str, int lenstr, char **endword, Oid collation)
{
char *beginword = str;
const char *endstr = str + lenstr;
@@ -344,7 +344,7 @@ find_word(char *str, int lenstr, char **endword)
{
int clen = pg_mblen_range(beginword, endstr);
- if (ISWORDCHR(beginword, clen))
+ if (ISWORDCHR(beginword, clen, collation))
break;
beginword += clen;
}
@@ -357,7 +357,7 @@ find_word(char *str, int lenstr, char **endword)
{
int clen = pg_mblen_range(*endword, endstr);
- if (!ISWORDCHR(*endword, clen))
+ if (!ISWORDCHR(*endword, clen, collation))
break;
*endword += clen;
}
@@ -533,7 +533,7 @@ generate_trgm_only(growable_trgm_array *dst, char *str, int slen, Oid collation,
}
eword = str;
- while ((bword = find_word(eword, slen - (eword - str), &eword)) != NULL)
+ while ((bword = find_word(eword, slen - (eword - str), &eword, collation)) != NULL)
{
int oldlen;
@@ -950,7 +950,7 @@ calc_word_similarity(char *str1, int slen1, char *str2, int slen2,
*/
static const char *
get_wildcard_part(const char *str, int lenstr,
- char *buf, int *bytelen)
+ char *buf, int *bytelen, Oid collation)
{
const char *beginword = str;
const char *endword;
@@ -973,7 +973,7 @@ get_wildcard_part(const char *str, int lenstr,
if (in_escape)
{
- if (ISWORDCHR(beginword, clen))
+ if (ISWORDCHR(beginword, clen, collation))
break;
in_escape = false;
in_leading_wildcard_meta = false;
@@ -984,7 +984,7 @@ get_wildcard_part(const char *str, int lenstr,
in_escape = true;
else if (ISWILDCARDCHAR(beginword))
in_leading_wildcard_meta = true;
- else if (ISWORDCHR(beginword, clen))
+ else if (ISWORDCHR(beginword, clen, collation))
break;
else
in_leading_wildcard_meta = false;
@@ -1022,7 +1022,7 @@ get_wildcard_part(const char *str, int lenstr,
clen = pg_mblen_range(endword, endstr);
if (in_escape)
{
- if (ISWORDCHR(endword, clen))
+ if (ISWORDCHR(endword, clen, collation))
{
memcpy(s, endword, clen);
s += clen;
@@ -1049,7 +1049,7 @@ get_wildcard_part(const char *str, int lenstr,
in_trailing_wildcard_meta = true;
break;
}
- else if (ISWORDCHR(endword, clen))
+ else if (ISWORDCHR(endword, clen, collation))
{
memcpy(s, endword, clen);
s += clen;
@@ -1113,7 +1113,7 @@ generate_wildcard_trgm(const char *str, int slen, Oid collation)
*/
eword = str;
while ((eword = get_wildcard_part(eword, slen - (eword - str),
- buf, &bytelen)) != NULL)
+ buf, &bytelen, collation)) != NULL)
{
char *word;
diff --git a/contrib/pg_trgm/trgm_regexp.c b/contrib/pg_trgm/trgm_regexp.c
index b4086a75e17..36a10f24b09 100644
--- a/contrib/pg_trgm/trgm_regexp.c
+++ b/contrib/pg_trgm/trgm_regexp.c
@@ -811,7 +811,7 @@ getColorInfo(regex_t *regex, TrgmNFA *trgmNFA, Oid collation)
if (!clen)
continue; /* ok to ignore it altogether */
- if (ISWORDCHR(c.bytes, clen))
+ if (ISWORDCHR(c.bytes, clen, collation))
colorInfo->wordChars[colorInfo->wordCharsCount++] = c;
else
colorInfo->containsNonWord = true;
diff --git a/src/backend/tsearch/ts_locale.c b/src/backend/tsearch/ts_locale.c
index df02ffb12fd..6f331e054a2 100644
--- a/src/backend/tsearch/ts_locale.c
+++ b/src/backend/tsearch/ts_locale.c
@@ -26,27 +26,27 @@ static void tsearch_readline_callback(void *arg);
#define GENERATE_T_ISCLASS_DEF(character_class) \
/* mblen shall be that of the first character */ \
int \
-t_is##character_class##_with_len(const char *ptr, int mblen) \
+t_is##character_class##_with_len_collation(const char *ptr, int mblen, Oid collation) \
{ \
pg_wchar wstr[WC_BUF_LEN]; \
int wlen pg_attribute_unused(); \
wlen = pg_mb2wchar_with_len(ptr, wstr, mblen); \
Assert(wlen <= 1); \
/* pass single character, or NUL if empty */ \
- return pg_isw##character_class(wstr[0], pg_database_locale()); \
+ return pg_isw##character_class(wstr[0], pg_newlocale_from_collation(collation)); \
} \
\
/* ptr shall point to a NUL-terminated string */ \
int \
t_is##character_class##_cstr(const char *ptr) \
{ \
- return t_is##character_class##_with_len(ptr, pg_mblen_cstr(ptr)); \
+ return t_is##character_class##_with_len_collation(ptr, pg_mblen_cstr(ptr), DEFAULT_COLLATION_OID); \
} \
/* ptr shall point to a string with pre-validated encoding */ \
int \
t_is##character_class##_unbounded(const char *ptr) \
{ \
- return t_is##character_class##_with_len(ptr, pg_mblen_unbounded(ptr)); \
+ return t_is##character_class##_with_len_collation(ptr, pg_mblen_unbounded(ptr), DEFAULT_COLLATION_OID); \
} \
/* historical name for _unbounded */ \
int \
diff --git a/src/include/tsearch/ts_locale.h b/src/include/tsearch/ts_locale.h
index f4edf300c2b..43a606505a8 100644
--- a/src/include/tsearch/ts_locale.h
+++ b/src/include/tsearch/ts_locale.h
@@ -18,6 +18,7 @@
#include "lib/stringinfo.h"
#include "mb/pg_wchar.h"
+#include "catalog/pg_collation.h"
#include "utils/pg_locale.h"
/* working state for tsearch_readline (should be a local var in caller) */
@@ -56,7 +57,7 @@ ts_copychar_cstr(void *dest, const void *src)
#define COPYCHAR ts_copychar_cstr
#define GENERATE_T_ISCLASS_DECL(character_class) \
-extern int t_is##character_class##_with_len(const char *ptr, int mblen); \
+extern int t_is##character_class##_with_len_collation(const char *ptr, int mblen, Oid collation); \
extern int t_is##character_class##_cstr(const char *ptr); \
extern int t_is##character_class##_unbounded(const char *ptr); \
\
--
2.51.0
From dee7d76a76608b51bad4d98399e4d6a7484a2714 Mon Sep 17 00:00:00 2001
From: David Geier <[email protected]>
Date: Wed, 21 Jan 2026 14:54:28 +0100
Subject: [PATCH v1 1/3] Use correct collation for lowercasing
pg_trgm converts the input words to lowercase before extracting the
trigrams. The lowercase conversion depends on the collation. Previously,
pg_trgm always used the default collation. Now, the specified collation
is used instead.
---
contrib/pg_trgm/Makefile | 2 +-
.../pg_trgm/expected/pg_trgm_collation.out | 43 +++++++++++++
.../pg_trgm/expected/pg_trgm_collation_1.out | 9 +++
contrib/pg_trgm/meson.build | 1 +
contrib/pg_trgm/sql/pg_trgm_collation.sql | 24 ++++++++
contrib/pg_trgm/trgm.h | 4 +-
contrib/pg_trgm/trgm_gin.c | 7 ++-
contrib/pg_trgm/trgm_gist.c | 10 ++--
contrib/pg_trgm/trgm_op.c | 60 ++++++++++---------
contrib/pg_trgm/trgm_regexp.c | 20 +++----
10 files changed, 132 insertions(+), 48 deletions(-)
create mode 100644 contrib/pg_trgm/expected/pg_trgm_collation.out
create mode 100644 contrib/pg_trgm/expected/pg_trgm_collation_1.out
create mode 100644 contrib/pg_trgm/sql/pg_trgm_collation.sql
diff --git a/contrib/pg_trgm/Makefile b/contrib/pg_trgm/Makefile
index c1756993ec7..26b3028b75e 100644
--- a/contrib/pg_trgm/Makefile
+++ b/contrib/pg_trgm/Makefile
@@ -14,7 +14,7 @@ DATA = pg_trgm--1.5--1.6.sql pg_trgm--1.4--1.5.sql pg_trgm--1.3--1.4.sql \
pg_trgm--1.0--1.1.sql
PGFILEDESC = "pg_trgm - trigram matching"
-REGRESS = pg_trgm pg_utf8_trgm pg_word_trgm pg_strict_word_trgm
+REGRESS = pg_trgm pg_utf8_trgm pg_word_trgm pg_strict_word_trgm pg_trgm_collation
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pg_trgm/expected/pg_trgm_collation.out b/contrib/pg_trgm/expected/pg_trgm_collation.out
new file mode 100644
index 00000000000..472ce867665
--- /dev/null
+++ b/contrib/pg_trgm/expected/pg_trgm_collation.out
@@ -0,0 +1,43 @@
+/*
+ * This test is for ICU collations.
+ */
+/* skip test if not UTF8 server encoding or no ICU collations installed */
+SELECT getdatabaseencoding() <> 'UTF8' OR
+ (SELECT count(*) FROM pg_collation WHERE collprovider = 'i' AND collname <> 'unicode') = 0
+ AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+-- Test that lowercase conversion of trigrams uses specified collation
+CREATE TABLE test(col TEXT COLLATE "tr-x-icu");
+INSERT INTO test VALUES ('ISTANBUL');
+SELECT show_trgm(col) FROM test;
+ show_trgm
+--------------------------------------------------------
+ {0xf31e1a,0xfe581d,0x3efd30,anb,bul,nbu,sta,tan,"ul "}
+(1 row)
+
+SELECT show_trgm('ISTANBUL' COLLATE "tr-x-icu");
+ show_trgm
+--------------------------------------------------------
+ {0xf31e1a,0xfe581d,0x3efd30,anb,bul,nbu,sta,tan,"ul "}
+(1 row)
+
+SELECT show_trgm('ISTANBUL' COLLATE "C");
+ show_trgm
+---------------------------------------------
+ {" i"," is",anb,bul,ist,nbu,sta,tan,"ul "}
+(1 row)
+
+SELECT similarity('ıstanbul' COLLATE "tr-x-icu", 'ISTANBUL' COLLATE "tr-x-icu");
+ similarity
+------------
+ 1
+(1 row)
+
+SELECT similarity('ıstanbul' COLLATE "C", 'ISTANBUL' COLLATE "C");
+ similarity
+------------
+ 0.54545456
+(1 row)
+
diff --git a/contrib/pg_trgm/expected/pg_trgm_collation_1.out b/contrib/pg_trgm/expected/pg_trgm_collation_1.out
new file mode 100644
index 00000000000..25c99c4abf0
--- /dev/null
+++ b/contrib/pg_trgm/expected/pg_trgm_collation_1.out
@@ -0,0 +1,9 @@
+/*
+ * This test is for ICU collations.
+ */
+/* skip test if not UTF8 server encoding or no ICU collations installed */
+SELECT getdatabaseencoding() <> 'UTF8' OR
+ (SELECT count(*) FROM pg_collation WHERE collprovider = 'i' AND collname <> 'unicode') = 0
+ AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/contrib/pg_trgm/meson.build b/contrib/pg_trgm/meson.build
index 3ecf95ba862..5eafa774435 100644
--- a/contrib/pg_trgm/meson.build
+++ b/contrib/pg_trgm/meson.build
@@ -42,6 +42,7 @@ tests += {
'pg_utf8_trgm',
'pg_word_trgm',
'pg_strict_word_trgm',
+ 'pg_trgm_collation',
],
},
}
diff --git a/contrib/pg_trgm/sql/pg_trgm_collation.sql b/contrib/pg_trgm/sql/pg_trgm_collation.sql
new file mode 100644
index 00000000000..afb3973a8b3
--- /dev/null
+++ b/contrib/pg_trgm/sql/pg_trgm_collation.sql
@@ -0,0 +1,24 @@
+/*
+ * This test is for ICU collations.
+ */
+
+/* skip test if not UTF8 server encoding or no ICU collations installed */
+SELECT getdatabaseencoding() <> 'UTF8' OR
+ (SELECT count(*) FROM pg_collation WHERE collprovider = 'i' AND collname <> 'unicode') = 0
+ AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
+-- Test that lowercase conversion of trigrams uses specified collation
+
+CREATE TABLE test(col TEXT COLLATE "tr-x-icu");
+INSERT INTO test VALUES ('ISTANBUL');
+SELECT show_trgm(col) FROM test;
+SELECT show_trgm('ISTANBUL' COLLATE "tr-x-icu");
+
+SELECT show_trgm('ISTANBUL' COLLATE "C");
+
+SELECT similarity('ıstanbul' COLLATE "tr-x-icu", 'ISTANBUL' COLLATE "tr-x-icu");
+SELECT similarity('ıstanbul' COLLATE "C", 'ISTANBUL' COLLATE "C");
+
diff --git a/contrib/pg_trgm/trgm.h b/contrib/pg_trgm/trgm.h
index ca23aad4dd9..b6911e91458 100644
--- a/contrib/pg_trgm/trgm.h
+++ b/contrib/pg_trgm/trgm.h
@@ -119,8 +119,8 @@ extern double strict_word_similarity_threshold;
extern double index_strategy_get_limit(StrategyNumber strategy);
extern uint32 trgm2int(trgm *ptr);
extern void compact_trigram(trgm *tptr, char *str, int bytelen);
-extern TRGM *generate_trgm(char *str, int slen);
-extern TRGM *generate_wildcard_trgm(const char *str, int slen);
+extern TRGM *generate_trgm(char *str, int slen, Oid collation);
+extern TRGM *generate_wildcard_trgm(const char *str, int slen, Oid collation);
extern float4 cnt_sml(TRGM *trg1, TRGM *trg2, bool inexact);
extern bool trgm_contained_by(TRGM *trg1, TRGM *trg2);
extern bool *trgm_presence_map(TRGM *query, TRGM *key);
diff --git a/contrib/pg_trgm/trgm_gin.c b/contrib/pg_trgm/trgm_gin.c
index 5766b3e9955..14a892c657d 100644
--- a/contrib/pg_trgm/trgm_gin.c
+++ b/contrib/pg_trgm/trgm_gin.c
@@ -42,7 +42,7 @@ gin_extract_value_trgm(PG_FUNCTION_ARGS)
*nentries = 0;
- trg = generate_trgm(VARDATA_ANY(val), VARSIZE_ANY_EXHDR(val));
+ trg = generate_trgm(VARDATA_ANY(val), VARSIZE_ANY_EXHDR(val), PG_GET_COLLATION());
trglen = ARRNELEM(trg);
if (trglen > 0)
@@ -93,7 +93,7 @@ gin_extract_query_trgm(PG_FUNCTION_ARGS)
case WordSimilarityStrategyNumber:
case StrictWordSimilarityStrategyNumber:
case EqualStrategyNumber:
- trg = generate_trgm(VARDATA_ANY(val), VARSIZE_ANY_EXHDR(val));
+ trg = generate_trgm(VARDATA_ANY(val), VARSIZE_ANY_EXHDR(val), PG_GET_COLLATION());
break;
case ILikeStrategyNumber:
#ifndef IGNORECASE
@@ -107,7 +107,8 @@ gin_extract_query_trgm(PG_FUNCTION_ARGS)
* potentially-matching string must include.
*/
trg = generate_wildcard_trgm(VARDATA_ANY(val),
- VARSIZE_ANY_EXHDR(val));
+ VARSIZE_ANY_EXHDR(val),
+ PG_GET_COLLATION());
break;
case RegExpICaseStrategyNumber:
#ifndef IGNORECASE
diff --git a/contrib/pg_trgm/trgm_gist.c b/contrib/pg_trgm/trgm_gist.c
index 11812b2984e..d9102400442 100644
--- a/contrib/pg_trgm/trgm_gist.c
+++ b/contrib/pg_trgm/trgm_gist.c
@@ -123,7 +123,7 @@ gtrgm_compress(PG_FUNCTION_ARGS)
TRGM *res;
text *val = DatumGetTextPP(entry->key);
- res = generate_trgm(VARDATA_ANY(val), VARSIZE_ANY_EXHDR(val));
+ res = generate_trgm(VARDATA_ANY(val), VARSIZE_ANY_EXHDR(val), PG_GET_COLLATION());
retval = palloc_object(GISTENTRY);
gistentryinit(*retval, PointerGetDatum(res),
entry->rel, entry->page,
@@ -242,7 +242,8 @@ gtrgm_consistent(PG_FUNCTION_ARGS)
case StrictWordSimilarityStrategyNumber:
case EqualStrategyNumber:
qtrg = generate_trgm(VARDATA(query),
- querysize - VARHDRSZ);
+ querysize - VARHDRSZ,
+ PG_GET_COLLATION());
break;
case ILikeStrategyNumber:
#ifndef IGNORECASE
@@ -251,7 +252,8 @@ gtrgm_consistent(PG_FUNCTION_ARGS)
pg_fallthrough;
case LikeStrategyNumber:
qtrg = generate_wildcard_trgm(VARDATA(query),
- querysize - VARHDRSZ);
+ querysize - VARHDRSZ,
+ PG_GET_COLLATION());
break;
case RegExpICaseStrategyNumber:
#ifndef IGNORECASE
@@ -475,7 +477,7 @@ gtrgm_distance(PG_FUNCTION_ARGS)
{
char *newcache;
- qtrg = generate_trgm(VARDATA(query), querysize - VARHDRSZ);
+ qtrg = generate_trgm(VARDATA(query), querysize - VARHDRSZ, PG_GET_COLLATION());
newcache = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
MAXALIGN(querysize) +
diff --git a/contrib/pg_trgm/trgm_op.c b/contrib/pg_trgm/trgm_op.c
index 22bcc3c3361..c04d7265153 100644
--- a/contrib/pg_trgm/trgm_op.c
+++ b/contrib/pg_trgm/trgm_op.c
@@ -490,7 +490,7 @@ done:
* bounds_p: where to return bounds of trigrams (if needed).
*/
static void
-generate_trgm_only(growable_trgm_array *dst, char *str, int slen, TrgmBound **bounds_p)
+generate_trgm_only(growable_trgm_array *dst, char *str, int slen, Oid collation, TrgmBound **bounds_p)
{
size_t buflen;
char *buf;
@@ -542,7 +542,7 @@ generate_trgm_only(growable_trgm_array *dst, char *str, int slen, TrgmBound **bo
{
char *lowered;
- lowered = str_tolower(bword, eword - bword, DEFAULT_COLLATION_OID);
+ lowered = str_tolower(bword, eword - bword, collation);
bytelen = strlen(lowered);
/* grow the buffer if necessary */
@@ -596,13 +596,13 @@ generate_trgm_only(growable_trgm_array *dst, char *str, int slen, TrgmBound **bo
* Returns the sorted array of unique trigrams.
*/
TRGM *
-generate_trgm(char *str, int slen)
+generate_trgm(char *str, int slen, Oid collation)
{
TRGM *trg;
growable_trgm_array arr;
int len;
- generate_trgm_only(&arr, str, slen, NULL);
+ generate_trgm_only(&arr, str, slen, collation, NULL);
len = arr.length;
trg = arr.datum;
trg->flag = ARRKEY;
@@ -857,7 +857,7 @@ iterate_word_similarity(int *trg2indexes,
*/
static float4
calc_word_similarity(char *str1, int slen1, char *str2, int slen2,
- uint8 flags)
+ uint8 flags, Oid collation)
{
bool *found;
pos_trgm *ptrg;
@@ -875,9 +875,9 @@ calc_word_similarity(char *str1, int slen1, char *str2, int slen2,
/* Make positional trigrams */
- generate_trgm_only(&trg1, str1, slen1, NULL);
+ generate_trgm_only(&trg1, str1, slen1, collation, NULL);
len1 = trg1.length;
- generate_trgm_only(&trg2, str2, slen2, (flags & WORD_SIMILARITY_STRICT) ? &bounds : NULL);
+ generate_trgm_only(&trg2, str2, slen2, collation, (flags & WORD_SIMILARITY_STRICT) ? &bounds : NULL);
len2 = trg2.length;
ptrg = make_positional_trgm(GETARR(trg1.datum), len1, GETARR(trg2.datum), len2);
@@ -1086,7 +1086,7 @@ get_wildcard_part(const char *str, int lenstr,
* " a", "bcd" would be extracted.
*/
TRGM *
-generate_wildcard_trgm(const char *str, int slen)
+generate_wildcard_trgm(const char *str, int slen, Oid collation)
{
TRGM *trg;
growable_trgm_array arr;
@@ -1118,7 +1118,7 @@ generate_wildcard_trgm(const char *str, int slen)
char *word;
#ifdef IGNORECASE
- word = str_tolower(buf, bytelen, DEFAULT_COLLATION_OID);
+ word = str_tolower(buf, bytelen, collation);
bytelen = strlen(word);
#else
word = buf;
@@ -1177,7 +1177,7 @@ show_trgm(PG_FUNCTION_ARGS)
trgm *ptr;
int i;
- trg = generate_trgm(VARDATA_ANY(in), VARSIZE_ANY_EXHDR(in));
+ trg = generate_trgm(VARDATA_ANY(in), VARSIZE_ANY_EXHDR(in), PG_GET_COLLATION());
d = palloc_array(Datum, 1 + ARRNELEM(trg));
for (i = 0, ptr = GETARR(trg); i < ARRNELEM(trg); i++, ptr++)
@@ -1344,8 +1344,8 @@ similarity(PG_FUNCTION_ARGS)
*trg2;
float4 res;
- trg1 = generate_trgm(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1));
- trg2 = generate_trgm(VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2));
+ trg1 = generate_trgm(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1), PG_GET_COLLATION());
+ trg2 = generate_trgm(VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2), PG_GET_COLLATION());
res = cnt_sml(trg1, trg2, false);
@@ -1366,7 +1366,7 @@ word_similarity(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
- 0);
+ 0, PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1382,7 +1382,7 @@ strict_word_similarity(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
- WORD_SIMILARITY_STRICT);
+ WORD_SIMILARITY_STRICT, PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1392,9 +1392,10 @@ strict_word_similarity(PG_FUNCTION_ARGS)
Datum
similarity_dist(PG_FUNCTION_ARGS)
{
- float4 res = DatumGetFloat4(DirectFunctionCall2(similarity,
- PG_GETARG_DATUM(0),
- PG_GETARG_DATUM(1)));
+ float4 res = DatumGetFloat4(DirectFunctionCall2Coll(similarity,
+ PG_GET_COLLATION(),
+ PG_GETARG_DATUM(0),
+ PG_GETARG_DATUM(1)));
PG_RETURN_FLOAT4(1.0 - res);
}
@@ -1402,9 +1403,10 @@ similarity_dist(PG_FUNCTION_ARGS)
Datum
similarity_op(PG_FUNCTION_ARGS)
{
- float4 res = DatumGetFloat4(DirectFunctionCall2(similarity,
- PG_GETARG_DATUM(0),
- PG_GETARG_DATUM(1)));
+ float4 res = DatumGetFloat4(DirectFunctionCall2Coll(similarity,
+ PG_GET_COLLATION(),
+ PG_GETARG_DATUM(0),
+ PG_GETARG_DATUM(1)));
PG_RETURN_BOOL(res >= similarity_threshold);
}
@@ -1418,7 +1420,7 @@ word_similarity_op(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
- WORD_SIMILARITY_CHECK_ONLY);
+ WORD_SIMILARITY_CHECK_ONLY, PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1434,7 +1436,7 @@ word_similarity_commutator_op(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
- WORD_SIMILARITY_CHECK_ONLY);
+ WORD_SIMILARITY_CHECK_ONLY, PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1450,7 +1452,7 @@ word_similarity_dist_op(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
- 0);
+ 0, PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1466,7 +1468,7 @@ word_similarity_dist_commutator_op(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
- 0);
+ 0, PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1482,7 +1484,8 @@ strict_word_similarity_op(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
- WORD_SIMILARITY_CHECK_ONLY | WORD_SIMILARITY_STRICT);
+ WORD_SIMILARITY_CHECK_ONLY | WORD_SIMILARITY_STRICT,
+ PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1498,7 +1501,8 @@ strict_word_similarity_commutator_op(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
- WORD_SIMILARITY_CHECK_ONLY | WORD_SIMILARITY_STRICT);
+ WORD_SIMILARITY_CHECK_ONLY | WORD_SIMILARITY_STRICT,
+ PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1514,7 +1518,7 @@ strict_word_similarity_dist_op(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
- WORD_SIMILARITY_STRICT);
+ WORD_SIMILARITY_STRICT, PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
@@ -1530,7 +1534,7 @@ strict_word_similarity_dist_commutator_op(PG_FUNCTION_ARGS)
res = calc_word_similarity(VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2),
VARDATA_ANY(in1), VARSIZE_ANY_EXHDR(in1),
- WORD_SIMILARITY_STRICT);
+ WORD_SIMILARITY_STRICT, PG_GET_COLLATION());
PG_FREE_IF_COPY(in1, 0);
PG_FREE_IF_COPY(in2, 1);
diff --git a/contrib/pg_trgm/trgm_regexp.c b/contrib/pg_trgm/trgm_regexp.c
index b4eaeec6090..b4086a75e17 100644
--- a/contrib/pg_trgm/trgm_regexp.c
+++ b/contrib/pg_trgm/trgm_regexp.c
@@ -479,11 +479,11 @@ typedef struct
/* prototypes for private functions */
static TRGM *createTrgmNFAInternal(regex_t *regex, TrgmPackedGraph **graph,
- MemoryContext rcontext);
+ MemoryContext rcontext, Oid collation);
static void RE_compile(regex_t *regex, text *text_re,
int cflags, Oid collation);
-static void getColorInfo(regex_t *regex, TrgmNFA *trgmNFA);
-static int convertPgWchar(pg_wchar c, trgm_mb_char *result);
+static void getColorInfo(regex_t *regex, TrgmNFA *trgmNFA, Oid collation);
+static int convertPgWchar(pg_wchar c, trgm_mb_char *result, Oid collation);
static void transformGraph(TrgmNFA *trgmNFA);
static void processState(TrgmNFA *trgmNFA, TrgmState *state);
static void addKey(TrgmNFA *trgmNFA, TrgmState *state, TrgmStateKey *key);
@@ -551,7 +551,7 @@ createTrgmNFA(text *text_re, Oid collation,
REG_ADVANCED | REG_NOSUB, collation);
#endif
- trg = createTrgmNFAInternal(®ex, graph, rcontext);
+ trg = createTrgmNFAInternal(®ex, graph, rcontext, collation);
/* Clean up all the cruft we created (including regex) */
MemoryContextSwitchTo(oldcontext);
@@ -565,7 +565,7 @@ createTrgmNFA(text *text_re, Oid collation,
*/
static TRGM *
createTrgmNFAInternal(regex_t *regex, TrgmPackedGraph **graph,
- MemoryContext rcontext)
+ MemoryContext rcontext, Oid collation)
{
TRGM *trg;
TrgmNFA trgmNFA;
@@ -573,7 +573,7 @@ createTrgmNFAInternal(regex_t *regex, TrgmPackedGraph **graph,
trgmNFA.regex = regex;
/* Collect color information from the regex */
- getColorInfo(regex, &trgmNFA);
+ getColorInfo(regex, &trgmNFA, collation);
#ifdef TRGM_REGEXP_DEBUG
printSourceNFA(regex, trgmNFA.colorInfo, trgmNFA.ncolors);
@@ -762,7 +762,7 @@ RE_compile(regex_t *regex, text *text_re, int cflags, Oid collation)
* Fill TrgmColorInfo structure for each color using regex export functions.
*/
static void
-getColorInfo(regex_t *regex, TrgmNFA *trgmNFA)
+getColorInfo(regex_t *regex, TrgmNFA *trgmNFA, Oid collation)
{
int colorsCount = pg_reg_getnumcolors(regex);
int i;
@@ -807,7 +807,7 @@ getColorInfo(regex_t *regex, TrgmNFA *trgmNFA)
for (j = 0; j < charsCount; j++)
{
trgm_mb_char c;
- int clen = convertPgWchar(chars[j], &c);
+ int clen = convertPgWchar(chars[j], &c, collation);
if (!clen)
continue; /* ok to ignore it altogether */
@@ -827,7 +827,7 @@ getColorInfo(regex_t *regex, TrgmNFA *trgmNFA)
* byte length.
*/
static int
-convertPgWchar(pg_wchar c, trgm_mb_char *result)
+convertPgWchar(pg_wchar c, trgm_mb_char *result, Oid collation)
{
/* "s" has enough space for a multibyte character and a trailing NUL */
char s[MAX_MULTIBYTE_CHAR_LEN + 1];
@@ -860,7 +860,7 @@ convertPgWchar(pg_wchar c, trgm_mb_char *result)
*/
#ifdef IGNORECASE
{
- char *lowerCased = str_tolower(s, clen, DEFAULT_COLLATION_OID);
+ char *lowerCased = str_tolower(s, clen, collation);
if (strcmp(lowerCased, s) != 0)
{
--
2.51.0
