pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Q: error on updating collation version information
7+ messages / 3 participants
[nested] [flat]

* Q: error on updating collation version information
@ 2022-12-04 12:22  Karsten Hilbert <Karsten.Hilbert@gmx.net>
  0 siblings, 3 replies; 7+ messages in thread

From: Karsten Hilbert @ 2022-12-04 12:22 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Dear all,

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

Now

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

despite

	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


However, note the UTF8 vs LATIN9.

The manual sayeth:

	Some (less frequently used) encodings are not supported
	by ICU. When the database encoding is one of these, ICU
	collation entries in pg_collation are ignored. Attempting
	to use one will draw an error along the lines of
	“collation "de-x-icu" for encoding "WIN874" does not
	exist”.

which sounds somewhat related.

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).

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 ?

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Q: error on updating collation version information
@ 2022-12-04 12:33  Karsten Hilbert <Karsten.Hilbert@gmx.net>
  parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
  2 siblings, 0 replies; 7+ messages in thread

From: Karsten Hilbert @ 2022-12-04 12:33 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Q: error on updating collation version information
@ 2022-12-04 12:35  Karsten Hilbert <Karsten.Hilbert@gmx.net>
  parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
  2 siblings, 1 reply; 7+ messages in thread

From: Karsten Hilbert @ 2022-12-04 12:35 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Q: error on updating collation version information
@ 2022-12-04 18:09  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Adrian Klaver @ 2022-12-04 18:09 UTC (permalink / raw)
  To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Q: error on updating collation version information
@ 2022-12-04 22:25  Karsten Hilbert <Karsten.Hilbert@gmx.net>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Karsten Hilbert @ 2022-12-04 22:25 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Q: error on updating collation version information
@ 2022-12-05 00:54  Karsten Hilbert <Karsten.Hilbert@gmx.net>
  parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Karsten Hilbert @ 2022-12-05 00:54 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Q: error on updating collation version information
@ 2022-12-05 10:51  Daniel Verite <daniel@manitou-mail.org>
  parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
  2 siblings, 0 replies; 7+ messages in thread

From: Daniel Verite @ 2022-12-05 10:51 UTC (permalink / raw)
  To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-general@lists.postgresql.org

	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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2022-12-05 10:51 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-04 12:22 Q: error on updating collation version information Karsten Hilbert <Karsten.Hilbert@gmx.net>
2022-12-04 12:33 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2022-12-04 12:35 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2022-12-04 18:09   ` Adrian Klaver <adrian.klaver@aklaver.com>
2022-12-04 22:25     ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2022-12-05 00:54       ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2022-12-05 10:51 ` Daniel Verite <daniel@manitou-mail.org>

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