Re: Update Unicode data to Unicode 16.0.0

Here is the patch to update the Unicode data to version 16.0.0.

Normally, this would have been routine, but a few months ago there was
some debate about how this should be handled. [0] AFAICT, the consensus
was to go ahead with it, but I just wanted to notify it here to be clear.

[0]:
/message-id/flat/d75d2d0d1d2bd45b2c332c47e3e0a67f0640b49c.camel%40j-davis.com

On 11/11/24 01:27, Peter Eisentraut wrote:
> Here is the patch to update the Unicode data to version 16.0.0.
>
> Normally, this would have been routine, but a few months ago there was
> some debate about how this should be handled. [0] AFAICT, the consensus
> was to go ahead with it, but I just wanted to notify it here to be clear.
>
> [0]:
> /message-id/flat/d75d2d0d1d2bd45b2c332c47e3e0a67f0640b49c.camel%40j-davis.com

I ran a check and found that this patch causes changes in upper casing
of some characters. Repro:

setup
8<-------------
wget https://joeconway.com/presentations/formated-unicode.txt
initdb
psql
CREATE DATABASE builtincoll
LOCALE_PROVIDER builtin
BUILTIN_LOCALE 'C.utf-8'
TEMPLATE template0;
\c builtincoll
CREATE TABLE unsorted_table(strings text);
\copy unsorted_table from formated-unicode.txt (format csv)
VACUUM FREEZE ANALYZE unsorted_table;
8<-------------

8<-------------
-- on master
builtincoll=# WITH t AS (SELECT lower(strings) AS s FROM unsorted_table
ORDER BY 1)
SELECT md5(string_agg(t.s,NULL)) FROM t;
md5
----------------------------------
7ec7f5c2d8729ec960942942bb82aedd
(1 row)

builtincoll=# WITH t AS (SELECT upper(strings) AS s FROM unsorted_table
ORDER BY 1)
SELECT md5(string_agg(t.s,NULL)) FROM t;
md5
----------------------------------
97f83a4d1937aa65bcf8be134bf7b0c4
(1 row)

builtincoll=# WITH t AS (SELECT initcap(strings) AS s FROM
unsorted_table ORDER BY 1)
SELECT md5(string_agg(t.s,NULL)) FROM t;
md5
----------------------------------
8cf65a43affc221f3a20645ef402085e
(1 row)
8<-------------

8<-------------
-- master+patch
builtincoll=# WITH t AS (SELECT lower(strings) AS s FROM unsorted_table
ORDER BY 1)
SELECT md5(string_agg(t.s,NULL)) FROM t;
md5
----------------------------------
7ec7f5c2d8729ec960942942bb82aedd
(1 row)

Time: 19858.981 ms (00:19.859)
builtincoll=# WITH t AS (SELECT upper(strings) AS s FROM unsorted_table
ORDER BY 1)SELECT md5(string_agg(t.s,NULL)) FROM t;
md5
----------------------------------
3055b3d5dff76c8c1250ef500c6ec13f
(1 row)

Time: 19774.467 ms (00:19.774)
builtincoll=# WITH t AS (SELECT initcap(strings) AS s FROM
unsorted_table ORDER BY 1)
SELECT md5(string_agg(t.s,NULL)) FROM t;
md5
----------------------------------
9985acddf7902ea603897cdaccd02114
(1 row)
8<-------------

So both UPPER and INITCAP produce different results unless I am missing
something.

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Mon, 2024-11-11 at 14:52 -0500, Joe Conway wrote:
> On 11/11/24 01:27, Peter Eisentraut wrote:
> > Here is the patch to update the Unicode data to version 16.0.0.
> >
> > Normally, this would have been routine, but a few months ago there was
> > some debate about how this should be handled. [0]  AFAICT, the consensus
> > was to go ahead with it, but I just wanted to notify it here to be clear.
> >
> > [0]:
> > /message-id/flat/d75d2d0d1d2bd45b2c332c47e3e0a67f0640b49c.camel%40j-davis.com
>
> I ran a check and found that this patch causes changes in upper casing
> of some characters.

I want to reiterate what I said in the above thread:
If that means that indexes on strings using the "builtin" collation
provider need to be reindexed after an upgrade, I am very much against it.

From my experiences in the field, I consider this need to rebuild indexes
one of the greatest current problems for the usability of PostgreSQL.
I dare say that most people would prefer living with an outdated Unicode version.

Yours,
Laurenz Albe

On 12.11.24 10:40, Laurenz Albe wrote:
> On Mon, 2024-11-11 at 14:52 -0500, Joe Conway wrote:
>> On 11/11/24 01:27, Peter Eisentraut wrote:
>>> Here is the patch to update the Unicode data to version 16.0.0.
>>>
>>> Normally, this would have been routine, but a few months ago there was
>>> some debate about how this should be handled. [0]  AFAICT, the consensus
>>> was to go ahead with it, but I just wanted to notify it here to be clear.
>>>
>>> [0]:
>>> /message-id/flat/d75d2d0d1d2bd45b2c332c47e3e0a67f0640b49c.camel%40j-davis.com
>>
>> I ran a check and found that this patch causes changes in upper casing
>> of some characters.
>
> I want to reiterate what I said in the above thread:
> If that means that indexes on strings using the "builtin" collation
> provider need to be reindexed after an upgrade, I am very much against it.

The practice of regularly updating the Unicode files is older than the
builtin collation provider. It is similar to updating the time zone
files, the encoding conversion files, the snowball files, etc. We need
to move all of these things forward to keep up with the aspects of the
real world that this data reflects. New features are required to live
in that environment. If a new feature were proposed that would then
require us to stop updating any of these files, we would likely not
accept that, or at least need a very deliberate discussion about that
before the feature is introduced. This was not done here at all. If
this new feature has this hidden requirement, then that feature is not
complete yet, and work should probably continue to make that feature
complete. But that can't take progress in other areas hostage.

On Mon, Nov 11, 2024 at 07:27:53AM +0100, Peter Eisentraut wrote:
> Here is the patch to update the Unicode data to version 16.0.0.
>
> Normally, this would have been routine, but a few months ago there was some
> debate about how this should be handled. [0] AFAICT, the consensus was to
> go ahead with it, but I just wanted to notify it here to be clear.
>
> [0]: /message-id/flat/d75d2d0d1d2bd45b2c332c47e3e0a67f0640b49c.camel%40j-davis.com

Worth noting that unaccent.rules is unchanged after switching to
16.0.0:
cd contrib/unaccent && make update-unicode
--
Michael

On Tue, 2024-11-12 at 10:40 +0100, Laurenz Albe wrote:
> I want to reiterate what I said in the above thread:
> If that means that indexes on strings using the "builtin" collation
> provider need to be reindexed after an upgrade, I am very much
> against it.

How would you feel if there was a better way to "lock down" the
behavior using an extension?

I have a patchset here:

/message-id/78a1b434ff40510dc5aaabe986299a09f4da90cf.camel%40j-davis.com

that changes the implementation of collation and ctype to use method
tables rather than branching, and it also introduces some hooks that
can be used to replace the method tables with whatever you want.

Regards,
Jeff Davis

On Tue, 2024-11-19 at 13:42 -0800, Jeff Davis wrote:
> On Tue, 2024-11-12 at 10:40 +0100, Laurenz Albe wrote:
> > I want to reiterate what I said in the above thread:
> > If that means that indexes on strings using the "builtin" collation
> > provider need to be reindexed after an upgrade, I am very much
> > against it.
>
> How would you feel if there was a better way to "lock down" the
> behavior using an extension?

Better.

> I have a patchset here:
>
> /message-id/78a1b434ff40510dc5aaabe986299a09f4da90cf.camel%40j-davis.com
>
> that changes the implementation of collation and ctype to use method
> tables rather than branching, and it also introduces some hooks that
> can be used to replace the method tables with whatever you want.

That looks like a nice idea, since it obviates the need to build
PostgreSQL yourself if you want to use a non-standard copy of - say -
the ICU library. You still have to build your own ICU library, though.

I had hoped that the builtin provider would remove the need to REINDEX,
but I have given up that hope. Peter's argument is sound from a
conceptual point of view, even though I doubt that the average user
will be able to appreciate it.

Yours,
Laurenz Albe

On Wed, 2024-11-20 at 06:41 +0100, Laurenz Albe wrote:
> That looks like a nice idea, since it obviates the need to build
> PostgreSQL yourself if you want to use a non-standard copy of - say -
> the ICU library.  You still have to build your own ICU library,
> though.

It would work with the builtin provider, too, which would not require
ICU at all.

The idea is that you could build an extension that copies the same
logic for building the Unicode tables that we have in Postgres now,
except that it uses whatever version of the Unicode data files you
want.

If we want it to be targeted more specifically at the builtin provider,
we can make it even simpler by allowing you to just replace the unicode
tables with an extension (rather than the method tables). I'm not 100%
sure what people actually want here, so I'm open to suggestion.

> I had hoped that the builtin provider would remove the need to
> REINDEX,
> but I have given up that hope.  Peter's argument is sound from a
> conceptual point of view, even though I doubt that the average user
> will be able to appreciate it.

I'd like to provide options for all kinds of users and packagers.

Regards,
Jeff Davis

On Fri, 2024-11-15 at 17:09 +0100, Peter Eisentraut wrote:
> The practice of regularly updating the Unicode files is older than
> the
> builtin collation provider.  It is similar to updating the time zone
> files, the encoding conversion files, the snowball files, etc.  We
> need
> to move all of these things forward to keep up with the aspects of
> the
> real world that this data reflects.

Should we consider bundling multiple versions of the generated tables
(header files) along with Postgres?

That would enable a compile-time option to build with an older version
of Unicode if you want, solving the packager concern that Noah raised.
It would also make it easier for people to coordinate the Postgres
version of Unicode and the ICU version of Unicode.

Regards,
Jeff Davis

On Mon, 20 Jan 2025 13:39:35 -0800
Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> On Fri, 2024-11-15 at 17:09 +0100, Peter Eisentraut wrote:
> > The practice of regularly updating the Unicode files is older than
> > the
> > builtin collation provider.  It is similar to updating the time
> > zone files, the encoding conversion files, the snowball files, etc.
> > We need
> > to move all of these things forward to keep up with the aspects of
> > the
> > real world that this data reflects.
>
> Should we consider bundling multiple versions of the generated tables
> (header files) along with Postgres?
>
> That would enable a compile-time option to build with an older version
> of Unicode if you want, solving the packager concern that Noah raised.
> It would also make it easier for people to coordinate the Postgres
> version of Unicode and the ICU version of Unicode.

FWIW, after adding ICU support I personally don't think there's a
pressing need to continue updating the tables anymore. I think ICU is
the best solution for people who need the latest linguistic collation
rules.

On the user side, my main concerns are the same as they've always
been: 100% confidence that Postgres updates will not corrupt any data
or cause incorrect query results, and not being forced to rebuild
everything (or logically copy data to avoid pg_upgrade). I'm at a large
company with many internal devs using Postgres in ways I don't know
about, and many users storing lots of unicode data I don't know about.

I'm working a fair bit with Docker and Kubernetes and CloudNativePG
now, so our builds come through the debian PGDG repo. Bundling multiple
tables doesn't bother me, as long as it's not a precursor to removing
current tables from the debian PGDG builds we consume in the future.

Ironically it's not really an issue yet for us on docker because
support for pg_upgrade is pretty limited at the moment. :) But I
think pg_upgrade support will rapidly improve in docker, and will
become common on large databases.

If Postgres does go the path of multiple tables, does the community
want to accumulate a new set of tables every year? That could add up
quickly. Maybe we don't add new tables every year, but follow the
examples of Oracle and DB2 in accumulating them on a less frequent
basis?

-Jeremy

On Mon, 2025-01-20 at 17:06 -0800, Jeremy Schneider wrote:
> FWIW, after adding ICU support I personally don't think there's a
> pressing need to continue updating the tables anymore.

I agree that it's not a pressing concern.

> If Postgres does go the path of multiple tables, does the community
> want to accumulate a new set of tables every year? That could add up
> quickly. Maybe we don't add new tables every year, but follow the
> examples of Oracle and DB2 in accumulating them on a less frequent
> basis?

Yeah, it would probably be every-other-release or something. By the
time we built up enough versions for someone to worry about, hopefully
we'd have some better systems in place to track versions and migrate
forward.

Regards,
Jeff Davis

On Mon, 2025-01-20 at 17:06 -0800, Jeremy Schneider wrote:
> On the user side, my main concerns are the same as they've always
> been: 100% confidence that Postgres updates will not corrupt any data
> or cause incorrect query results

I'll add that, while 100% may be a good goal, it hasn't been the
standard in the past. You're talking about a new standard of
immutability starting in 18, and as Peter pointed out, I don't think
Unicode updates are the only thing we need to consider.

My personal opinion is that both positions -- to upgrade Unicode or not
-- are a bit exaggerated. On the one hand, there's no urgency to
updating Unicode; but on the other hand, there's not a huge danger, at
least compared with our historical standards.

Regards,
Jeff Davis

On 21.01.25 02:06, Jeremy Schneider wrote:
> FWIW, after adding ICU support I personally don't think there's a
> pressing need to continue updating the tables anymore.

That appears to ignore what these tables are actually used for. They
are used for Unicode normalization, which is used by SCRAM. So in a
slightly hyperbolic sense, keeping these tables updated is
security-relevant. They are also used by psql to determine character
width and format output correctly.

Building a collation provider on this came much later. It was possibly
a mistake how that was done.

On 20.01.25 22:39, Jeff Davis wrote:
> On Fri, 2024-11-15 at 17:09 +0100, Peter Eisentraut wrote:
>> The practice of regularly updating the Unicode files is older than
>> the
>> builtin collation provider.  It is similar to updating the time zone
>> files, the encoding conversion files, the snowball files, etc.  We
>> need
>> to move all of these things forward to keep up with the aspects of
>> the
>> real world that this data reflects.
>
> Should we consider bundling multiple versions of the generated tables
> (header files) along with Postgres?

I wouldn't have a problem with that.

> That would enable a compile-time option to build with an older version
> of Unicode if you want, solving the packager concern that Noah raised.
> It would also make it easier for people to coordinate the Postgres
> version of Unicode and the ICU version of Unicode.

But I don't think it would be a compile-time decision. I think it would
be a run-time selection, similar to the theorized multiple-ICU-versions
feature. (Those two features might even go together, since a given ICU
version also sort of assumes a given Unicode version.)

On Wed, 2025-01-22 at 19:08 +0100, Peter Eisentraut wrote:
> But I don't think it would be a compile-time decision.  I think it
> would
> be a run-time selection, similar to the theorized multiple-ICU-
> versions
> feature.  (Those two features might even go together, since a given
> ICU
> version also sort of assumes a given Unicode version.)

I am trying to get there, and the ctype methods patch is a step in that
direction, but I don't think we will have full the full multi-library-
versions work in v18.

A compile-time option does have a chance for v18, and if that satisfies
the immediate concerns of packagers, then we can still update Unicode
in the default build.

Regards,
Jeff Davis

On Wed, 2025-01-22 at 19:03 +0100, Peter Eisentraut wrote:
> Building a collation provider on this came much later.  It was
> possibly
> a mistake how that was done.

It wasn't a mistake. "Stability within a PG major version" was called a
*benefit* near the top of the first email on the subject[1]. It was
considered a benefit because it offered a level of stability that
neither libc nor ICU could offer. As far as I know, it's still
considered to be a benefit today by more people than not (e.g. [2]).

The concerns about Unicode updates come from a misunderstanding of the
level of stability offered in the past:

* IMMUTABLE was initially a planner concept[3], which is why it didn't
care much about dependence on GUCs for instance.

* Expression / predicate indexes rely on immutability to mean something
more strict, and for that, dependence on GUCs creates a problem[4].
(Also, partitioning.)

* It's hard to make an immutable UDF without a SET search_path clause,
but until version 17, that was such a huge performance hit that it was
not usable in an expression index. There will be a lot of not-truly-
immutable UDFs used in expression indexes for a long time.

* Ordinary text indexes rely on the collation libraries to be stable,
which is hard to control because they could be updated by the OS. It's
barely possible recently to freeze the version of libc[5] without
freezing the whole OS version. And if you do manage to freeze both libc
and ICU, you are risking missed security fixes.

* pg_upgrade implicitly relies on IMMUTABLE to mean something even more
strict: stability across major versions. That's a problem for
expression indexes on functions like NORMALIZE(). And, if using the
optional built-in provider, also a problem for expression indexes on
LOWER(), etc.

At each moment we took steps that made sense at the time and in context
and I am not criticizing any of those steps. The biggest practical
problem was unforseen dramatic changes in glibc that broke a lot of
text indexes. The rest of the problems are a mix of design issues,
feature interactions, and implementation details that were not resolved
before the builtin provider existed and still not resolved today.

I do not accept the premise that there is a problem with the built-in
provider. I didn't throw caution to the wind and neither did the
reviewers: you, Daniel, Jeremy, and I did a ton of work to understand,
mitigate, and document the risks (along with a lot of help from
Thomas's earlier work). Users who opt-in to the built in provider opt-
in to occasional controlled changes according to the rather strict
Unicode stability policies[6]. These policies mitigate risks
dramatically, especially for those using only assigned code points,
which can be checked with the SQL function unicode_assigned().

Regards,
Jeff Davis

[1]
/message-id/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.camel@j-davis.com

[2]
/message-id/3729436.1721322211%40sss.pgh.pa.us

[3]
/message-id/3428810.1721160969%40sss.pgh.pa.us

[4]

CREATE TABLE t(f float4);
CREATE UNIQUE INDEX t_idx ON t((f::text));
SET extra_float_digits = 0;
INSERT INTO t VALUES (1.23456789);
INSERT INTO t VALUES (1.23456789); -- error
SET extra_float_digits = 1;
INSERT INTO t VALUES (1.23456789); -- success

[5] https://github.com/awslabs/compat-collation-for-glibc

[6] https://www.unicode.org/policies/stability_policy.html

On Mon, 2024-11-11 at 07:27 +0100, Peter Eisentraut wrote:
> Here is the patch to update the Unicode data to version 16.0.0.
>
> Normally, this would have been routine, but a few months ago there
> was
> some debate about how this should be handled. [0]  AFAICT, the
> consensus
> was to go ahead with it, but I just wanted to notify it here to be
> clear.

We discussed $SUBJECT at the Developer Meeting before FOSDEM.

Those people who were most concerned about the Unicode updates on the
list were not present, so I don't consider the discussion to be
binding. But the attendees present agreed that:

(a) we should not block the update to Unicode indefinitely; and
(b) we should make reasonable attempts to mitigate potential
problems.

One idea for (b) resurfaced, which was to make a best-effort check at
pg_upgrade time for affected indexes. The check would not be
bulletproof, because we can't catch dependencies that are hidden inside
SPI (e.g. a plpgsql function that calls LOWER()), but it would catch
most potential problems.

Patch attached. A few notes:

* The dependency entries don't exist because LOWER(), etc., are
system objects (pinned); so it queries the indexprs, indpreds,
partexprs, and conbin.
* The query is large and perhaps too clever, but it seems to work. I
tried to add inline comments to the SQL, and pgindent had its own ideas
about how to format them -- suggestions welcome.
* We haven't actually done the Unicode update yet, so it will notice
that the PG17 and PG18 Unicode versions are the same, and return early.
Either apply on top of the Unicode update patch, or comment out the
early return for testing.
* It emits a warning rather than an error, so you need to specify
pg_upgrade with "-r" to see the output file.
* I didn't adapt the query to run on pre-17 versions, even though it
could find some potential problem cases (like an index on NORMALIZE()).
I can add that if someone thinks it's worthwhile.

Regards,
Jeff Davis

On 05.02.25 22:47, Jeff Davis wrote:
> (b) we should make reasonable attempts to mitigate potential
> problems.
>
> One idea for (b) resurfaced, which was to make a best-effort check at
> pg_upgrade time for affected indexes. The check would not be
> bulletproof, because we can't catch dependencies that are hidden inside
> SPI (e.g. a plpgsql function that calls LOWER()), but it would catch
> most potential problems.
>
> Patch attached. A few notes:
>
> * The dependency entries don't exist because LOWER(), etc., are
> system objects (pinned); so it queries the indexprs, indpreds,
> partexprs, and conbin.
> * The query is large and perhaps too clever, but it seems to work. I
> tried to add inline comments to the SQL, and pgindent had its own ideas
> about how to format them -- suggestions welcome.
> * We haven't actually done the Unicode update yet, so it will notice
> that the PG17 and PG18 Unicode versions are the same, and return early.
> Either apply on top of the Unicode update patch, or comment out the
> early return for testing.
> * It emits a warning rather than an error, so you need to specify
> pg_upgrade with "-r" to see the output file.
> * I didn't adapt the query to run on pre-17 versions, even though it
> could find some potential problem cases (like an index on NORMALIZE()).
> I can add that if someone thinks it's worthwhile.

This all looks quite reasonable to me. The code could obviously use a
bit closer inspection, but the way you've structured it looked quite
okay to me.

On Mon, 2024-11-18 at 13:58 +0900, Michael Paquier wrote:
> Worth noting that unaccent.rules is unchanged after switching to
> 16.0.0:
> cd contrib/unaccent && make update-unicode

What diffs are you seeing? I don't see any diffs to unaccent.rules
since Unicode 14.0.0.

Aside: it looks like that target is not being run with the meson build.

Regards,
Jeff Davis

On Fri, 2025-02-14 at 09:02 +0100, Peter Eisentraut wrote:
> This all looks quite reasonable to me.  The code could obviously use
> a
> bit closer inspection, but the way you've structured it looked quite
> okay to me.

Attached a version that rebases both patches. In my patch, I added a
report_status().

Note that the Unicode update has a few test diffs for NORMALIZE(),
please check to see if the tests themselves need an update.

Regards,
Jeff Davis

On Mon, Feb 17, 2025 at 11:39:14AM -0800, Jeff Davis wrote:
> On Mon, 2024-11-18 at 13:58 +0900, Michael Paquier wrote:
>> Worth noting that unaccent.rules is unchanged after switching to
>> 16.0.0:
>> cd contrib/unaccent && make update-unicode
>
> What diffs are you seeing? I don't see any diffs to unaccent.rules
> since Unicode 14.0.0.

My point exactly: unaccent.rules does not change when switching to
Unicode 16.0.0.
--
Michael

On Mon, Feb 17, 2025 at 11:46:43AM -0800, Jeff Davis wrote:
> Attached a version that rebases both patches. In my patch, I added a
> report_status().

I briefly looked at v2-0002, and the UpgradeTask usage looks correct to me.
Did you find it easy enough to use?

+ /*
+ * The builtin provider did not exist prior to version 17. While there are
+ * still problems that could potentially be caught from earlier versions,
+ * such as an index on NORMALIZE(), we don't check for that here.
+ */
+ if (GET_MAJOR_VERSION(cluster->major_version) < 1700)
+ return;

nitpick: In most cases, I think this check is done in
check_and_dump_old_cluster() before actually calling the checking function.
I don't think there's any big problem here, except you might strand the
memory allocated for the task.

+ if (!unicode_version_changed(cluster))
+ {
+ check_ok();
+ return;
+ }

Same nitpick here about stranding the task memory.

+ report_status(PG_WARNING, "warning");
+ pg_log(PG_WARNING, "Your installation contains relations that may be affected by a new version of Unicode.\n"
+ "A list of potentially-affected relations is in the file:\n"
+ " %s", report.path);

This may have been discussed upthread, but is a warning enough? That seems
like something that could very easily be missed.

--
nathan

On Wed, 2025-03-05 at 14:33 -0600, Nathan Bossart wrote:
> +               report_status(PG_WARNING, "warning");
> +               pg_log(PG_WARNING, "Your installation contains
> relations that may be affected by a new version of Unicode.\n"
> +                          "A list of potentially-affected relations
> is in the file:\n"
> +                          "    %s", report.path);
>
> This may have been discussed upthread, but is a warning enough?  That
> seems
> like something that could very easily be missed.

There can be false positives, because even if such an expression index
exists, it's often not an actual problem. Do we want to stop an upgrade
from happening in that case? I doubt it, but if so, we'd need some kind
of option to bypass it.

Regards,
Jeff Davis

On Wed, Mar 05, 2025 at 03:34:06PM -0800, Jeff Davis wrote:
> On Wed, 2025-03-05 at 14:33 -0600, Nathan Bossart wrote:
>> +               report_status(PG_WARNING, "warning");
>> +               pg_log(PG_WARNING, "Your installation contains
>> relations that may be affected by a new version of Unicode.\n"
>> +                          "A list of potentially-affected relations
>> is in the file:\n"
>> +                          "    %s", report.path);
>>
>> This may have been discussed upthread, but is a warning enough?  That
>> seems
>> like something that could very easily be missed.
>
> There can be false positives, because even if such an expression index
> exists, it's often not an actual problem. Do we want to stop an upgrade
> from happening in that case? I doubt it, but if so, we'd need some kind
> of option to bypass it.

I see. Do we provide any suggested next steps for users to assess the
potentially-affected relations?

--
nathan

On Wed, 2025-03-05 at 20:43 -0600, Nathan Bossart wrote:
> I see.  Do we provide any suggested next steps for users to assess
> the
> potentially-affected relations?

I don't know exactly where we should document it, but I've attached a
SQL file that demonstrates what can happen for a PG17->PG18 upgrade,
assuming that we've updated Unicode to 16.0.0 in PG18.

The change in Unicode that I'm focusing on is the addition of U+A7DC,
which is unassigned in Unicode 15.1 and assigned in Unicode 16, which
lowercases to U+019B. The examples assume that the user is using
unassigned code points in PG17/Unicode15.1 and the PG_C_UTF8 collation.

The exmaple table 'test' has a single row with U+A7DC and an index
test_idx on LOWER(t). Because the codepoint is unassigned in PG17,
LOWER(U&'\A7DC') returns itself, while in PG18 it returns U&'\019B'.

The first thing the user should do if they see the warnings during the
--check phase is to rerun with "--check --retain" so they can keep the
file. Maybe that's not great, and we should keep the file regardless
for these particular kinds of warnings.

Then go ahead and upgrade. After upgrade, the user should:

REINDEX INDEX CONCURRENTLY test_idx;

each potentially-affected index. If they have amcheck installed, they
can do:

select bt_index_check(index=>'test_idx', heapallindexed=>true);

first, and only REINDEX if there's an error to fix. Do the same for
other indexes listed in the warning file.

The next example is tuniq, which is similar except that the index is a
unique index, and the table has two values: U&'\019B' and U&'\A7DC'. In
PG17, LOWER() returns U&'\019B' and U&'\A7DC', respectively (the former
because it's already lowercase, and the latter because it's unassigned
and returns itself). In PG18, LOWER() returns U&'\019B' and U&'\019B'.
So, a REINDEX will encounter a uniqueness violation, and the user will
need to decide how to handle the duplicate values.

The next example is tcheck, which does CHECK(LOWER(t) = t), which is
the most plausible check constraint I can think of that would be
affected. In theory, the user might have to reason through what should
be done about a tuple that violates it, but in practice it might be as
simple as:

UPDATE tcheck SET t = LOWER(t) WHERE t <> LOWER(t);

The last example is partitioning where the table is range partitioned
on LOWER(t), and the tuple ends up in the wrong partition on PG18.
After fixing the indexes with:

-- tpart0_lower_idx is fine, no REINDEX needed
select bt_index_check(index=>'tpart0_lower_idx',
heapallindexed=>true);
select bt_index_check(index=>'tpart1_lower_idx',
heapallindexed=>true);
REINDEX INDEX CONCURRENTLY tpart1_lower_idx;

See the problem with:

SET enable_partition_pruning=off;
-- correctly finds one tuple
SELECT * FROM tpart WHERE LOWER(t) = U&'\019B';

SET enable_partition_pruning TO DEFAULT;
-- incorrectly finds zero tuples
SELECT * FROM tpart WHERE LOWER(t) = U&'\019B';

Now move the tuples to the right partitions:

BEGIN;
WITH d AS (
DELETE FROM tpart0
WHERE NOT (LOWER(t) >= U&'\0001' AND LOWER(t) < U&'\8000')
RETURNING *
) INSERT INTO tpart SELECT * FROM d;
WITH d AS (
DELETE FROM tpart1
WHERE NOT (LOWER(t) >= U&'\8000' AND LOWER(t) < U&'\FFFF')
RETURNING *
) INSERT INTO tpart SELECT * FROM d;
COMMIT;

The order of operations should be to fix indexes, unique constraints,
and check constraints first; and then to fix partitioned tables. That
way the above partitioned table queries get correct results using the
indexes.

Granted, this is not a great user experience, and we could do better in
the future (e.g. add some new utility commands). But this is an
improvement in the sense that it's a small number of affected objects,
unlike a collation version change, which affects anything related to
text.

Regards,
Jeff Davis

On 17.02.25 20:46, Jeff Davis wrote:
> Note that the Unicode update has a few test diffs for NORMALIZE(),
> please check to see if the tests themselves need an update.

I think these test result changes are incorrect. AFAICT, nothing has
changed in the upstream data that would explain such a change.

I didn't get such test differences in my original patch. Did you rebase
the patch or regenerate it? Maybe something went wrong there.

On Fri, 07 Mar 2025 13:11:18 -0800
Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> On Wed, 2025-03-05 at 20:43 -0600, Nathan Bossart wrote:
> > I see.  Do we provide any suggested next steps for users to assess
> > the
> > potentially-affected relations?
>
> I don't know exactly where we should document it, but I've attached a
> SQL file that demonstrates what can happen for a PG17->PG18 upgrade,
> assuming that we've updated Unicode to 16.0.0 in PG18.
>
> The change in Unicode that I'm focusing on is the addition of U+A7DC,
> which is unassigned in Unicode 15.1 and assigned in Unicode 16, which
> lowercases to U+019B. The examples assume that the user is using
> unassigned code points in PG17/Unicode15.1 and the PG_C_UTF8
> collation.

It seems the consensus is to update unicode in core... FWIW, I'm still
in favor of leaving it alone because ICU is there for when I need
up-to-date unicode versions.

From my perspective, the whole point of the builtin collation was to
one option that avoids these problems that come with updating both ICU
and glibc.

So I guess the main point of the builtin provider just that it's faster
than ICU?

-Jeremy

On Fri, 2025-03-14 at 23:54 -0700, Jeremy Schneider wrote:
> On Fri, 07 Mar 2025 13:11:18 -0800
> It seems the consensus is to update unicode in core... FWIW, I'm still
> in favor of leaving it alone because ICU is there for when I need
> up-to-date unicode versions.

Me too.

> From my perspective, the whole point of the builtin collation was to
> one option that avoids these problems that come with updating both ICU
> and glibc.

+1

Yours,
Laurenz Albe

On 3/15/25 03:26, Laurenz Albe wrote:
> On Fri, 2025-03-14 at 23:54 -0700, Jeremy Schneider wrote:
>> On Fri, 07 Mar 2025 13:11:18 -0800
>> It seems the consensus is to update unicode in core... FWIW, I'm still
>> in favor of leaving it alone because ICU is there for when I need
>> up-to-date unicode versions.
>
> Me too.

+1

>> From my perspective, the whole point of the builtin collation was to
>> one option that avoids these problems that come with updating both ICU
>> and glibc.
>
> +1

+1

In the long term I think we should figure out how to support newer
versions of unicode for the builtin, but in my mind that might involve
the necessity of supporting multiple versions of unicode such that the
choice remains to remain on the older one.

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Jeremy Schneider <schneider(at)ardentperf(dot)com> writes:
> On Fri, 07 Mar 2025 13:11:18 -0800
> Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> The change in Unicode that I'm focusing on is the addition of U+A7DC,
>> which is unassigned in Unicode 15.1 and assigned in Unicode 16, which
>> lowercases to U+019B. The examples assume that the user is using
>> unassigned code points in PG17/Unicode15.1 and the PG_C_UTF8
>> collation.

> It seems the consensus is to update unicode in core... FWIW, I'm still
> in favor of leaving it alone because ICU is there for when I need
> up-to-date unicode versions.

> From my perspective, the whole point of the builtin collation was to
> one option that avoids these problems that come with updating both ICU
> and glibc.

I don't really buy this argument. If we sit on Unicode 15 until that
becomes untenable, which it will, then people will still be faced
with a behavioral change whenever we bow to reality and invent a
"builtin-2.0" or whatever collation. Moreover, by then they might
well have instances of the newly-assigned code points in their
database, making the changeover real and perhaps painful for them.

On the other hand, if we keep up with the Joneses by updating the
Unicode data, we can hopefully put those behavioral changes into
effect *before* they'd affect any real data. So it seems to me
that freezing our Unicode data is avoiding hypothetical pain now
at the price of certain pain later.

I compare this to our routine timezone data updates, which certainly
have not been without occasional pain ... but does anyone seriously
want to argue that we should still be running tzdata from 20 years
back? Or even 5 years back?

In fact, on the analogy of timezones, I think we should not only
adopt newly-published Unicode versions pretty quickly but push
them into released branches as well. Otherwise the benefit of
staying ahead of real use of the new code points isn't there
for end users.

regards, tom lane

On Sat, 2025-03-15 at 12:15 -0400, Tom Lane wrote:
> On the other hand, if we keep up with the Joneses by updating the
> Unicode data, we can hopefully put those behavioral changes into
> effect *before* they'd affect any real data.

That's a good point.

Regards,
Jeff Davis

On Fri, 2025-03-14 at 23:54 -0700, Jeremy Schneider wrote:
> From my perspective, the whole point of the builtin collation was to
> one option that avoids these problems that come with updating both
> ICU
> and glibc.
>
> So I guess the main point of the builtin provider just that it's
> faster
> than ICU?

It doesn't break primary keys.

Also, it's stable within a major version, we can document and test its
behavior, it solves 99% of the upgrade problem, and what problems
remains are much more manageable.

And yes, collation is way, way faster than ICU.

Regards,
Jeff Davis

> On Mar 15, 2025, at 10:22 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> On Sat, 2025-03-15 at 12:15 -0400, Tom Lane wrote:
>> On the other hand, if we keep up with the Joneses by updating the
>> Unicode data, we can hopefully put those behavioral changes into
>> effect *before* they'd affect any real data.
>
> That's a good point.

Jeff - thanks for the reminder that this is just about character semantics and not ordering. Obviously C collation by definition (code point ordering) doesn’t change sort order… two weeks ago I was working on updating the torture test GitHub page with glibc collation changes up through Ubuntu 24.10 so my mind was definitely over there. No detected changes in en-US so that’s great news. 🙂

Is the simple answer that functions & clauses related to both time zones and character semantics should just all be considered STABLE instead of IMMUTABLE?

I think if that were the case then changes across a minor version would simply be allowed by definition right? No need for warnings.

This would impact the ability to create case-insensitive indexes.

-Jeremy

Sent from my TI-83

On Sat, 8 Mar 2025 at 02:41, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> On Wed, 2025-03-05 at 20:43 -0600, Nathan Bossart wrote:
> > I see. Do we provide any suggested next steps for users to assess
> > the
> > potentially-affected relations?
>
> I don't know exactly where we should document it, but I've attached a
> SQL file that demonstrates what can happen for a PG17->PG18 upgrade,
> assuming that we've updated Unicode to 16.0.0 in PG18.
>
> The change in Unicode that I'm focusing on is the addition of U+A7DC,
> which is unassigned in Unicode 15.1 and assigned in Unicode 16, which
> lowercases to U+019B. The examples assume that the user is using
> unassigned code points in PG17/Unicode15.1 and the PG_C_UTF8 collation.
>
> The exmaple table 'test' has a single row with U+A7DC and an index
> test_idx on LOWER(t). Because the codepoint is unassigned in PG17,
> LOWER(U&'\A7DC') returns itself, while in PG18 it returns U&'\019B'.
>
> The first thing the user should do if they see the warnings during the
> --check phase is to rerun with "--check --retain" so they can keep the
> file. Maybe that's not great, and we should keep the file regardless
> for these particular kinds of warnings.
>
> Then go ahead and upgrade. After upgrade, the user should:
>
> REINDEX INDEX CONCURRENTLY test_idx;
>
> each potentially-affected index. If they have amcheck installed, they
> can do:
>
> select bt_index_check(index=>'test_idx', heapallindexed=>true);
>
> first, and only REINDEX if there's an error to fix. Do the same for
> other indexes listed in the warning file.
>
> The next example is tuniq, which is similar except that the index is a
> unique index, and the table has two values: U&'\019B' and U&'\A7DC'. In
> PG17, LOWER() returns U&'\019B' and U&'\A7DC', respectively (the former
> because it's already lowercase, and the latter because it's unassigned
> and returns itself). In PG18, LOWER() returns U&'\019B' and U&'\019B'.
> So, a REINDEX will encounter a uniqueness violation, and the user will
> need to decide how to handle the duplicate values.
>
> The next example is tcheck, which does CHECK(LOWER(t) = t), which is
> the most plausible check constraint I can think of that would be
> affected. In theory, the user might have to reason through what should
> be done about a tuple that violates it, but in practice it might be as
> simple as:
>
> UPDATE tcheck SET t = LOWER(t) WHERE t <> LOWER(t);
>
> The last example is partitioning where the table is range partitioned
> on LOWER(t), and the tuple ends up in the wrong partition on PG18.
> After fixing the indexes with:
>
> -- tpart0_lower_idx is fine, no REINDEX needed
> select bt_index_check(index=>'tpart0_lower_idx',
> heapallindexed=>true);
> select bt_index_check(index=>'tpart1_lower_idx',
> heapallindexed=>true);
> REINDEX INDEX CONCURRENTLY tpart1_lower_idx;
>
> See the problem with:
>
> SET enable_partition_pruning=off;
> -- correctly finds one tuple
> SELECT * FROM tpart WHERE LOWER(t) = U&'\019B';
>
> SET enable_partition_pruning TO DEFAULT;
> -- incorrectly finds zero tuples
> SELECT * FROM tpart WHERE LOWER(t) = U&'\019B';
>
> Now move the tuples to the right partitions:
>
> BEGIN;
> WITH d AS (
> DELETE FROM tpart0
> WHERE NOT (LOWER(t) >= U&'\0001' AND LOWER(t) < U&'\8000')
> RETURNING *
> ) INSERT INTO tpart SELECT * FROM d;
> WITH d AS (
> DELETE FROM tpart1
> WHERE NOT (LOWER(t) >= U&'\8000' AND LOWER(t) < U&'\FFFF')
> RETURNING *
> ) INSERT INTO tpart SELECT * FROM d;
> COMMIT;
>
> The order of operations should be to fix indexes, unique constraints,
> and check constraints first; and then to fix partitioned tables. That
> way the above partitioned table queries get correct results using the
> indexes.
>
> Granted, this is not a great user experience, and we could do better in
> the future (e.g. add some new utility commands). But this is an
> improvement in the sense that it's a small number of affected objects,
> unlike a collation version change, which affects anything related to
> text.

We currently have two Commitfest entries for the same thread at [1]
and [2]. Are both still necessary, or can we consolidate tracking into
a single entry?
[1] - https://commitfest.postgresql.org/patch/5472/
[2] - https://commitfest.postgresql.org/patch/5563/

Regards,
Vignesh

On Sat, 2025-03-15 at 18:23 -0700, Jeremy Schneider wrote:
> Is the simple answer that functions & clauses related to both time
> zones and character semantics should just all be considered STABLE
> instead of IMMUTABLE?

That was discussed a few times, but:

(a) That doesn't exactly solve the problem, because people still need
indexes on LOWER() or CASEFOLD(); and

(b) If we change IMMUTABLE to mean "returns the same results on every
platform for all time", that would be too strict for many purposes,
like the planner doing constant folding.

I have been thinking about ways we can express the right dependencies,
and I may be making some proposals along those lines.

Regards,
Jeff Davis

On Sun, 2025-03-16 at 19:10 +0530, vignesh C wrote:
> We currently have two Commitfest entries for the same thread at [1]
> and [2]. Are both still necessary, or can we consolidate tracking
> into
> a single entry?

I'm fine removing my CF entry, but unfortunately there's no "withdrawn
-- duplicate", so it might send the wrong message.

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> That was discussed a few times, but:

> (a) That doesn't exactly solve the problem, because people still need
> indexes on LOWER() or CASEFOLD(); and

> (b) If we change IMMUTABLE to mean "returns the same results on every
> platform for all time", that would be too strict for many purposes,
> like the planner doing constant folding.

Yeah. Not only would the set of functions meeting such a standard be
vanishingly small, but so would the set of use-cases. What we need is
some sort of understanding that "this is okay to use in indexes",
"this is okay to constant-fold when planning", etc. Maybe it's less
about "is it okay to just assume this" and more about "can we devise
a method for figuring out when we have to reindex, replan, etc".
We've got bits of that in our collation versioning infrastructure,
but that doesn't cover every source of infrequently-mutating behavior.

> I have been thinking about ways we can express the right dependencies,
> and I may be making some proposals along those lines.

I await a proposal with interest.

regards, tom lane

On Thu, 2025-03-13 at 14:49 +0100, Peter Eisentraut wrote:
> I think these test result changes are incorrect.  AFAICT, nothing has
> changed in the upstream data that would explain such a change.
>
> I didn't get such test differences in my original patch.  Did you
> rebase
> the patch or regenerate it?  Maybe something went wrong there.

Right. The previous version was improperly rebased.

I went back to your original version and rebased over master (including
the latest optimizations for case mapping), and then rebased my changes
on top of that. Attached.

One annoyance with the recent case mapping optimizations is that the
Unicode update diff is larger, similar to the diff for
unicode_norm_hashfunc.h. Previously, it was binary search, so the only
differences were logical differences.

Regards,
Jeff Davis

On Sat, 2025-03-15 at 12:15 -0400, Tom Lane wrote:
> In fact, on the analogy of timezones, I think we should not only
> adopt newly-published Unicode versions pretty quickly but push
> them into released branches as well.

That approach suggests that we consider something like my previous
STRICT_UNICODE proposal[1]. If Postgres updates Unicode quickly enough,
there's not much reason that users would need to use unassigned code
points, so it would be practical to just reject them (as an option).
That would dramatically reduce the practical problems people would
encounter when we do update Unicode.

Note that assigned code points can still change behavior in later
versions, but not in ways that would typically cause a problem for
things like indexes. For instance, U+0363 changed from non-Alphabetic
to Alphabetic in Unicode 16, which changes the results of the
expression:

U&'\0363' ~ '[[:alpha:]]' COLLATE PG_C_UTF8

from false to true, even though U+0363 is assigned in both Unicode
15.1.0 and 16.0.0. That might plausibly matter, but such cases would be
more obscure than case folding.

Regards,
Jeff Davis

[1] https://commitfest.postgresql.org/patch/4876/

On Mon, 17 Mar 2025 at 23:03, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> On Sun, 2025-03-16 at 19:10 +0530, vignesh C wrote:
> > We currently have two Commitfest entries for the same thread at [1]
> > and [2]. Are both still necessary, or can we consolidate tracking
> > into
> > a single entry?
>
> I'm fine removing my CF entry, but unfortunately there's no "withdrawn
> -- duplicate", so it might send the wrong message.

Yes, we don't have "withdrawn duplicate", I have closed it as
withdrawn, anyway we can get it reviewed and committed using the other
entry.

Regards,
Vignesh

On Sat, 2025-03-15 at 10:14 -0400, Joe Conway wrote:
> In the long term I think we should figure out how to support newer
> versions of unicode for the builtin, but in my mind that might
> involve
> the necessity of supporting multiple versions of unicode such that
> the
> choice remains to remain on the older one.

What do you think of Tom's argument that waiting to update Unicode is
what creates the problem in the first place?

"by then they might well have instances of the newly-assigned code
points in their database"[1]

Regards,
Jeff Davis

[1]
/message-id/3481161.1742055336@sss.pgh.pa.us

On Tue, 18 Mar 2025 08:53:56 -0700
Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> What do you think of Tom's argument that waiting to update Unicode is
> what creates the problem in the first place?
>
> "by then they might well have instances of the newly-assigned code
> points in their database"[1]
>
> [1]
> /message-id/3481161.1742055336@sss.pgh.pa.us

Waiting to update Unicode is not what creates the problem, as long as
we support in-place major upgrades without rebuilding indexes.

It seems that we think that it's really uncommon to make indexes on
timezone data. We think that breaking primary keys must definitely be
avoided. We think case-insensitive indexes are probably uncommon, so as
long as its "rare" we can let them break.

I'm not asking for an extreme definition of "IMMUTABLE" but I'd be
very happy with a GUC "data_safety=radical_like_jeremy" where Postgres
simply won't start if the control file says it was from a different
operating system or architecture or ICU/glibc collation version. I can
disable the GUC (like a maintenance mode) to rebuild my indexes and
update my collation versions, and ideally this GUC would also mean that
indexes simply aren't allowed to be created on functions that might
change within the guarantees that are made. (And range-based partitions
can't use them, and FDWs can't rely on them for query planning, etc.)

I get that we don't want to break stuff people are currently doing -
but I think there are a bunch of people who would be happy with an
option to reduce functionality going forward in exchange for clearly
defined behavior. I just don't want to have to think about this on
behalf of all my Postgres users.

-Jeremy

On Tue, Mar 18, 2025 at 11:54 AM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> What do you think of Tom's argument that waiting to update Unicode is
> what creates the problem in the first place?
>
> "by then they might well have instances of the newly-assigned code
> points in their database"[1]

I know you weren't asking me, but FWIW, I think Tom has a point, but I
also don't completely buy it. There's no technological safeguard
against people using Unicode points before they're assigned, so we
can't assume that no one ever does. And they probably do.

I think Joe has the right idea. The way to actually provide the
stability that people want here is to continue supporting old versions
while adding support for new versions. Anything else we do works
subject to assumptions: you can either assume that people don't use
code points before they're assigned (as Tom proposes) or you can
assume that not updating to newer Unicode specs will not cause any
problems for anyone. Joe's proposal is unique in requiring no
assumptions about what users will do or what they care about. It just
works, period. The only disadvantage is that it's more work for us,
but this problem has caused enough suffering over the years that I bet
we can find hackers to implement it and maintain it on an ongoing
basis without great difficulty.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Tue, 2025-03-18 at 09:28 -0700, Jeremy Schneider wrote:
> We think case-insensitive indexes are probably uncommon, so as
> long as its "rare" we can let them break.

Let's define "break" in this context to mean that the constraints are
not enforced, or that the query doesn't return the results that the
user is expecting.

Let's say a user has an index on LOWER(t) in PG17 (Unicode 15.1). Then
Unicode 16.0 comes out, introducing the newly-assigned U+A7DC, which
lowercases to U+019B. The rest of the world moves on and starts using
U+A7DC.

There are only two ways that Postgres can prevent breakage:

1. Update the database to Unicode 16.0 before U+A7DC is encountered, so
that it's properly lowercased to U+019B, and a query on LOWER(t) =
U&'\019B' will correctly return the record containing it.

2. Prevent U+A7DC from going into the database at all.

Continuing on with Unicode 15.1 and accepting the unassigned code point
*cannot* prevent breakage.

A truly paranoid user would want a combination of both solutions:
regular Unicode updates; and something like STRICT_UNICODE
( https://commitfest.postgresql.org/patch/4876/ ) to protect the user
between the time Unicode assigns the code point and the time they can
deploy a version of Postgres that understands it.

You are rightfully concerned that updating Unicode can create its own
inconsistencies, and if nothing is done that can lead to breakage as
well. The upgrade-time check in this thread is one solution to that
problem, but we could do a lot more.

You are also right that we should be more skeptical of an internal
inconsistency (e.g. different results for seqscan vs indexscan) than a
wider definition of inconsistency. But the user created a unicode-based
case-folded index there for a reason, and we shouldn't lose sight of
that.

> I'm not asking for an extreme definition of "IMMUTABLE" but I'd be
> very happy with a GUC "data_safety=radical_like_jeremy" where
> Postgres
> simply won't start if the control file says it was from a different
> operating system or architecture or ICU/glibc collation version. I
> can
> disable the GUC (like a maintenance mode) to rebuild my indexes and
> update my collation versions, and ideally this GUC would also mean
> that
> indexes simply aren't allowed to be created on functions that might
> change within the guarantees that are made. (And range-based
> partitions
> can't use them, and FDWs can't rely on them for query planning, etc.)

Does the upgrade check patch in this thread accomplish that for you? If
not, what else does it need?

It's an upgrade-time check rather than a GUC, but it basically seems to
match what you want. See:

/message-id/16c4e37d4c89e63623b009de9ad6fb90e7456ed8.camel@j-davis.com

Regards,
Jeff Davis

On Tue, Mar 18, 2025 at 2:55 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> Continuing on with Unicode 15.1 and accepting the unassigned code point
> *cannot* prevent breakage.

Under your definition, this is true, but I think Jeremy would define
breakage differently. His primary concern, I expect, is *stability*.
Breakage means that the same supposedly-stable results return
different answers on the same data. Under that definition, continuing
under Unicode 15.1 does prevent breakage.

--
Robert Haas
EDB: http://www.enterprisedb.com

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Mar 18, 2025 at 2:55 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> Continuing on with Unicode 15.1 and accepting the unassigned code point
>> *cannot* prevent breakage.

> Under your definition, this is true, but I think Jeremy would define
> breakage differently. His primary concern, I expect, is *stability*.
> Breakage means that the same supposedly-stable results return
> different answers on the same data. Under that definition, continuing
> under Unicode 15.1 does prevent breakage.

That approach works only if you sit on Unicode 15.1 *forever*.
The impracticality of that seems obvious to me. Sooner or later
you will need to update, and then you are going to suffer pain.
(In the running example of this thread, a unique index on LOWER(t)
might not only be corrupt, but might fail reindex due to the
constraint being violated under the newer rules.) The longer you
wait, the more probable it is that you are going to have problems,
and the more painful it'll be to clean things up.

Now, if you both sit on Unicode 15.1 forever and disallow the
introduction of unassigned-per-15.1 code points, you can escape
that fate, but that approach brings its own kind of pain.

The short answer is that "immutable" = "doesn't change till the heat
death of the universe" is a definition that is not useful when
dealing with this type of data. Other people determine the reality
that you have to deal with.

regards, tom lane

On Tue, Mar 18, 2025 at 3:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> That approach works only if you sit on Unicode 15.1 *forever*.
> The impracticality of that seems obvious to me. Sooner or later
> you will need to update, and then you are going to suffer pain.

I completely agree.

> The short answer is that "immutable" = "doesn't change till the heat
> death of the universe" is a definition that is not useful when
> dealing with this type of data. Other people determine the reality
> that you have to deal with.

I think that's mostly true because of lack of versioning capabilities,
or crappy versioning practices. glibc, AIUI, just disclaims collation
stability: if you're fool enough to sort anything with one of their
collations, that's on you. To me, that seems like an obviously
user-hostile position, as if it were reasonable to suppose that an
algorithm whose whole purpose is to implement a sort order would not
be used for, uh, sorting. Or at least not any sort of sorting where
you don't immediately throw away the results (and then why did you
bother?). ICU doesn't seem to be entirely stable, either. But none of
that means stability isn't a valuable property. It just means people
have done a bad job implementing it. If we give people the ability to
execute operation X using ICU 15.1 or ICU 16.0, they're still
*eventually* going to have to migrate forward to ICU 16.0 or some
later version, because we're probably not going to keep ICU 15.1 until
the heat death of the universe. But we allow people to not have that
update forced upon them at the same time they're trying to change
other things, and that's pretty darn useful. That's why extensions
have separate versioning from the server, for instance.

--
Robert Haas
EDB: http://www.enterprisedb.com

On 3/18/25 16:30, Robert Haas wrote:
> On Tue, Mar 18, 2025 at 3:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> That approach works only if you sit on Unicode 15.1 *forever*.
>> The impracticality of that seems obvious to me. Sooner or later
>> you will need to update, and then you are going to suffer pain.
>
> I completely agree.
>
>> The short answer is that "immutable" = "doesn't change till the heat
>> death of the universe" is a definition that is not useful when
>> dealing with this type of data. Other people determine the reality
>> that you have to deal with.
>
> I think that's mostly true because of lack of versioning capabilities,
> or crappy versioning practices. glibc, AIUI, just disclaims collation
> stability: if you're fool enough to sort anything with one of their
> collations, that's on you. To me, that seems like an obviously
> user-hostile position, as if it were reasonable to suppose that an
> algorithm whose whole purpose is to implement a sort order would not
> be used for, uh, sorting. Or at least not any sort of sorting where
> you don't immediately throw away the results (and then why did you
> bother?). ICU doesn't seem to be entirely stable, either.

Yep

> But none of that means stability isn't a valuable property. It just
> means people have done a bad job implementing it. If we give people
> the ability to execute operation X using ICU 15.1 or ICU 16.0,
> they're still *eventually* going to have to migrate forward to ICU
> 16.0 or some later version, because we're probably not going to keep
> ICU 15.1 until the heat death of the universe. But we allow people
> to not have that update forced upon them at the same time they're
> trying to change other things, and that's pretty darn useful. That's
> why extensions have separate versioning from the server, for
> instance.

+1 Robert articulates my thinking exactly, and much better than I did :-)

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Tue, 2025-03-18 at 14:45 -0400, Robert Haas wrote:
> I think Joe has the right idea. The way to actually provide the
> stability that people want here is to continue supporting old
> versions
> while adding support for new versions. Anything else we do works
> subject to assumptions: you can either assume that people don't use
> code points before they're assigned (as Tom proposes) or you can
> assume that not updating to newer Unicode specs will not cause any
> problems for anyone. Joe's proposal is unique in requiring no
> assumptions about what users will do or what they care about. It just
> works, period. The only disadvantage is that it's more work for us,
> but this problem has caused enough suffering over the years that I
> bet
> we can find hackers to implement it and maintain it on an ongoing
> basis without great difficulty.

I've already put a fair amount of work into this approach, but it is a
lot of work, and I could use some help. Here's a quick dump of some of
my notes on what we can do going forward:

* builtin provider with stable primary keys: done

* collation behavior as method tables: done

* have support for STRICT_UNICODE, or something like it, to allow users
to mitigate their upgrade risks by rejecting unassigned code points: I
submitted a proposal for a database-level option, which got no
discussion

* ctype behavior as method tables: patch written, discussion trailed
off. There was a really minor performance regression, so I held off
committing it, but I don't think it's an actual problem so if people
are in general agreement that we want it then I have no problem
committing it.

* separate "collation provider" from "ctype provider" internally. Have
pg_open_collation() and pg_open_ctype(), and deprecate
pg_newlocale_from_collation(). This is a fair amount of work, but it's
important for dependency and version tracking, as well as an
organizational improvement.

* turn providers into method tables: not too hard. We'd still need to
have the notion of a "provider kind" (builtin, ICU, libc) so that we
know how to interpret the syntax and store things in the other catalogs
(for instance, only ICU accepts ICU_RULES, only libc allows LC_COLLATE
and LC_CTYPE to be different, etc.).

* put providers into new shared catalogs pg_collation_provider and
pg_ctype_provider, which would each have handlers that know how to
instantiate a specific collation or ctype

* add new function markers COLLATE and CTYPE (or some other names),
meaning that the function is sensitive to the collation or ctype of its
arguments.
- for example: LOWER() would be marked CTYPE, ">" would be marked
COLLATE, and "||" wouldn't need any mark.
- When creating some object that has an expression in it, let's say
an index, we already walk the expression and add dependencies on the
functions in the expression. If one of those functions has such a
marker, we would look at the inferred collation of the function, find
its provider, and add a dependency on the provider's shared catalog
entry.
- must work even on "pinned" functions
- queries with ORDER BY, say as part of an MV definition, would be
implicitly treated like functions marked with COLLATE

* (optional) have some kind of runtime check so that UDFs that are
missing the appropraite COLLATE or CTYPE markers figure out that a
collation or ctype is being opened, and throw a WARNING or ERROR

* throw away the idea of collation-speciifc versions, or make it more
of an additional check. Versions would be attached to the provider
entries in the shared catalogs. The only provider that differentiates
collation versions by locale is ICU, and people were highly skeptical
of that before we found bugs in it, and more skeptical afterward. They
will just be a source of confusion in the long term.

* Have some new functions and DDL commands that can find and fix
objects by following the dependency links.

* Allow extensions to be loaded at initdb time, and initialize their
own providers and their own lists of collations.

* Provide a contrib that implements the builtin provider with unicode
15.1.

* If we want multiple versions of a provider in the same running
server, that would take more work. I have my doubts about how many
people would really, actually use that, but it's possible.

I plan to submit some proposals in a few weeks as this CF settles down,
and then have an unconference session on this topic at pgconf.dev. If
anyone is motivated for these problems to be fixed, please jump into
those discussions on list or at the conference, and take on a task or
two.

I am not trying to be dismissive of the concerns raised in this thread,
but I'd like others to understand that what they are asking for is a
lot of work, and that the builtin collation provider solves 99% of it
already. All this effort is to solve that last 1%.

Regards,
Jeff Davis

On Tue, Mar 18, 2025 at 5:09 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> I am not trying to be dismissive of the concerns raised in this thread,
> but I'd like others to understand that what they are asking for is a
> lot of work, and that the builtin collation provider solves 99% of it
> already. All this effort is to solve that last 1%.

I'm not asking you to do the work. It would be great if you did, of
course, but you don't work for me.

But I could not disagree more strongly with the idea that this problem
is 99% solved. That doesn't seem remotely true to me. I'm not sure the
problem is 1% solved.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Tue, 2025-03-18 at 21:34 -0400, Robert Haas wrote:
> But I could not disagree more strongly with the idea that this
> problem
> is 99% solved. That doesn't seem remotely true to me. I'm not sure
> the
> problem is 1% solved.

If we compare the following two problems:

A. With glibc or ICU, every text index, including primary keys, are
highly vulnerable to inconsistencies after an OS upgrade, even if
there's no Postgres upgrade; vs.

B. With the builtin provider, only expression indexes and a few other
things are vulnerable, only during a major version upgrade, and mostly
(but not entirely) when using recently-assigned Cased letters.

To me, problem A seems about 100 times worse than B almost any way I
can imagine measuring it: number of objects vulnerable, severity of the
problem when it does happen, likelihood of a vulnerable object having
an actual problem, etc. If you disagree, I'd like to hear more.

Regards,
Jeff Davis

On Tue, 18 Mar 2025 19:33:00 -0700
Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> If we compare the following two problems:
>
> A. With glibc or ICU, every text index, including primary keys, are
> highly vulnerable to inconsistencies after an OS upgrade, even if
> there's no Postgres upgrade; vs.
>
> B. With the builtin provider, only expression indexes and a few
> other things are vulnerable, only during a major version upgrade, and
> mostly (but not entirely) when using recently-assigned Cased letters.
>
> To me, problem A seems about 100 times worse than B almost any way I
> can imagine measuring it: number of objects vulnerable, severity of
> the problem when it does happen, likelihood of a vulnerable object
> having an actual problem, etc. If you disagree, I'd like to hear more.

Jeff - you and several others have literally put years into making this
better, and it's deeply appreciated. I agree that with the builtin
provider we're in a much better place.

I don't quite understand Tom's argument about why Unicode 15 must
eventually become untenable. Why are we assuming it will? In Oracle's
entire history, I think they have only ever supported four versions of
Unicode. [1] MySQL seems to have added their second only recently. [2]
And again - we have ICU if I need the latest emoji characters. Frankly,
Unicode 15 is pretty good. Most updates to unicode these days are fairly
minor.

Maybe Postgres can be the first database to always ship support for the
latest Unicode with each major version - but I think we should design
that right if we're going to do it. If we just stay on Unicode 15 for
now then there are no problems with case insensitive indexes or range
partitioned tables returning wrong query results after a major version
upgrades.

There's been a lot of discussion about indexes, but this SQL also seems
to work:

postgres=# create table test_events(customer_name text, ts timestamp,
message text) partition by range((lower(customer_name)));

I'm sure that people shouldn't do this ... but if anyone /did/ then it
wouldn't be as simple as an index rebuild after their major version
upgrade.

I had never really considered it before, but this SQL also seems to work

postgres=# create table test_events(id uuid, ts timestamp, message
text) partition by range((ts at time zone 'America/Sao_Paulo'));

I'm sure that people shouldn't do that either ... but if anyone did then
would their rows would be in the wrong partition after they upgraded
from 11.4 to 11.5?

The difficulty here is that I work at a company with thousands of
developers and lots of Postgres and I see people do things all the time
that we might think they "shouldnt" do.

Before we bump the unicode version, perseonally I'd just like to have
some tools to make it so people actually can't do the things they
shouldn't do.

-Jeremy

1:
https://docs.oracle.com/en/database/oracle/oracle-database/23/nlspg/appendix-A-locale-data.html#GUID-CC85A33C-81FC-4E93-BAAB-1B3DB9036060__CIABEDHB

2:
https://dev.mysql.com/blog-archive/mysql-character-sets-unicode-and-uca-compliant-collations/

On Tue, Mar 18, 2025 at 10:33 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> If we compare the following two problems:
>
> A. With glibc or ICU, every text index, including primary keys, are
> highly vulnerable to inconsistencies after an OS upgrade, even if
> there's no Postgres upgrade; vs.
>
> B. With the builtin provider, only expression indexes and a few other
> things are vulnerable, only during a major version upgrade, and mostly
> (but not entirely) when using recently-assigned Cased letters.
>
> To me, problem A seems about 100 times worse than B almost any way I
> can imagine measuring it: number of objects vulnerable, severity of the
> problem when it does happen, likelihood of a vulnerable object having
> an actual problem, etc. If you disagree, I'd like to hear more.

I see your point, but most people don't use the builtin collation
provider. Granted, we could change the default and then more people
would use it, but I'm not sure people would be happy with the
resulting behavior: a lot of people probably want "a" to sort near "á"
even if they don't have strong preferences about the exact details in
every corner case.

Also, and I think rather importantly, many people are less sensitive
to whether anything is actually broken than to whether anything
hypothetically could be broken. When an EDB customer asks "if I do X,
will anything break," it's often the case that answering "maybe" is
the same as answering "yes". The DBA doesn't necessarily know or care
what the application does or know or care what data is in the
database. They want a hard guarantee that the behavior will not
change. From that point of view, your statement that nothing will
change in minor releases when the builtin provider is used is quite
powerful (and a good argument against back-patching Unicode updates as
Tom proposes).

But people will still need to use other collation providers and they
will still need to do major release upgrades and they also want those
things to be guaranteed not to break. Again, I'm not trying to oblige
you to deliver that behavior and I confess to ignorance on how we
could realistically get there. But I do think it's what people want:
to be forced to endure collation updates infrequently, and to be able
to choose the timing of the update when they absolutely must happen,
and to be able to easily know exactly what they need to reindex.

And from that point of view -- and again, I'm not volunteering to
implement it and I'm not telling you to do it either -- Joe's proposal
of supporting multiple versions sounds fantastic. Because then, I can
do a major version upgrade using pg_upgrade and keep everything pinned
to the old Unicode version or, perhaps even the old ICU version if we
had multi-version libicu support. I may be able to go through several
major version upgrades without ever needing to survive a collation
change. Eventually my hand will be forced, because PostgreSQL will
remove support for the Unicode version I care about or that old
version of libicu won't compile any more or will have security
vulnerabilities or something, but I will have the option to deal with
that collation change before or after any PostgreSQL version changes
that I'm doing. I'll be able to change the collation version at a time
when I'm not changing anything else and deal with JUST that fallout on
its own.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Wed, Mar 19, 2025 at 1:25 AM Jeremy Schneider
<schneider(at)ardentperf(dot)com> wrote:
> Maybe Postgres can be the first database to always ship support for the
> latest Unicode with each major version

Shipping the latest Unicode with our latest major version is existing
policy, as I understand it. I don't think we're breaking any new
ground here.

> Before we bump the unicode version, perseonally I'd just like to have
> some tools to make it so people actually can't do the things they
> shouldn't do.

I would, too, but I think that's a hole with no bottom. We're not
realistically going to sort this problem out in the next twelve
months, or even in the next five years. For that reason, I don't
understand how you can propose with a straight face that we pause the
Unicode updates that have been routine up until now until we have the
problem sorted out. That amounts to deciding that we're never going to
add support for any new Unicode versions. And I don't see that as
realistic. We have those Unicode tables because their contents matter.
If we don't update them then, for example, new cased letters won't
case-fold properly. Yeah, maybe if we didn't update for one year,
nobody would care. But five or ten or twenty years from now, somebody
will care, and I don't think we'll have all the stuff you want to
prevent people from hosing themselves even then.

Not only that, but freezing the Unicode tables doesn't even really
help that much, because the much bigger problem comes from the
constant whacking around of the glibc and to a lesser extent ICU
behavior.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Wed, 2025-03-19 at 08:46 -0400, Robert Haas wrote:
> I see your point, but most people don't use the builtin collation
> provider.

The other providers aren't affected by us updating Unicode, so I think
we got off track somehow. I suppose what I meant was:

"If you are concerned about inconsistencies, and you move to the
builtin provider, then 99% of the inconsistency problem is gone. We can
remove the last 1% of the problem if we do all the work listed above."

> When an EDB customer asks "if I do X,
> will anything break," it's often the case that answering "maybe" is
> the same as answering "yes".

That's a good point. However, note that "doesn't break primary keys" is
a nice guarantee, even if there's still some remaining doubts about
expression indexes, etc.

> They want a hard guarantee that the behavior will not
> change.

My understanding of this thread so far was that we were mostly
concerned about internal inconsistencies of stored structures; e.g.
indexes that could return different results than a seqscan.

Not changing query results at all between major versions is a valid
concern, but a fairly strict one that doesn't seem limited to immutable
functions or collation issues. Surely, at least the results of "SELECT
version()" should change from release to release ;-)

> Again, I'm not trying to oblige
> you to deliver that behavior and I confess to ignorance on how we
> could realistically get there.

FWIW I'm not complaining about doing the work. But I think the results
will be better if we can get a few people aligned on a general plan and
collaborating. I will try to kick that off.

> and to be able to easily know exactly what they need to reindex.

That's the main one, I think. The upgrade check offers that for the
builtin provider, though admittedly it's not a very user-friendly
solution, and we can do better.

> And from that point of view -- and again, I'm not volunteering to
> implement it and I'm not telling you to do it either -- Joe's
> proposal
> of supporting multiple versions sounds fantastic.

I certainly don't oppose giving users that choice. But I view it as a
burden we are placing on the users -- better than breakage, but not
really great, either. So if we do put in a ton of work, I'd like it if
we could arrive at a bettter destination.

If we actually want the BEST user experience possible, they'd not even
really know that their index was ever inconsistent. Autovacuum would
come along and just find the few entries in the index that need fixing,
and reindex just those few tuples. In theory, it should be possible:
there are a finite number of codepoints that change each Unicode
version, and we can just search for them in the data and fix up derived
structures.

Regards,
Jeff Davis

On Wed, Mar 19, 2025 at 1:39 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Wed, 2025-03-19 at 08:46 -0400, Robert Haas wrote:
> > I see your point, but most people don't use the builtin collation
> > provider.
>
> The other providers aren't affected by us updating Unicode, so I think
> we got off track somehow. I suppose what I meant was:
>
> "If you are concerned about inconsistencies, and you move to the
> builtin provider, then 99% of the inconsistency problem is gone. We can
> remove the last 1% of the problem if we do all the work listed above."

All right. I'm not sure I totally buy the 99% number, but I take your point.

> > When an EDB customer asks "if I do X,
> > will anything break," it's often the case that answering "maybe" is
> > the same as answering "yes".
>
> That's a good point. However, note that "doesn't break primary keys" is
> a nice guarantee, even if there's still some remaining doubts about
> expression indexes, etc.

No argument.

> > They want a hard guarantee that the behavior will not
> > change.
>
> My understanding of this thread so far was that we were mostly
> concerned about internal inconsistencies of stored structures; e.g.
> indexes that could return different results than a seqscan.

I think that is true, but inconsistent indexes can be the worst
problem without being the only one.

> Not changing query results at all between major versions is a valid
> concern, but a fairly strict one that doesn't seem limited to immutable
> functions or collation issues. Surely, at least the results of "SELECT
> version()" should change from release to release ;-)

Maybe we should stop doing releases, and then users won't have to
worry about our releases breaking things!

Slightly more seriously, the use of UPPER() and LOWER() in expression
indexes is not that uncommon. Sometimes, the index exists specifically
to enforce a unique constraint. Yes, plain indexes on columns are more
common, and it makes sense to target that case first, but we shouldn't
be too quickly hand-wave away the use of case-folding functions as a
thing that doesn't happen.

> I certainly don't oppose giving users that choice. But I view it as a
> burden we are placing on the users -- better than breakage, but not
> really great, either. So if we do put in a ton of work, I'd like it if
> we could arrive at a bettter destination.
>
> If we actually want the BEST user experience possible, they'd not even
> really know that their index was ever inconsistent. Autovacuum would
> come along and just find the few entries in the index that need fixing,
> and reindex just those few tuples. In theory, it should be possible:
> there are a finite number of codepoints that change each Unicode
> version, and we can just search for them in the data and fix up derived
> structures.

I have to disagree with this. I think this is a case where fixing
something automatically is clearly worse. First, it could never fix it
instantly, so you would be stuck with some window where queries might
return wrong results -- or if you prevent that by not using the
indexes any more until they're fixed, then it would instead cause huge
query performance regressions that could easily take down the whole
system. Second, one of the things people like least about autovacuum
is when it unexpectedly does a lot of work all at once. Today, that's
usually a vacuum for wrap-around, but suddenly trying to fix all my
indexes when I wasn't expecting that to happen could easily be just as
bad. I strongly believe users want to control what happens, not have
the system try to fix it for them automatically without their
knowledge.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Wed, 2025-03-19 at 14:33 -0400, Robert Haas wrote:

> I strongly believe users want to control what happens, not have
> the system try to fix it for them automatically without their
> knowledge.

Do you have a sketch of what the ideal Unicode version management
experience might look like? Very high level, like "this is what happens
by default during an upgrade" and "this is how a user discovers that
that they might want to update Uniocde", etc.

What ways can/should we nudge users to update more quickly, if at all,
so that they are less likely to have problems with newly-assigned code
points?

And, if possible, how we might extend this user experience to libc or
ICU updates?

Regards,
Jeff Davis

On Wed, Mar 19, 2025 at 5:47 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> Do you have a sketch of what the ideal Unicode version management
> experience might look like? Very high level, like "this is what happens
> by default during an upgrade" and "this is how a user discovers that
> that they might want to update Uniocde", etc.
>
> What ways can/should we nudge users to update more quickly, if at all,
> so that they are less likely to have problems with newly-assigned code
> points?
>
> And, if possible, how we might extend this user experience to libc or
> ICU updates?

As I think you know, I don't consider myself an expert in this area,
just somebody who has seen a decent amount of user pain (although I am
sure that even there some other people have seen more). That said, for
me the ideal would probably include the following things:

* When the collation/ctype/whatever definitions upon which you are
relying change, you can either decide to switch to the new ones
without rebuilding your indexes and risk wrong results until you
reindex, or you can decide to create new indexes using the new
definitions and drop the old ones.

* You're never forced to adopt new definitions during a SPECIFIC major
or minor release upgrade or when making some other big change to the
system. It's fine, IMHO, if we eventually remove support for old
stuff, but there should be a multi-year window of overlap. For
example, if PostgreSQL 42 adds support for Unicode 95.0.0, we'd keep
that support for, I don't know, at least the next four or five major
versions. So upgrading PG can eventually force you to upgrade
collation defs, but you don't get into a situation where PG 41
supports only Unicode < 95 and PG 42 supports only Unicode >= 95.

* In an absolutely perfect world, we'd have strong versioning of every
type of collation from every provider. This is probably very difficult
to achieve in practice, so maybe the somewhat more realistic goal
might be to get to a point where most users, most of the time, are
relying on collations with strong versioning. For glibc, this seems
relatively hopeless unless upstream changes their policy in a big way.
For ICU, loading multiple library versions seems like a possible path
forward. Relying more on built-in collations seems like another
possible approach, but I think that would require us to have more than
just a code-point sort: we'd need to have built-in collations for
users of various languages. That sounds like it would be a lot of work
to develop, but even worse, it sounds like it would be a tremendous
amount of work to maintain. I expect Tom will opine that this is an
absolutely terrible idea that we should never do under any
circumstances, and I understand the sentiment, but I think it might be
worth considering if we're confident we will have people to do the
maintenance over the long term.

* I would imagine pg_upgrade either keeping the behavior unchanged for
any strongly-versioned collation, or failing. I don't see a strong
need to try to notify users about the availability of new versions
otherwise. People who want to stay current will probably figure out
how to do that, and people who don't will ignore any warnings we give
them. I'm not completely opposed to some other form of notification,
but I think it's OK if "we finally removed support for your extremely
old ICU version" is the driving force that makes people upgrade.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Thu, 2025-03-20 at 08:45 -0400, Robert Haas wrote:
> * When the collation/ctype/whatever definitions upon which you are
> relying change, you can either decide to switch to the new ones
> without rebuilding your indexes and risk wrong results until you
> reindex, or you can decide to create new indexes using the new
> definitions and drop the old ones.

Would newly-created objects pick up the new Unicode version, or stick
with the old one?

> Relying more on built-in collations seems like another
> possible approach, but I think that would require us to have more
> than
> just a code-point sort: we'd need to have built-in collations for
> users of various languages. That sounds like it would be a lot of
> work
> to develop, but even worse, it sounds like it would be a tremendous
> amount of work to maintain. I expect Tom will opine that this is an
> absolutely terrible idea that we should never do under any
> circumstances, and I understand the sentiment, but I think it might
> be
> worth considering if we're confident we will have people to do the
> maintenance over the long term.

Supporting a built-in case-insensitive collation would be some work,
but it's not a huge leap now that we have CASEFOLD().

Supprting built-in natural language sort orders would be a much larger
scope. And I don't think we need that, but that's a larger discussion.

> I don't see a strong
> need to try to notify users about the availability of new versions
> otherwise. People who want to stay current will probably figure out
> how to do that

What if we were able to tell, for instance, that your database has none
of the codepoints affected by the most recent update. Then updating
would be less risky than not updating: if you don't update Unicode,
then the code points could end up in the database treated as
unassigned, and then cause a problem for future updates.

Regards,
Jeff Davis

On Fri, Mar 21, 2025 at 2:45 AM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Thu, 2025-03-20 at 08:45 -0400, Robert Haas wrote:
> > * When the collation/ctype/whatever definitions upon which you are
> > relying change, you can either decide to switch to the new ones
> > without rebuilding your indexes and risk wrong results until you
> > reindex, or you can decide to create new indexes using the new
> > definitions and drop the old ones.
>
> Would newly-created objects pick up the new Unicode version, or stick
> with the old one?

Hmm, I hadn't thought about that. I'm assuming that the Unicode
version would need, in this scheme, to be coupled to the object that
depends on it. For example, an index that uses a Unicode collation
would need to store a Unicode version. But for a new index, how would
that be set? Maybe the Unicode version would be treated as part of the
collation. I'm guessing that an index defaults to the column
collation, and I think the column collation defaults to the database
default collation. We might need a way for ALTER DATABASE to allow the
database default to be adjusted. I'm not quite sure here, but my
general feeling is that Unicode version feels like part of the
collation and that we should avoid introducing a separate mechanism if
possible. What are your thoughts?

> Supprting built-in natural language sort orders would be a much larger
> scope. And I don't think we need that, but that's a larger discussion.

I'm curious why you think this. My own feeling (as I think you
probably know, but just to be clear) is that relatively few people
need extremely precise control over their collation behavior, but
there are some who do. However, I think there are many people for whom
a code-point sort won't be good enough. If you want to leave this
discussion for another time, that's fine.

> What if we were able to tell, for instance, that your database has none
> of the codepoints affected by the most recent update. Then updating
> would be less risky than not updating: if you don't update Unicode,
> then the code points could end up in the database treated as
> unassigned, and then cause a problem for future updates.

The problem with this is that it requires scanning the whole database.
That's not to say it's useless. Some people can afford to scan the
whole database, and some people might even WANT to scan the whole
database just to give themselves peace of mind. But there are also
plenty of people for whom this is a major downside, even unusable. I'd
like to have a solution that is based on metadata.

Maybe we should actually move in the direction of having encodings
that are essentially specific versions of Unicode. Instead of just
having utf-8 that accepts whatever, you could have utf-8.v16.0.0 or
whatever, which would only accept code points known to that version of
Unicode. Or maybe this shouldn't be entirely new encodings but
something vaguely akin to a typmod, so that you could have columns of
type text[limited_to_unicode_v16_0_0] or whatever. If we actually
exclude unassigned code points, then we know they aren't there, and we
can make deductions about what is safe to do based on that
information. I'm not quite sure how useful that is, but I tend to
think that enforcing rules when the data goes in has a decent shot at
being better than letting anything going in and then having to scan it
later to see how it all turned out.

--
Robert Haas
EDB: http://www.enterprisedb.com

On 15.03.25 07:54, Jeremy Schneider wrote:
> in favor of leaving it alone because ICU is there for when I need
> up-to-date unicode versions.
>
> From my perspective, the whole point of the builtin collation was to
> one option that avoids these problems that come with updating both ICU
> and glibc.
>
> So I guess the main point of the builtin provider just that it's faster
> than ICU?

A mistake that some people apparently continue to make in this
discussion is that they assume that the only thing the Unicode tables
drive is the builtin collation provider. This is not true, the Unicode
tables were there long before the builtin collation provider, and they
have other purposes. And we knew at the time the builtin collation
provider was added that it would have certain problems with the Unicode
table updates, and we accepted it with the understanding that this would
not change our procedures. Otherwise, we would likely not have accepted
it in its current form.

Those who are now trying to make the builtin collation provider have
properties that it does not have and was not intended to have when it
was added, they would need to arrange the work to make it have those
properties if they want them, rather than insist that progress in other
areas must stop because they are content with the current state.

On Fri, 2025-03-21 at 17:15 +0100, Peter Eisentraut wrote:
> And we knew at the time the builtin collation
> provider was added that it would have certain problems with the
> Unicode
> table updates, and we accepted it with the understanding that this
> would
> not change our procedures.

Correct. That was called out by me in the initial proposal for the
builtin collation provider and documented explicitly.

> Those who are now trying to make the builtin collation provider have
> properties that it does not have and was not intended to have when it
> was added, they would need to arrange the work to make it have those
> properties if they want them, rather than insist that progress in
> other
> areas must stop because they are content with the current state.

It does feel like the goalposts are moving. That's not necessarily bad
by itself -- our expectations should go up. But the way it's happening
in this thread makes it feel like new obligations are being put on the
people already working on collation improvements, in particular Peter
and I.

Robert indicated that there might be some willing hackers, and perhaps
even appetite for larger-scope projects in this area, which is great
news. A lot of what's happening in this area is non-controversial, and
more attention would be an unqualified win. For instance, Peter put
some work into better support for non-deterministic collations, and I
had some ideas there:

/message-id/024c9b9aa834f668496ef95700b57e50bf3f4808.camel%40j-davis.com

but I didn't have time to work on that this cycle. (Maybe my idea would
be hard to implement or not work at all, or maybe Peter and Tom already
have better ideas, but that's different from being controversial.)

For the many people who think multi-lib is the way to go, the shortest
path involves someone taking a look at this prerequisite:

/message-id/cb580fec46ea4ca96dd4bbde9d2769360e097d01.camel%40j-davis.com

Some technical review would be nice, but really what I needed was
someone to say "this small regression in a worst case due to an
unavoidable indirect function call is not worth worrying about". It
might be a bit late now, though, as a big refactoring right before FF
seems like a bad idea. So it will probably slip until July, adding risk
that any other multi-lib work (which I am not promising to do) might
slip to PG20, which users will see at the end of 2027. Ugh.

Regards,
Jeff Davis

On Fri, Mar 21, 2025 at 2:27 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > Those who are now trying to make the builtin collation provider have
> > properties that it does not have and was not intended to have when it
> > was added, they would need to arrange the work to make it have those
> > properties if they want them, rather than insist that progress in
> > other
> > areas must stop because they are content with the current state.
>
> It does feel like the goalposts are moving. That's not necessarily bad
> by itself -- our expectations should go up. But the way it's happening
> in this thread makes it feel like new obligations are being put on the
> people already working on collation improvements, in particular Peter
> and I.

Honestly, I'm confused as to why Peter hasn't committed the Unicode
update a long time ago at this point. Nobody has alleged that the
stability guarantees provided by the builtin collation provider are
(a) worse than any of other other providers or (b) worse than what was
documented. And nobody has refuted the argument that refusing to
update the Unicode tables will cause other problems (such as not
knowing what to do with new code points that are added in the other
places where those tables are used). People who aren't doing the work
to improve the infrastructure don't get to hold the longstanding
process hostage.

None of the above means that I wouldn't like things to be better in
this area; this is a huge source of pain and I would obviously like
all of kinds of amazing things, preferably done by other people with
no real thought or work required on my part. But I'd like that in lots
of areas of PostgreSQL and lots of areas of my life in general, and I
understand that it's an unreasonable expectation.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Fri, 2025-03-21 at 10:45 -0400, Robert Haas wrote:
> We might need a way for ALTER DATABASE to allow the
> database default to be adjusted. I'm not quite sure here, but my
> general feeling is that Unicode version feels like part of the
> collation and that we should avoid introducing a separate mechanism
> if
> possible. What are your thoughts?

My (early stage) plans are to have two new shared catalogs,
pg_ctype_provider and pg_collation_provider. Objects would depend on
records in those shared catalogs, which would each have a version. We'd
eventually allow multiple records with providerkind=icu, for instance,
and have some way to choose which one to use (perhaps new objects get
the default version, old objects keep the old version, or something).

The reason to have two shared catalogs is because some objects depend
on collation behavior and some on ctype behavior. If there's an index
on "t COLLATE PG_C_UTF8" then there would be no direct dependency from
the index to the builtin provider in either catalog, because collation
behavior in the builtin provider is unversioned memcmp. But if there's
an index on "LOWER(t COLLATE PG_C_UTF8)", then it would have a
dependency entry to the builtin provider's entry in pg_ctype_provider.

>
> I'm curious why you think this. My own feeling (as I think you
> probably know, but just to be clear) is that relatively few people
> need extremely precise control over their collation behavior, but
> there are some who do. However, I think there are many people for
> whom
> a code-point sort won't be good enough.

You can use ICU for sorting without using it for the index comparators.
Using ICU in the index comparators is an implementation detail that's
only required for unique indexes over non-deterministic collations. And
if it's not used for the index comparators, then most of the problems
go away, and versioning is not nearly so important.

Sure, there are some cases where using ICU in the index comparator is
important, and I'm not suggesting that we remove functionality. But I
believe that using libc or ICU for index comparators is the wrong
default behavior -- high downsides and low upsides for most text
indexes that have ever been created.

Even if there is an ORDER BY, using an index is often the wrong thing
unless it's an index only scan. Text indexes are rarely correlated with
the heap, so it would lead to a lot of random heap fetches, and it's
often better to just execute the query and do a final sort. The
situations where ICU in the comparator is a good idea are special cases
of special cases.

I've posted about this in the past, and got universal disagreement. But
I believe others will eventually come to the same conclusion that I
did.

>
> Maybe we should actually move in the direction of having encodings
> that are essentially specific versions of Unicode. Instead of just
> having utf-8 that accepts whatever, you could have utf-8.v16.0.0 or
> whatever, which would only accept code points known to that version
> of
> Unicode. Or maybe this shouldn't be entirely new encodings but
> something vaguely akin to a typmod, so that you could have columns of
> type text[limited_to_unicode_v16_0_0] or whatever. If we actually
> exclude unassigned code points, then we know they aren't there, and
> we
> can make deductions about what is safe to do based on that
> information.

I like this line of thinking, vaguely similar to my STRICT_UNICODE
database option proposal. Maybe these aren't exactly the right things
to do, but I think there are some possibilities here, and we shouldn't
give up and assume there's a problem when usually there is not.

It reminds me of fast-path locking: sure, there *might* be DDL
happening while I'm trying to do a simple SELECT query. But probably
not, so let's make it the responsibility of DDL to warn others that
it's doing something, rather than the responsibility of the SELECT
query.

Regards,
Jeff Davis

On Fri, 2025-03-21 at 14:54 -0400, Robert Haas wrote:
> And nobody has refuted the argument that refusing to
> update the Unicode tables will cause other problems (such as not
> knowing what to do with new code points that are added in the other
> places where those tables are used).

The argument is irrefutable.
I can only utter my (unproven) conviction that the complaints about
some outlandish character being converted to upper case in the wrong
way won't be as loud as the complaints about a corrupted index.

But perhaps both occurrences would be rare enough that there is
little point in arguing at all.

> People who aren't doing the work
> to improve the infrastructure don't get to hold the longstanding
> process hostage.

Actually, I am arguing for *not* doing the work...

Yours,
Laurenz Albe

On Fri, 21 Mar 2025 13:45:24 -0700
Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> > Maybe we should actually move in the direction of having encodings
> > that are essentially specific versions of Unicode. Instead of just
> > having utf-8 that accepts whatever, you could have utf-8.v16.0.0 or
> > whatever, which would only accept code points known to that version
> > of
> > Unicode. Or maybe this shouldn't be entirely new encodings but
> > something vaguely akin to a typmod, so that you could have columns
> > of type text[limited_to_unicode_v16_0_0] or whatever. If we actually
> > exclude unassigned code points, then we know they aren't there, and
> > we
> > can make deductions about what is safe to do based on that
> > information.
>
> I like this line of thinking, vaguely similar to my STRICT_UNICODE
> database option proposal. Maybe these aren't exactly the right things
> to do, but I think there are some possibilities here, and we shouldn't
> give up and assume there's a problem when usually there is not.

There is "the iPhone paradox" here; if we reject unassigned code
points, then websites are going to start throwing database errors for
anyone with the latest iPhone who uses a new emoji.

(Unless the database is updated very quickly, which is atypical.) Apple
tends to get new emojis into consumers hands a year or less after the
new Unicode release.

-Jeremy

On 17.03.25 19:54, Jeff Davis wrote:
> On Thu, 2025-03-13 at 14:49 +0100, Peter Eisentraut wrote:
>> I think these test result changes are incorrect.  AFAICT, nothing has
>> changed in the upstream data that would explain such a change.
>>
>> I didn't get such test differences in my original patch.  Did you
>> rebase
>> the patch or regenerate it?  Maybe something went wrong there.
>
> Right. The previous version was improperly rebased.
>
> I went back to your original version and rebased over master (including
> the latest optimizations for case mapping), and then rebased my changes
> on top of that. Attached.
>
> One annoyance with the recent case mapping optimizations is that the
> Unicode update diff is larger, similar to the diff for
> unicode_norm_hashfunc.h. Previously, it was binary search, so the only
> differences were logical differences.

I have committed the update to the Unicode tables. I suggest you commit
your pg_upgrade patch on top of that now.

On Wed, Mar 05, 2025 at 02:33:42PM -0600, Nathan Bossart wrote:
> + /*
> + * The builtin provider did not exist prior to version 17. While there are
> + * still problems that could potentially be caught from earlier versions,
> + * such as an index on NORMALIZE(), we don't check for that here.
> + */
> + if (GET_MAJOR_VERSION(cluster->major_version) < 1700)
> + return;
>
> nitpick: In most cases, I think this check is done in
> check_and_dump_old_cluster() before actually calling the checking function.
> I don't think there's any big problem here, except you might strand the
> memory allocated for the task.
>
> + if (!unicode_version_changed(cluster))
> + {
> + check_ok();
> + return;
> + }
>
> Same nitpick here about stranding the task memory.

Coverity is unhappy about these. I think we should at least do something
like the following. I'll commit this when I have an opportunity.

diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 8f946c4e3d6..18c2d652bb6 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -1825,7 +1825,7 @@ static void
check_for_unicode_update(ClusterInfo *cluster)
{
UpgradeTaskReport report;
- UpgradeTask *task = upgrade_task_create();
+ UpgradeTask *task;
const char *query;

/*
@@ -1920,6 +1920,7 @@ check_for_unicode_update(ClusterInfo *cluster)
" d.datname = current_database() AND "
" d.encoding = pg_char_to_encoding('UTF8');";

+ task = upgrade_task_create();
upgrade_task_add_step(task, query,
process_unicode_update,
true, &report);

--
nathan

On Sun, Apr 06, 2025 at 09:16:17AM -0500, Nathan Bossart wrote:
> Coverity is unhappy about these. I think we should at least do something
> like the following. I'll commit this when I have an opportunity.

Committed.

--
nathan