Subject: Re: Add CASEFOLD() function.

From: Vik Fearing <[email-masked]>
Date: Tue, 17 Jun 2025 17:37:37 +0200

On 16/12/2024 18:49, Jeff Davis wrote:
> One question I have is whether we want this function to normalize the
> output.

Yes, we do.

I am sorry that I am so late to the party, but I am currently writing 
the Change Proposal for the SQL Standard for this function.

For <fold> (which includes LOWER() and UPPER()), the text says in 
Section 6.35 GR 7.e:

If the character set of <character factor> is UTF8, UTF16, or UTF32, 
then FR is replaced by
     Case:
         i) If the <search condition> S IS NORMALIZED evaluates to True, 
then NORMALIZE (FR)
         ii) Otherwise, FR.

Here, FR is the result of the function and S is its argument.

It does not appear to me that our LOWER and UPPER functions obey this 
rule, so there is a valid argument that we should continue to ignore it. 
Or, we can say that we have at least one of three compliant.

-- 

Vik Fearing

---

From: Jeff Davis <[email-masked]>
Date: Tue, 17 Jun 2025 11:14:58 -0700

On Tue, 2025-06-17 at 17:37 +0200, Vik Fearing wrote:
>          i) If the <search condition> S IS NORMALIZED evaluates to
> True, then NORMALIZE (FR)
>          ii) Otherwise, FR.

I read that as "if the input is normalized, then the output should be
normalized", IOW preserve the normalization. But does it mean "preserve
whatever the input normal form is" or "preserve NFC if the input is
NFC, otherwise the normalization is undefined"?

The above wording seems to mean "preserve NFC if the input is NFC",
because that's what NORMALIZE(FR) does when the normal form is
unspecified.

> rule,

You are correct:

   WITH s(t) AS
   (SELECT NORMALIZE(U&'\00C1\00DF\0301' COLLATE "en-US-x-icu"))
   SELECT UPPER(t) = NORMALIZE(UPPER(t)) FROM s;
    ?column? 
   ----------
    f

>  so there is a valid argument that we should continue to ignore it.

What do other databases do?

Given how costly normalization can be, imposing that on every caller
seems like a bit much. And favoring NFC for the user unconditionally
might not be the best thing. Then again, NFC is good most of the time,
and there are patches to speed up normalization.

I tend to think that a lot of users who want casefolding would also
want normalization, but it's hard to weigh that against the performance
cost. It might not matter outside of a few edge cases, though I'm not
sure exactly how many.

Regards,
	Jeff Davis

---

From: Vik Fearing <[email-masked]>
Date: Wed, 18 Jun 2025 19:09:04 +0200

> because that's what NORMALIZE(FR) does when the normal form is

Yes, and that is also the default for <normalized predicate>.

>     SELECT UPPER(t) = NORMALIZE(UPPER(t)) FROM s;

I don't know.  I am just pointing out what the Standard says.  I think 
we should either comply, or say that we don't do it for LOWER and UPPER 
so let's keep things implementation-consistent.

> seems like a bit much.

How much does it cost to check for NFC?  I honestly don't know the 
answer to that question, but that is the only case where we need to 
maintain normalization.

> And favoring NFC for the user unconditionally

It's not unconditionally, it's only if the input was NFC.

> cost. It might not matter outside of a few edge cases, though I'm not

I defer to you and others in the thread to make this decision.

-- 

Vik Fearing

---

From: Jeff Davis <[email-masked]>
Date: Wed, 18 Jun 2025 19:53:01 -0700

> so let's keep things implementation-consistent.

For the standard, I see two potential philosophies:

I. CASEFOLD() is another variant of LOWER()/UPPER(), and it should
preserve NFC in the same way.

II. CASEFOLD() is not like LOWER()/UPPER(); it returns a semi-opaque
text value that is useful for caseless matching, but should not
ordinarily be used for display or sent to the application (those things
would be allowed, just not encouraged). For normalization, either:
  (A) Follow Unicode Default Caseless Matching (16.0 3.13.5 D144), and
don't require any kind of normalization; or
  (B) Follow Unicode Canonical Caseless Matching (D145), and require
that the input and output are normalized appropriately, but leave the
precise normal form as implementation-defined.

The current implementation could either be seen as philosophy (I) where
we've chosen to ignore the normalization part for the sake of
consistency with LOWER()/UPPER(); or it could be seen as philosophy
(II)(A).

> answer to that question, but that is the only case where we need to 

I attached a very rough patch and ran a very simple test on strings
averaging 36 bytes in length, all already in NFC and the result is also
NFC. Before the patch, doing a CASEFOLD() on 10M tuples took about 3
seconds, afterward about 8.

There's a patch to optimize some of the normalization paths, which I
haven't had a chance to review yet. So those numbers might come down. 

> It's not unconditionally, it's only if the input was NFC.

Optimizing the case where the input is _not_ NFC seems strange to me.
If we are normalizing the output, I'd say we should just make the
output always NFC. Being more strict, this seems likely to comply with
the eventual standard.

Additionally, if we are normalizing the output, then we should also do
the input fixup for U+0345, which would make the result usable for
Canonical Caseless Matching. Again, this seems likely to comply with
the eventual standard.

