From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Subject: Re: insensitive collations
Date: Wed, 24 Mar 2021 13:41:38 -0700 (MST)
Message-ID: <1616618498263-0.post@n3.nabble.com> (raw)
In-Reply-To: <1616528764665-0.post@n3.nabble.com>
References: <1ccc668f-4cbc-0bef-af67-450b47cdfee7@2ndquadrant.com>
<cdd0ed2e-1d98-6fc4-07f1-23f19f53b201@2ndquadrant.com>
<5c7f51b0-6788-4ffd-b9c4-23b710373140@manitou-mail.org>
<fd54ad77-fb1a-c77d-1371-d7850562b080@2ndquadrant.com>
<440ec733-d068-46d3-9756-f5f692aba7c8@manitou-mail.org>
<99818c53-d10f-eb0b-bcb1-fd5d60f6dd96@2ndquadrant.com>
<f2b3b8e8-12c4-ceb7-6803-b4af6eb2cb71@2ndquadrant.com>
<693ad06d-db9a-4e59-8131-f823483c5893@manitou-mail.org>
<1616528764665-0.post@n3.nabble.com>
For a UTF8 encoded, case-insensitive (nondeterministic), accent-sensitive ICU
collation, a LIKE predicate can be used with a small transformation of the
predicate, and the pattern can contain multi-byte characters:
from:
SELECT * FROM locations WHERE location LIKE 'midi-Pyrené%';
-- ERROR: nondeterministic collations are not supported for LIKE
to:
SELECT * FROM locations WHERE lower(location) COLLATE "C" LIKE
lower('midi-Pyrené%');
and if there is an index defined as follows:
CREATE INDEX location_LOWER_C_index ON
locations (LOWER(location) COLLATE "C");
then the LIKE predicate above performs the desired CI_AS evaluation and also
exploits the index:
EXPLAIN VERBOSE SELECT * FROM locations WHERE LOWER(location) COLLATE "C"
LIKE LOWER('midi-Pyrené%');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using location_LOWER_C_index on public.locations
(cost=0.13..8.16 rows=1 width=18)
Output: location
Index Cond: (((lower((locations.location)::text))::text >=
'midi-pyren?'::text) AND ((lower((locations.location)::text))::text <
'midi-pyren?'::text))
Filter: ((lower((locations.location)::text))::text ~~
'midi-pyren?%'::text)
(4 rows)
It turns out that CI_AS represents the vast majority (more than 99.9%) of
nondeterministic collations that we are seeing in babelfish, because
SQL_Latin1_General_CP1_CI_AS is the default collation in SQL Server.
Currently nondeterministic collations are disabled at the database level.
The cited reason was because of the lack of LIKE support and because certain
catalog views use LIKE. That may still need to be a limitation if those
LIKE predicates currently have an index exploitation unless we can create a
LOWER(col) COLLATE "C" index on the catalog. Which catalog views were these
that had the problem?
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jfinnert@amazon.com
Subject: Re: insensitive collations
In-Reply-To: <1616618498263-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox