| 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 |
| 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 |