Horton

Re: Built-in CTYPE provider

Jeremy Schneider <schneider@ardentperf.com>

View thread View on postgr.es
From: Jeremy Schneider <schneider@ardentperf.com>
To: pgsql-hackers@lists.postgresql.org, "Davis, Jeff" <jefdavj@amazon.com>
Date: 2023-12-20T23:47:51Z
Lists: pgsql-hackers 
On 12/5/23 3:46 PM, Jeff Davis wrote:
> CTYPE, which handles character classification and upper/lowercasing
> behavior, may be simpler than it first appears. We may be able to get
> a net decrease in complexity by just building in most (or perhaps all)
> of the functionality.
> 
> === Character Classification ===
> 
> Character classification is used for regexes, e.g. whether a character
> is a member of the "[[:digit:]]" ("\d") or "[[:punct:]]"
> class. Unicode defines what character properties map into these
> classes in TR #18 [1], specifying both a "Standard" variant and a
> "POSIX Compatible" variant. The main difference with the POSIX variant
> is that symbols count as punctuation.
> 
> === LOWER()/INITCAP()/UPPER() ===
> 
> The LOWER() and UPPER() functions are defined in the SQL spec with
> surprising detail, relying on specific Unicode General Category
> assignments. How to map characters seems to be left (implicitly) up to
> Unicode. If the input string is normalized, the output string must be
> normalized, too. Weirdly, there's no room in the SQL spec to localize
> LOWER()/UPPER() at all to handle issues like [1]. Also, the standard
> specifies one example, which is that "ß" becomes "SS" when folded to
> upper case. INITCAP() is not in the SQL spec.

I'll be honest, even though this is primarily about CTYPE and not
collation, I still need to keep re-reading the initial email slowly to
let it sink in and better understand it... at least for me, it's complex
to reason through. 🙂

I'm trying to make sure I understand clearly what the user impact/change
is that we're talking about: after a little bit of brainstorming and
looking through the PG docs, I'm actually not seeing much more than
these two things you've mentioned here: the set of regexp_* functions PG
provides, and these three generic functions. That alone doesn't seem
highly concerning.

I haven't checked the source code for the regexp_* functions yet, but
are these just passing through to an external library? Are we actually
able to easily change the CTYPE provider for them? If nobody
knows/replies then I'll find some time to look.

One other thing that comes to mind: how does the parser do case folding
for relation names? Is that using OS-provided libc as of today? Or did
we code it to use ICU if that's the DB default? I'm guessing libc, and
global catalogs probably need to be handled in a consistent manner, even
across different encodings.

(Kindof related... did you ever see the demo where I create a user named
'🏃' and then I try to connect to a database with non-unicode encoding?
💥😜  ...at least it seems to be able to walk the index without decoding
strings to find other users - but the way these global catalogs work
scares me a little bit)

-Jeremy


-- 
http://about.me/jeremy_schneider

Commits

  1. Support PG_UNICODE_FAST locale in the builtin collation provider.

  2. Support Unicode full case mapping and conversion.

  3. Fix test failures when language environment is not UTF-8.

  4. Add unicode_strtitle() for Unicode Default Case Conversion.

  5. Use version for builtin collations.

  6. Fix convert_case(), introduced in 5c40364dd6.

  7. Inline basic UTF-8 functions.

  8. Support C.UTF-8 locale in the new builtin collation provider.

  9. Fix another warning, introduced by 846311051e.

  10. Address more review comments on commit 2d819a08a1.

  11. Fix unreachable code warning from commit 2d819a08a1.

  12. Introduce "builtin" collation provider.

  13. Catalog changes preparing for builtin collation provider.

  14. Unicode case mapping tables and functions.

  15. Add Unicode property tables.

  16. Documentation update for Standard Collations.

  17. Cleanup for unicode-update build target and test.

  18. Shrink Unicode category table.

  19. Make some error strings more generic

  20. pg_upgrade: copy locale and encoding information to new cluster.

  21. Update Unicode data to Unicode 15.0.0

  22. Create a new type category for "internal use" types.