public inbox for [email protected]help / color / mirror / Atom feed
Re: Collation again here 4+ messages / 3 participants [nested] [flat]
* Re: Collation again here @ 2026-01-08 12:48 Dominique Devienne <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Dominique Devienne @ 2026-01-08 12:48 UTC (permalink / raw) To: Rihad <[email protected]>; +Cc: pgsql-general On Thu, Jan 8, 2026 at 1:39 PM Rihad <[email protected]> wrote: > Hi, guys. Just pg_upgraded our PG from 13.x to 18.1, rebuilt all indexes concurrently and issued ALTER DATABASE foo REFRESH COLLATION VERSION > Everything's fine on the master server, no warnings etc. Then I set up a replica using pg_basebackup, and there when trying to access the DB using psql I get: > > WARNING: database "foo" has a collation version mismatch > DETAIL: The database was created using collation version 43.0, but the operating system provides version 34.0. > HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE foo REFRESH COLLATION VERSION, or build PostgreSQL with the right library version. > The OS are both FreeBSD, but the master runs 14.3, the replica runs 13.5. PostgreSQL packages are built with ICU support by default, the versions used are identical: icu-76.1 > Could it be that the OS (libc) needs to be the same version? I hoped it would be enough to use the same ICU. Depends what Provider you used in those DBs: https://www.postgresql.org/docs/current/locale.html#LOCALE-PROVIDERS Having the same ICU is good, but do your DBs use that provider? If not, and you're using the libc one, then yes, the libc version matters. Myself I use the new builtin provider to avoid OS portability issues. --DD ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Collation again here @ 2026-01-08 13:18 Rihad <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Rihad @ 2026-01-08 13:18 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: pgsql-general On 1/8/26 4:48 PM, Dominique Devienne wrote: > On Thu, Jan 8, 2026 at 1:39 PM Rihad<[email protected]> wrote: >> Hi, guys. Just pg_upgraded our PG from 13.x to 18.1, rebuilt all indexes concurrently and issued ALTER DATABASE foo REFRESH COLLATION VERSION >> Everything's fine on the master server, no warnings etc. Then I set up a replica using pg_basebackup, and there when trying to access the DB using psql I get: >> >> WARNING: database "foo" has a collation version mismatch >> DETAIL: The database was created using collation version 43.0, but the operating system provides version 34.0. >> HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE foo REFRESH COLLATION VERSION, or build PostgreSQL with the right library version. >> The OS are both FreeBSD, but the master runs 14.3, the replica runs 13.5. PostgreSQL packages are built with ICU support by default, the versions used are identical: icu-76.1 >> Could it be that the OS (libc) needs to be the same version? I hoped it would be enough to use the same ICU. > Depends what Provider you used in those DBs: > https://www.postgresql.org/docs/current/locale.html#LOCALE-PROVIDERS > Having the same ICU is good, but do your DBs use that provider? If > not, and you're using the libc one, then yes, the libc version > matters. > Myself I use the new builtin provider to avoid OS portability issues. --DD Hi, our locale/collate/whatever is en_US.UTF-8 (as set by these pg_upgrade flags: --encoding=utf-8 --locale=en_US.UTF-8) Looking into pg_collation system table that collation has collprovide="c". First I thought "c" meant libc, but this article states that "c" means PG Internal provider, and libc would have been "l". https://medium.com/@adarsh2801/understanding-collations-in-postgresql-648e4fa333e1 1. */PostgreSQL Internal Provider (‘c’) /*: Introduced in Postgres 15. This built-in collation support is System/OS agnostic. 2. */System Library Provider (‘l’) : /*Uses GNU C library and hence is OS locale dependent. 3. */ICU — International Components for Unicode (‘i’) : /*Uses ICU library for unicode-aware collation. We only have "i" & "c" in pg_collation. And we aren't using any of "i" it seems. All this locale/encoding/collate stuff is too much for me to handle, sorry) So if we are using the internal (builtin) "c" provider how come the PG 18.1 run on FreeBSD 13.5 version shows warnings that the system version is 34.0? The article must be wrong I guess. Then upgrading 13.5 to 14.3 is our only option. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Collation again here @ 2026-01-08 14:02 Dominique Devienne <[email protected]> parent: Rihad <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Dominique Devienne @ 2026-01-08 14:02 UTC (permalink / raw) To: Rihad <[email protected]>; +Cc: pgsql-general On Thu, Jan 8, 2026 at 2:18 PM Rihad <[email protected]> wrote: > We only have "i" & "c" in pg_collation. And we aren't using any of "i" it seems. All this locale/encoding/collate stuff is too much for me to handle, sorry) As Daniel already told you, it's pg_database.datlocprovider that matters. > So if we are using the internal (builtin) "c" provider how come the PG 18.1 run on FreeBSD 13.5 version shows warnings that the system version is 34.0? "c" is NOT built-in. It's implemented thanks to the OS' libc as a dependency. "b" is built-in, i.e. entirely within postgres, w/o external dependencies (like icu or libc). New in PostgreSQL 18. > Then upgrading (the OS from) 13.5 to 14.3 is our only option. Given you're likely on "c", probably. As recommeded by Daniel. --DD ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Collation again here @ 2026-01-08 16:04 Adrian Klaver <[email protected]> parent: Rihad <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2026-01-08 16:04 UTC (permalink / raw) To: Rihad <[email protected]>; Dominique Devienne <[email protected]>; +Cc: pgsql-general On 1/8/26 05:18, Rihad wrote: > On 1/8/26 4:48 PM, Dominique Devienne wrote: > > Looking into pg_collation system table that collation has > collprovide="c". First I thought "c" meant libc, but this article states > that "c" means PG Internal provider, and libc would have been "l". > > https://medium.com/@adarsh2801/understanding-collations-in- > postgresql-648e4fa333e1 > > 1. */PostgreSQL Internal Provider (‘c’) /*: Introduced in Postgres 15. > This built-in collation support is System/OS agnostic. > 2. */System Library Provider (‘l’) : /*Uses GNU C library and hence is > OS locale dependent. > 3. */ICU — International Components for Unicode (‘i’) : /*Uses ICU > library for unicode-aware collation. This is what the docs are for: https://www.postgresql.org/docs/current/catalog-pg-collation.html "collprovider char Provider of the collation: d = database default, b = builtin, c = libc, i = icu " And https://www.postgresql.org/docs/current/locale.html#LOCALE-PROVIDERS " 23.1.4. Locale Providers A locale provider specifies which library defines the locale behavior for collations and character classifications. The commands and tools that select the locale settings, as described above, each have an option to select the locale provider. Here is an example to initialize a database cluster using the ICU provider: initdb --locale-provider=icu --icu-locale=en See the description of the respective commands and programs for details. Note that you can mix locale providers at different granularities, for example use libc by default for the cluster but have one database that uses the icu provider, and then have collation objects using either provider within those databases. Regardless of the locale provider, the operating system is still used to provide some locale-aware behavior, such as messages (see lc_messages). The available locale providers are listed below: builtin The builtin provider uses built-in operations. Only the C, C.UTF-8, and PG_UNICODE_FAST locales are supported for this provider. The C locale behavior is identical to the C locale in the libc provider. When using this locale, the behavior may depend on the database encoding. The C.UTF-8 locale is available only for when the database encoding is UTF-8, and the behavior is based on Unicode. The collation uses the code point values only. The regular expression character classes are based on the "POSIX Compatible" semantics, and the case mapping is the "simple" variant. The PG_UNICODE_FAST locale is available only when the database encoding is UTF-8, and the behavior is based on Unicode. The collation uses the code point values only. The regular expression character classes are based on the "Standard" semantics, and the case mapping is the "full" variant. icu The icu provider uses the external ICU library. PostgreSQL must have been configured with support. ICU provides collation and character classification behavior that is independent of the operating system and database encoding, which is preferable if you expect to transition to other platforms without any change in results. LC_COLLATE and LC_CTYPE can be set independently of the ICU locale. Note For the ICU provider, results may depend on the version of the ICU library used, as it is updated to reflect changes in natural language over time. libc The libc provider uses the operating system's C library. The collation and character classification behavior is controlled by the settings LC_COLLATE and LC_CTYPE, so they cannot be set independently. Note The same locale name may have different behavior on different platforms when using the libc provider. " Rather then some made up gibberish. > > > We only have "i" & "c" in pg_collation. And we aren't using any of "i" > it seems. All this locale/encoding/collate stuff is too much for me to > handle, sorry) > > So if we are using the internal (builtin) "c" provider how come the PG > 18.1 run on FreeBSD 13.5 version shows warnings that the system version > is 34.0? > > The article must be wrong I guess. > > Then upgrading 13.5 to 14.3 is our only option. > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-01-08 16:04 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-01-08 12:48 Re: Collation again here Dominique Devienne <[email protected]> 2026-01-08 13:18 ` Rihad <[email protected]> 2026-01-08 14:02 ` Dominique Devienne <[email protected]> 2026-01-08 16:04 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox