>
> On Jul 6, 2024, at 12:51 PM, Noah Misch <n...@leadboat.com> wrote:
> Behavior after that:
>
> -- 2 rows w/ seq scan, 0 rows w/ index scan
> SELECT 1 FROM t WHERE s ~ '[[:alpha:]]';
> SET enable_seqscan = off;
> SELECT 1 FROM t WHERE s ~ '[[:alpha:]]';
>
> -- ERROR: heap tuple (0,1) from table "t" lacks matching index tuple within
> index "iexpr"
> SELECT bt_index_parent_check('iexpr', heapallindexed => true);
> -- ERROR: heap tuple (0,1) from table "t" lacks matching index tuple within
> index "ipred"
> SELECT bt_index_parent_check('ipred', heapallindexed => true);
Other databases do still ship built-in ancient versions of unicode (Db2 ships
4.0+ and Oracle ships 6.1+), and they have added new Unicode versions alongside
the old but not removed the old versions. They claim to have “deprecated” old
versions… but it seems they haven’t been able to get rid of them yet. Maybe
some customer is willing to pay to continue deferring painful rebuilds needed
to get rid of the old collation versions in commercial DBs?
For reference, see the table on slide 56 at
https://www.pgevents.ca/events/pgconfdev2024/schedule/session/95-collations-from-a-to-z/
and also see
https://ardentperf.com/2024/05/22/default-sort-order-in-db2-sql-server-oracle-postgres-17/
Thanks for the illustration with actual Unicode 16 draft data.
Also, not directly related to this email… but reiterating a point I argued for
in the recorded talk at pgconf.dev in Vancouver: a very strong argument for
having the DB default to a stable unchanging built-in collation is that the
dependency tracking makes it easy to identify objects in the database using
non-default collations, and it’s easy to know exactly what needs to be rebuilt
for a user to safely change some non-default collation provider’s behavior.
-Jeremy
Sent from my TI-83
