Q: error on updating collation version information

Am Sun, Dec 04, 2022 at 01:22:02PM +0100 schrieb Karsten Hilbert:

gnumed_v22=> ALTER COLLATION pg_catalog."br_FR@euro" REFRESH VERSION;
ERROR: collation "pg_catalog.br_FR@euro" for encoding "UTF8" does not exist

The OS (libc) does seem to know that collation:

@hermes:~$ locale -a | grep br_FR
br_FR
br_FR@euro
br_FR.utf8

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Am Sun, Dec 04, 2022 at 01:22:02PM +0100 schrieb Karsten Hilbert:

following an ICU upgrade, collations in a stock Debian PG 15.1
cluster now have divergent version information in pg_collations.

Correction: this is following a libc upgrade 2.35 -> 2.36

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On 12/4/22 04:35, Karsten Hilbert wrote:

Am Sun, Dec 04, 2022 at 01:22:02PM +0100 schrieb Karsten Hilbert:

following an ICU upgrade, collations in a stock Debian PG 15.1
cluster now have divergent version information in pg_collations.

Correction: this is following a libc upgrade 2.35 -> 2.36

So to be clear this database is not using ICU, but collations from libc?

How was the database installed?

In first post you had:

gnumed_v22=> select *, pg_encoding_to_char(collencoding) from
pg_collation where collname = 'br_FR@euro';
-[ RECORD 1 ]-------+-----------
oid | 12413
collname | br_FR@euro
collnamespace | 11
collowner | 10
collprovider | c
collisdeterministic | t
collencoding | 16
collcollate | br_FR@euro
collctype | br_FR@euro
colliculocale |
collversion | 2.35
pg_encoding_to_char | LATIN9

where collprovider c means libc and collversion 2.35.

Not exactly sure how that interacts with from here:

https://www.postgresql.org/docs/current/catalog-pg-collation.html

collversion text

Provider-specific version of the collation. This is recorded when the
collation is created and then checked when it is used, to detect changes
in the collation definition that could lead to data corruption.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

--
Adrian Klaver
adrian.klaver@aklaver.com

Am Sun, Dec 04, 2022 at 10:09:47AM -0800 schrieb Adrian Klaver:

following an ICU upgrade, collations in a stock Debian PG 15.1
cluster now have divergent version information in pg_collations.

Correction: this is following a libc upgrade 2.35 -> 2.36

So to be clear this database is not using ICU, but collations from libc?

Sorry for the confusion.

This database carries collations from _both_ libc and ICU in
pg_collations.

The collation in question (br_FR@euro) is _not_ in use (as in
being depended on by any in-database object).

How was the database installed?

stock Debian

apt-get install postgresql-15 (which gives 15.1)

followed by

CREATE DATABASE "gnumed_v22" with owner = "redacted :-)" template = "template1" encoding = 'unicode';

as "postgres".

In first post you had:

gnumed_v22=> select *, pg_encoding_to_char(collencoding) from pg_collation where
collname = 'br_FR@euro';
-[ RECORD 1 ]-------+-----------
oid | 12413
collname | br_FR@euro
collnamespace | 11
collowner | 10
collprovider | c
collisdeterministic | t
collencoding | 16
collcollate | br_FR@euro
collctype | br_FR@euro
colliculocale |
collversion | 2.35
pg_encoding_to_char | LATIN9

where collprovider c means libc and collversion 2.35.

Yeah, that's when I figured that I misspoke about the ICU upgrade.

