Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Jeremy Schneider <schneider@ardentperf.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Collation version tracking for macOS
Date: Thu, 9 Jun 2022 10:54:01 -0700
Message-Id: <EF3786A4-4254-456F-A9B8-C12C4AF3156C@ardentperf.com>
References: 
 <CAH2-WzmZce7T9_j0nWXZJPzs4xwO6zyMa3aDBmo1=d-F-NVoBA@mail.gmail.com>
Cc: Thomas Munro <thomas.munro@gmail.com>, Jim Nasby <nasbyj@amazon.com>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
In-Reply-To: 
 <CAH2-WzmZce7T9_j0nWXZJPzs4xwO6zyMa3aDBmo1=d-F-NVoBA@mail.gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Archived-At: 
 <https://www.postgresql.org/message-id/EF3786A4-4254-456F-A9B8-C12C4AF3156C%40ardentperf.com>
Precedence: bulk


> On Jun 8, 2022, at 22:40, Peter Geoghegan <pg@bowt.ie> wrote:
>=20
> =EF=BB=BFOn Wed, Jun 8, 2022 at 10:24 PM Jeremy Schneider
> <schneider@ardentperf.com> wrote:
>> Even if PG supports two versions of ICU, how does someone actually go abo=
ut removing every dependency on the old version and replacing it with the ne=
w?
>=20
> They simply REINDEX, without changing anything. The details are still
> fuzzy, but at least that's what I was thinking of.
>=20
>> Can it be done without downtime? Can it be done without modifying a runni=
ng application?
>=20
> Clearly the only way that we can ever transition to a new "physical
> collation" is by reindexing using a newer ICU version. And clearly
> there is going to be a need to fully deprecate any legacy version of
> ICU on a long enough timeline. There is just no getting around that.


I=E2=80=99m probably just going to end up rehashing the old threads I haven=E2=
=80=99t read yet=E2=80=A6

One challenge with this approach is you have things like sort-merge joins th=
at require the same collation across multiple objects. So I think you=E2=80=99=
d need to keep all the old indexes around until you have new indexes availab=
le for all objects in a database, and somehow the planner would need to be s=
mart enough to dynamically figure out old vs new versions on a query-by-quer=
y basis. May need an atomic database-wide cutover; running a DB with interna=
lly mixed collation versions doesn=E2=80=99t seem like a small challenge. It=
 would require enough disk space for two copies of all indexes, and queries w=
ould change which indexes they use in a way that wouldn=E2=80=99t be immedia=
tely obvious to users or app dev. Suddenly switching to or from a differentl=
y-bloated index could result in confusing and sudden performance changes.

Also there would still need to be a plan to address all the other non-index o=
bjects where collation is used, as has been mentioned before.

And given the current architecture, that final =E2=80=9Calter database updat=
e default collation=E2=80=9D command still seems awful risky, bug-prone and d=
ifficult to get correct. At least it seems that way to me.

At a minimum, this is a very big project and it seems to me like it may be w=
ise to get more end-to-end fleshing out of the plans before committing incre=
mental pieces in core (which could end up being misguided if the plan doesn=E2=
=80=99t work as well as assumed). Definitely doesn=E2=80=99t seem to me like=
 anything that will happen in a year or two.

And my opinion is that the problems caused by depending on OS libraries for c=
ollation need to be addressed on a shorter timeline than what=E2=80=99s real=
istic for inventing a new way for a relational database to offer transparent=
 or online upgrades of linguistic collation versions.

Also I still think folks are overcomplicating this by focusing on linguistic=
 collation as the solution. Like 1% of users actually need or care about hav=
ing the latest technically correct local-language-based sorting, at a databa=
se level. MySQL did the right thing here by doing what every other RDBMS did=
, and just making a simple =E2=80=9Cgood-enough=E2=80=9D collation hardcoded=
 in the DB, same across all platforms, that never changes.

The 1% of users who need true linguistic collation can probably deal with th=
e trade-off of dump-and-load upgrades for their ICU indexes and databases fo=
r a few more years.

-Jeremy


Sent from my TI-83