Re: Use correct collation in pg_trgm

From: David Geier <geidav(dot)pg(at)gmail(dot)com>
To: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org, solai v <solai(dot)cdac(at)gmail(dot)com>
Subject: Re: Use correct collation in pg_trgm
Date: 2026-11-16 16:06:45
Message-ID: 5be622c6-72f8-443d-9cbf-da492f6b2c4e@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> 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

Attachment Content-Type Size
v9-0001-Use-correct-collation-for-lowercasing.patch text/plain 22.1 KB
v9-0002-Use-correct-collation-for-finding-word-boundaries.patch text/plain 9.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Matheus Alcantara 2026-11-16 16:20:41 Re: extension_control_path: ignore empty elements and deduplicate directories
Previous Message Dean Rasheed 2026-11-16 16:04:45 Re: [PATCH] Normalize numeric/bigint scientific notation after rounding