Re: Why do indexes and sorts use the database collation?

On Wed, 15 Nov 2023 at 00:28, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> On Tue, 2023-11-14 at 14:47 -0500, Tom Lane wrote:
> > Why should that ever be different from the column's own declared
> > collation?
>
> Because an index with the "C" collation is more efficient in terms of
> building/maintaining/searching the index, and it also doesn't carry
> risks of corrupting your PK index when you upgrade libc or other
> dependency headaches.

That doesn't really answer the question for me. Why would you have a
primary key that has different collation rules (which include equality
rules) than the columns that this primary key contains? It is not
unlikely that users are misinformed about the behaviour of the
collation they're creating, thus breaking any primary key or equality
lookup that uses indexes auto-converted from that collation to the "C"
collation.

If the collation on my primary key's columns changes from one that is
deterministic to one that isn't, then my primary key surely has to be
reindexed. If the collation of the underlying index was overwritten to
'C' for performance, then that's a problem, right, as we wouldn't have
the expectation that the index is based on the columns' actual
collation's properties?

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)