Yes, there was an ICU upgrade, and yes, it did affect
collations. Those I was able to fix up (the "reindex /
revalidate constraint / refresh collation version" dance).

There also was a libc upgrade which also affected locales.
Most of them were fixable by that dance but some popped up
(such as br_FR@euro) to not be "correctable" showing the
"does not exist for encoding" error.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Is this to be expected ?

PG 15.1 on Debian:

gnumed_v22=# select *, pg_collation_actual_version(oid), pg_encoding_to_char(collencoding) from pg_collation where collname = 'zh_TW';
-[ RECORD 1 ]---------------+------------
oid | 12985
collname | zh_TW
collnamespace | 11
collowner | 10
collprovider | c
collisdeterministic | t
collencoding | 4
collcollate | zh_TW.euctw
collctype | zh_TW.euctw
colliculocale |
collversion | 2.35
pg_collation_actual_version | 2.36
pg_encoding_to_char | EUC_TW
-[ RECORD 2 ]---------------+------------
oid | 12986
collname | zh_TW
collnamespace | 11
collowner | 10
collprovider | c
collisdeterministic | t
collencoding | 6
collcollate | zh_TW.utf8
collctype | zh_TW.utf8
colliculocale |
collversion | 2.36
pg_collation_actual_version | 2.36
pg_encoding_to_char | UTF8

gnumed_v22=# begin;
BEGIN
gnumed_v22=*# alter collation pg_catalog."zh_TW" refresh version ;
NOTICE: version has not changed
ALTER COLLATION
gnumed_v22=*# alter collation pg_catalog."zh_TW.utf8" refresh version ;
NOTICE: version has not changed
ALTER COLLATION
gnumed_v22=*# alter collation pg_catalog."zh_TW.euctw" refresh version ;
ERROR: collation "pg_catalog.zh_TW.euctw" for encoding "UTF8" does not exist
gnumed_v22=!#

As far as I can tell the documentation asserts that since the
database encoding is UTF8 the pg_catalog."zh_TW.euctw" will
be ignored by the server for all practical purposes.

Does this mean it is impossible to "correct" its version
information ?

And if so, that is expected to be non-harmful and is not
expected to trigger nag messages ?

Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Karsten Hilbert wrote:

The database encoding is UTF8. That br_FR@euro.LATIN9 had
_not_ been added manually. It is also not actively used in my
database(s).

br_FR@euro.LATIN9 cannot be used actively in an UTF-8 database
because it's for a different encoding than the database.

It was probably available in the OS at initdb time. Every available
locale (see locale -a) gets imported into the template databases,
and then into the other databases, since CREATE DATABASE copies a
template without filtering out the locales that are incompatible
with the target database.

There's no need to upgrade anything in the OS to get the
ALTER COLLATION... REFRESH error you mention.
It's sufficient to have a mix of collations for different encodings
and try to refresh collations whose encoding are not compatible
with the current database.

What is the canonical advice on the way forward here ? Is
the _suggested_ solution to delete the collation or am I
missing to see the "proper" approach to fixing it ?

ISTM that dropping that collation from any non-LATIN9 database
is the more sensible action.

Maybe it could be discussed as a possible improvement to have
ALTER COLLATION... REFRESH ignore the database encoding and still
refresh the version number.

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

Dear all,

I managed to drop the "special" collations default, C, and
POSIX with OIDs 100, 950, 951.

Is there a way to recreate them (short of restoring a backup)
? Naive attempts with create collation do not seem to work
out.

Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On Wed, 2022-12-21 at 15:24 +0100, Karsten Hilbert wrote:

I managed to drop the "special" collations default, C, and
POSIX with OIDs 100, 950, 951.

Is there a way to recreate them (short of restoring a backup)
? Naive attempts with create collation do not seem to work
out.

I would definitely go for the backup, but here is how you can
create these three rows in PostgreSQL v15:

INSERT INTO pg_collation
(oid, collname, collnamespace, collowner, collprovider,
collisdeterministic, collencoding, collcollate, collctype)
VALUES
(100, 'default', 11, 10, 'd', TRUE, -1, NULL, NULL),
(950, 'C', 11, 10, 'c', TRUE, -1, 'C', 'C'),
(951, 'POSIX', 11, 10, 'c', TRUE, -1, 'POSIX', 'POSIX');

Yours,
Laurenz Albe

Am Wed, Dec 21, 2022 at 03:41:24PM +0100 schrieb Laurenz Albe:

I would definitely go for the backup, but here is how you can
create these three rows in PostgreSQL v15:

INSERT INTO pg_collation
(oid, collname, collnamespace, collowner, collprovider,
collisdeterministic, collencoding, collcollate, collctype)
VALUES
(100, 'default', 11, 10, 'd', TRUE, -1, NULL, NULL),
(950, 'C', 11, 10, 'c', TRUE, -1, 'C', 'C'),
(951, 'POSIX', 11, 10, 'c', TRUE, -1, 'POSIX', 'POSIX');

Many thanks ! I wasn't so sure whether inserting appropriate
rows would be equivalent to create collation... (pg_collation
might have been a view projecting inner workings of the
server engine).

Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Am Wed, Dec 21, 2022 at 03:46:48PM +0100 schrieb Karsten Hilbert:

I wasn't so sure whether inserting appropriate
rows would be equivalent to create collation...

For that matter, is DELETE FROM pg_collation ... equivalent
to DROP COLLATION ?

Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

For that matter, is DELETE FROM pg_collation ... equivalent
to DROP COLLATION ?

There's also entries in pg_depend and pg_shdepend to worry
about.

For these built-in collations, as of v15 there are no such
entries, but prior versions had explicit "pin" entries.

regards, tom lane