> With the latest version, an XX000 cache lookup can surface to the user
> with two columns with different collations:

Good catch! This happened already before rebasing.

> Minimal repro:
> 
> CREATE EXTENSION IF NOT EXISTS pg_trgm;
> DROP TABLE IF EXISTS ic_a, ic_b;
> CREATE TABLE ic_a(x text COLLATE "C");
> CREATE TABLE ic_b(y text COLLATE "POSIX");
> INSERT INTO ic_a VALUES ('hello world');
> INSERT INTO ic_b VALUES ('hello word');
> SELECT similarity(x, y) FROM ic_a, ic_b;
> -- ERROR:  XX000: cache lookup failed for collation 0

The root cause is that PG_GET_COLLATION() returns InvalidOid when the
parser can't resolve a conflict of implicit collations. Functions with
two or more collatable arguments are expected to check for that
themselves (like varstr_cmp(), LIKE, ...) and report
ERRCODE_INDETERMINATE_COLLATION. The new code passed it straight to
pg_newlocale_from_collation(InvalidOid), which throws

XX000: cache lookup failed for collation 0.

I fixed this in attached v9. The fix is folded into patch 0001, since
that's the commit that first made the error reachable. I placed the test
in pg_trgm.sql rather than pg_trgm_collation.sql since the latter is
skipped on non-ICU builds, and C/POSIX exist everywhere.

-- 
David Geier
From a47fc861a372f665fe4c18a7b72f8939aa506af3 Mon Sep 17 00:00:00 2001
From: David Geier <[email protected]>
Date: Wed, 21 Jan 2026 14:54:28 +0100
Subject: [PATCH v9 1/2] 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 +-
 contrib/pg_trgm/expected/pg_trgm.out          | 14 ++++
 .../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.sql               |  8 ++
 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                     | 78 ++++++++++++-------
 contrib/pg_trgm/trgm_regexp.c                 | 20 ++---
 12 files changed, 172 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.out 
b/contrib/pg_trgm/expected/pg_trgm.out
index 612625f1fda..8a5f6e93ad8 100644
--- a/contrib/pg_trgm/expected/pg_trgm.out
+++ b/contrib/pg_trgm/expected/pg_trgm.out
@@ -5448,3 +5448,17 @@ SELECT DISTINCT city, similarity(city, 'Warsaw'), 
show_limit()
  Warsaw |          1 |        0.5
 (1 row)
 
+-- Check that conflicting implicit collations are reported properly
+CREATE TEMP TABLE ic_a(x text COLLATE "C");
+CREATE TEMP TABLE ic_b(y text COLLATE "POSIX");
+INSERT INTO ic_a VALUES ('hello world');
+INSERT INTO ic_b VALUES ('hello word');
+SELECT similarity(x, y) FROM ic_a, ic_b;
+ERROR:  could not determine which collation to use for string similarity
+HINT:  Use the COLLATE clause to set the collation explicitly.
+SELECT similarity(x, y COLLATE "C") FROM ic_a, ic_b;
+ similarity 
+------------
+   0.642857
+(1 row)
+
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.sql b/contrib/pg_trgm/sql/pg_trgm.sql
index 49db86caf7d..fc5e7d6093d 100644
--- a/contrib/pg_trgm/sql/pg_trgm.sql
+++ b/contrib/pg_trgm/sql/pg_trgm.sql
@@ -244,3 +244,11 @@ SELECT DISTINCT city, similarity(city, 'Warsaw'), 
show_limit()
 SELECT set_limit(0.5);
 SELECT DISTINCT city, similarity(city, 'Warsaw'), show_limit()
   FROM restaurants WHERE city % 'Warsaw';
+
+-- Check that conflicting implicit collations are reported properly
+CREATE TEMP TABLE ic_a(x text COLLATE "C");
+CREATE TEMP TABLE ic_b(y text COLLATE "POSIX");
+INSERT INTO ic_a VALUES ('hello world');
+INSERT INTO ic_b VALUES ('hello word');
+SELECT similarity(x, y) FROM ic_a, ic_b;
+SELECT similarity(x, y COLLATE "C") FROM ic_a, ic_b;
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 dfa2f968d77..42e8516f606 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..f856913df92 100644
--- a/contrib/pg_trgm/trgm_op.c
+++ b/contrib/pg_trgm/trgm_op.c
@@ -66,6 +66,24 @@ typedef uint8 TrgmBound;
 #define WORD_SIMILARITY_STRICT         0x02    /* force bounds of extent to 
