public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniel Verite <[email protected]>
To: Matthias Apitz <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Dominique Devienne <[email protected]>
Cc: Jeremy Schneider <[email protected]>
Cc: [email protected]
Subject: Re: COLLATION update in 13.1
Date: Mon, 24 Feb 2025 18:07:52 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHzebO9fq5eRsoQZnzDiR=MDCOYo+eHmQ05Firo17zZg=6p06w@mail.gmail.com>

	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/

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: COLLATION update in 13.1
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox