Re: COLLATION update in 13.1

Matthias Apitz wrote:

> Thanks. I did \l before which gives:
>
> List of databases
> Name | Owner | Encoding | Collate | Ctype | ICU Locale
> | Locale Provider | Access privileges
> ------------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
> bar | foo | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
> | libc |
> customers | sisis | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
> | libc |
> ...
>
> But why the ALTER statement needs the spelling different as the output of
> \l :
>
> sisis=# ALTER COLLATION "de_DE.UTF-8" REFRESH VERSION;
> ERROR: collation "de_DE.UTF-8" for encoding "UTF8" does not exist

The "Collate" and "Ctype" columns in the output of \l refer to a
locale name that is passed to libc to use locale-dependant functions.
It's somewhat counter-intuitive, but it's technically not a database
collation, and it's independent from the de_DE.utf8 collation that
exists in the database, in the sense that de_DE.utf8 is the name
of a database object whereas a locale is not a database object.

In fact, if your applications always use the default collation like
most apps do (that is, it never uses explicit COLLATE clauses), then
you could even issue DROP COLLATION "de_DE.utf8" or
ALTER COLLATION "de_DE.utf8" RENAME TO "foobar" and it
would not have any notable effect.
\l would still report "de_DE.UTF-8" as it did previously.
That's because "de_DE.utf8" is not the default collation, it's
a collation that happens to correspond to the same locale as the
default collation. The default collation is named "default", it
lives in the "pg_catalog" namespace, and it cannot be dropped since
it's a system object.

Technically the ALTER DATABASE xxx REFRESH COLLATION VERSION updates
the pg_database.datcollversion field (for PG15+, before that it did
not exist), whereas the ALTER COLLATION xxx REFRESH VERSION updates
the pg_collation.collversion field.

With PG15+, ALTER DATABASE xxx REFRESH COLLATION VERSION does
not imply any ALTER COLLATION. If you do only the ALTER DATABASE,
all the collations in pg_collation still have their collversion
fields that lag behind. But it only matters if these collations
are actually used by explicit COLLATE clauses, otherwise
Postgres will never use them and thus never emit any warning.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/