So I only see two reasonable implementations:

1. The current CASEFOLD() implementation.

2. Do the input fixup for U+0345 and unconditionally normalize the
output in NFC.

If there's a case to be made for both implementations, we could also
consider having two functions, say, CASEFOLD() for #1 and NCASEFOLD()
for #2. I'm not sure whether we'd want to standardize one or both of
those functions.

And if you think there's likely to be a collision with the standard
that's hard to anticipate and fix now, then we should consider
reverting CASEFOLD() for 18 and wait for more progress on the
standardization. What's the likelihood that the name changes or
something like that?

Regards,
	Jeff Davis

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 5bd1e01f7e4..12e688acec6 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -79,6 +79,7 @@
 #include "common/int.h"
 #include "common/unicode_case.h"
 #include "common/unicode_category.h"
+#include "common/unicode_norm.h"
 #include "mb/pg_wchar.h"
 #include "nodes/miscnodes.h"
 #include "parser/scansup.h"
@@ -1866,6 +1867,9 @@ str_casefold(const char *buff, size_t nbytes, Oid collid)
 		size_t		dstsize;
 		char	   *dst;
 		size_t		needed;
+		int mblen, i;
+		unsigned char *p;
+		pg_wchar   *decoded;
 
 		/* first try buffer of equal size plus terminating NUL */
 		dstsize = srclen + 1;
@@ -1882,7 +1886,54 @@ str_casefold(const char *buff, size_t nbytes, Oid collid)
 		}
 
 		Assert(dst[needed] == '\0');
-		result = dst;
+
+		/* convert to pg_wchar */
+		mblen = pg_mbstrlen_with_len(dst, needed);
+		decoded = palloc((mblen + 1) * sizeof(pg_wchar));
+		p = (unsigned char *) dst;
+		for (i = 0; i < mblen; i++)
+		{
+			decoded[i] = utf8_to_unicode(p);
+			p += pg_utf_mblen(p);
+		}
+		decoded[i] = (pg_wchar) '\0';
+
+		if (unicode_is_normalized_quickcheck(UNICODE_NFC, decoded) == UNICODE_NORM_QC_YES)
+		{
+			pfree(decoded);
+			result = dst;
+		}
+		else
+		{
+			pg_wchar *normalized;
+			unsigned char *normalized_utf8;
+
+			normalized = unicode_normalize(UNICODE_NFC, decoded);
+			pfree(decoded);
+
+			/* convert back to UTF-8 string */
+			mblen = 0;
+			for (pg_wchar *wp = normalized; *wp; wp++)
+			{
+				unsigned char buf[4];
+
+				unicode_to_utf8(*wp, buf);
+				mblen += pg_utf_mblen(buf);
+			}
+
+			normalized_utf8 = palloc(mblen + 1);
+
+			p = normalized_utf8;
+			for (pg_wchar *wp = normalized; *wp; wp++)
+			{
+				unicode_to_utf8(*wp, p);
+				p += pg_utf_mblen(p);
+			}
+			*p = '\0';
+			pfree(normalized);
+
+			result = (char *) normalized_utf8;
+		}
 	}
 
 	return result;

---

From: Thom Brown <[email-masked]>
Date: Thu, 19 Jun 2025 05:03:35 +0100

> standardization. What's the likelihood that the name changes or

Late to the party, but is there an argument for porting this to the citext
type? Or supplementing the extension with an additional type ("cftext"?
*shrug*). It currently uses lower(), so our current recommendation for
dealing with all unicode characters is to use nondeterministic collations.

Thom

---

From: Jeff Davis <[email-masked]>
Date: Wed, 18 Jun 2025 21:26:23 -0700

On Thu, 2025-06-19 at 05:03 +0100, Thom Brown wrote:
> Late to the party, but is there an argument for porting this to the
> citext type? Or supplementing the extension with an additional type
> ("cftext"? *shrug*).

CASEFOLD() addresses a lot of the problems with using LOWER(), so that
sounds like a good idea. I'd be interested to hear from users of
citext.

Regards,
	Jeff Davis

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 19 Jun 2025 16:47:10 +0200

> Or, we can say that we have at least one of three compliant.

The SQL standard also says in a few other places that normalization 
should be applied, and we do none of those, so this is probably not a 
reason to change CASEFOLD at this point.

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 19 Jun 2025 16:51:02 +0200

On 19.06.25 06:03, Thom Brown wrote:
> ("cftext"? *shrug*). It currently uses lower(), so our current 
> recommendation for dealing with all unicode characters is to use 
> nondeterministic collations.

What is the motivation for wanting a citext variant instead of using 
nondeterministic collations?

---

From: Thom Brown <[email-masked]>
Date: Thu, 19 Jun 2025 16:36:29 +0100

> nondeterministic collations?

Ease of use, perhaps. It seems easier to use:

column_name cftext

rather than:

CREATE COLLATION case_insensitive_collation (
    PROVIDER = icu,
    LOCALE = 'und-u-ks-level2',
    DETERMINISTIC = FALSE
);

column_name text COLLATE case_insensitive_collation