match
                                                                                
         * word bounds */
 
+/*
+ * Return the collation to use for a similarity function call.  If the
+ * parser could not resolve a conflict of implicit collations, InvalidOid
+ * is passed; report an error in that case, like other collation-dependent
+ * functions.
+ */
+static Oid
+similarity_get_collation(FunctionCallInfo fcinfo)
+{
+       if (!OidIsValid(fcinfo->fncollation))
+               ereport(ERROR,
+                               (errcode(ERRCODE_INDETERMINATE_COLLATION),
+                                errmsg("could not determine which collation to 
use for string similarity"),
+                                errhint("Use the COLLATE clause to set the 
collation explicitly.")));
+
+       return fcinfo->fncollation;
+}
+
 /*
  * A growable array of trigrams
  *
@@ -490,7 +508,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 +560,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 +614,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 +875,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 +893,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 +1104,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 +1136,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 +1195,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 +1362,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), 
similarity_get_collation(fcinfo));
+       trg2 = generate_trgm(VARDATA_ANY(in2), VARSIZE_ANY_EXHDR(in2), 
similarity_get_collation(fcinfo));
 
        res = cnt_sml(trg1, trg2, false);
 
@@ -1366,7 +1384,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, 
similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1382,7 +1400,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, similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1392,9 +1410,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,
+                                                                               
                                         similarity_get_collation(fcinfo),
+                                                                               
                                         PG_GETARG_DATUM(0),
+                                                                               
                                         PG_GETARG_DATUM(1)));
 
        PG_RETURN_FLOAT4(1.0 - res);
 }
@@ -1402,9 +1421,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,
+                                                                               
                                         similarity_get_collation(fcinfo),
+                                                                               
                                         PG_GETARG_DATUM(0),
+                                                                               
                                         PG_GETARG_DATUM(1)));
 
        PG_RETURN_BOOL(res >= similarity_threshold);
 }
@@ -1418,7 +1438,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, similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1434,7 +1454,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, similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1450,7 +1470,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, 
similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1466,7 +1486,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, 
similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1482,7 +1502,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,
+                                                          
similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1498,7 +1519,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,
+                                                          
similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1514,7 +1536,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, similarity_get_collation(fcinfo));
 
        PG_FREE_IF_COPY(in1, 0);
        PG_FREE_IF_COPY(in2, 1);
@@ -1530,7 +1552,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, similarity_get_collation(fcinfo));
 
        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 b3eca178c5a..0e72efa0378 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(&regex, graph, rcontext);
+       trg = createTrgmNFAInternal(&regex, 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;
@@ -806,7 +806,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 */
@@ -826,7 +826,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];
@@ -859,7 +859,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.55.0

From c85ff3b95ce699609d2931432e88073afd83c74e Mon Sep 17 00:00:00 2001
From: David Geier <[email protected]>
Date: Fri, 23 Jan 2026 15:39:06 +0100
Subject: [PATCH v9 2/2] 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 f856913df92..49d7dc59014 100644
--- a/contrib/pg_trgm/trgm_op.c
+++ b/contrib/pg_trgm/trgm_op.c
@@ -353,7 +353,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;
@@ -362,7 +362,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;
        }
@@ -375,7 +375,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;
        }
@@ -551,7 +551,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;
 
@@ -968,7 +968,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;
@@ -991,7 +991,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;
@@ -1002,7 +1002,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;
@@ -1040,7 +1040,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;
@@ -1067,7 +1067,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;
@@ -1131,7 +1131,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 0e72efa0378..dd49e154331 100644
--- a/contrib/pg_trgm/trgm_regexp.c
+++ b/contrib/pg_trgm/trgm_regexp.c
@@ -810,7 +810,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.55.0

Reply via email to