But I see the arguments against it. It creates an unnecessary
dependency on an extension, and if someone wants to ignore both case
and accents, they may resort to using 2 extensions (citext + unaccent)
when none are needed. I guess I don't feel strongly about it either
way.

Thom

---

From: Robert Treat <[email-masked]>
Date: Thu, 19 Jun 2025 12:15:48 -0400

> when none are needed. I guess I don't feel strongly about it either

Don't forget, if you have a defined insensitive / normalized
collations, you also enable on-the-fly collation based matching, a la
"SELECT 'Å' = 'A' COLLATE ignore_accent_case;" regardless of the
provided collations (which I think is much more common certain in
other databases)

Robert Treat
https://xzilla.net

---

From: Vik Fearing <[email-masked]>
Date: Thu, 19 Jun 2025 18:21:09 +0200

On 19/06/2025 16:47, Peter Eisentraut wrote:
>> rule, so there is a valid argument that we should continue to ignore 
>> it. Or, we can say that we have at least one of three compliant.
>

Works for me.

-- 

Vik Fearing.

---

From: Jeff Davis <[email-masked]>
Date: Thu, 19 Jun 2025 09:33:41 -0700

>     LOCALE = 'und-u-ks-level2',

We could auto-create such a collation at initdb time for ICU-enabled
builds.

> when none are needed.

There are at least three ways to do case insensitivity (or other kinds
of equivalence):

* Explicit function calls in queries, as well as index and constraint
definitions. E.g. expression index on LOWER(), queries that explicitly
do "LOWER(x) = ..."

* Wrap those function calls up in a separate data type, like citext.

* Non-deterministic collations.

Given that we have collations, which are a way of organizing alternate
behaviors for existing data types, I'm not sure I see the need for
creating an entirely separate data type.

> I guess I don't feel strongly about it either
> way.

Are you a user of citext? I'm genuinely interested in the use cases,
and whether the separate-data-type approach has merits that are missing
in the other approaches.

Regards,
	Jeff Davis

---

From: Robert Treat <[email-masked]>
Date: Thu, 19 Jun 2025 12:51:05 -0400

> We could auto-create such a collation at initdb time for ICU-enabled

Providing a generic insensitive/non-deterministic collation by default
would solve a number of different use cases, so +1 on the idea from
me.
And TBH I usually build --without-icu but this would likely cause me
to change that.

> and whether the separate-data-type approach has merits that are missing

Yeah, I'd be interested to hear if there is some missing bit that
existing users have concerns over; as a former user of citext, it was
a great workaround at the time, but there are "better ways" to handle
those things now (imho).

Robert Treat
https://xzilla.net

---

From: Jeff Davis <[email-masked]>
Date: Thu, 19 Jun 2025 09:52:47 -0700

> > reason to change CASEFOLD at this point.

Sounds good. We can document compatibility notes around this point.

If normalization becomes important, we can take the time to work out
the performance implications more carefully, and potentially introduce
an NCASEFOLD() if needed.

Regards,
	Jeff Davis

---

From: Thom Brown <[email-masked]>
Date: Thu, 19 Jun 2025 17:59:08 +0100

> and whether the separate-data-type approach has merits that are missing

No. But given the options, I would personally choose nondeterministic
collations now that they are available. I just wish they were more
user-friendly as I suspect the majority of people either won't know about
them, or won't know how to use them. But like you say, maybe having a set
of predefined nd-collections would help. As it stands, I'm just bringing up
the consideration of citext in case it has any value, which it doesn't
appear to. In fact it's probably even an argument to begin the process of
deprecation.

Thom

---

From: "David E. Wheeler" <[email-masked]>
Date: Thu, 19 Jun 2025 13:38:54 -0400

On Jun 19, 2025, at 12:59, Thom Brown <[email-masked]> wrote:

> No. But given the options, I would personally choose nondeterministic collations now that they are available. I just wish they were more user-friendly as I suspect the majority of people either won't know about them, or won't know how to use them.

I suspect there are a lot of uses of citext for databases created before nondeterministic collations existed and people are unaware of them or unclear on the migration path from one to the other, let alone implications for any infrastructure they built around cutest (like function signatures and return values). As long as citext conteinues to be maintained there and there’s no super clear path to migrate, I’d bet good money that few would bother to switch.

Best,

David

---

From: Thom Brown <[email-masked]>
Date: Thu, 19 Jun 2025 19:55:43 +0100

> I suspect there are a lot of uses of citext for databases created before nondeterministic collations existed and people are unaware of them or unclear on the migration path from one to the other, l…

Maybe the citext doc page should explain how to get unhooked from it.
Something like:

ALTER TABLE mytable
  ALTER COLUMN ci_column
  SET DATA TYPE TEXT COLLATE case_insensitive_collation;

or

CREATE DOMAIN ci_text AS text
  COLLATE case_insensitive_collation;

ALTER TABLE mytable
  ALTER COLUMN ci_column
  SET DATA TYPE ci_text;

And because they're binary-compatible, they should also be free. No
doubt a procedure could do this to every instance in the database,
although I guess it gets trickier when it comes to functions that
accept citext as a parameter type, and other similar examples.

Thom