I don't know whether this is the correct package to report this to, so
please feel free to reassign as appropriate.

I recently started getting warnings such as the following from my
postgresql backup script:

WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.35, but
the operating system provides version 2.36.
HINT:  Rebuild all objects in this database that use the default
collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION,
or build PostgreSQL with the right library version.

I don't know exactly where the source of this problem is, and there's
no guidance as to how to fix it; for example, how do I rebuild all the
objects in the database that use the default collation?  I didn't
manually build this database in the first place.  A web search was
only minimally useful for this.

Something in a NEWS file or in README.Debian giving guidance would be
very helpful here.

Best wishes,

   Julian

ALTER DATABASE postgres REFRESH COLLATION VERSION

The cause for that (IIUC) is that the database was created when libc6 was at
version 2.35 and it has since been upgraded to 2.36.

See also https://bugs.debian.org/1021074
issue may only occur with people running Testing or Unstable.
OTOH: There are now (at least) 2 people who ran into it and didn't know what
to do with/about it.

HTH,
  Diederik

Thanks!  The messages were significantly more verbose than that (they
also talked about rebuilding indexes and the like), so I didn't want
to run this command without knowing it was safe to do so.

But it will happen when people upgrade their stable machines to
bookworm once it is released.

Best wishes,

   Julian

AFAIK, that upgrade procedure is different and those users will not run into this issue.

Thanks!  It's worth checking, of course!  (Unfortunately, I'm not in a
position to be able to do so.)

Best wishes,

   Julian

Hi,

While those are only warnings, you will actually get errors
when you try to create new databases:

And those errors are relatively frequent for users of rolling releases
derived from Debian like Kali Linux. They even have a page for this:
https://www.kali.org/docs/troubleshooting/postgresql-collation-mismatch-error/

I think it would be nice if that operation could be automated.
With dpkg triggers, it's easy to schedule a script on the postgresql
package side when libc6 (or locales?) gets upgraded. We can record the
libc6 version at installation time, and compare the version in the trigger
run, and if the major version has changed, then we run the required
ALTER DATABASE commands.

What do you think Christoph?

Cheers,

PS: Not quite sure if there's a good CLI command to directly get the
"collation version".

Re: Raphael Hertzog

The correct default action is to reindex all text indexes. ALTER
DATABASE is just a shortcut for when you know that the collation has
not changed.

I wouldn't want to wire that decision into postinst scripts. On
Debian, it's not a problem since it only happens at release upgrade
time.

In the OS, it's the glibc version. In PG, it's
pg_database.datcollversion.

Christoph

Hi,

How can we determine if the collation information has changed between
two glibc releases? (cc Aurélien in case he knows)

I understand the correct default action is to reindex all text fields,
but arguably the "template1" and "template0" databases
are mostly empty and likely have only ascii content where the collation
information has no impact (or rather almost 0% chance of having been
modified), so it should still be safe to perform those operations on those
databases no?

I would argue that the "postgres" database could likely be in the same
set, while it's not empty, does it have any non-ASCII content where the
collation information might have an impact on indexes? Even if yes, I
guess that the database is small enough that rebuilding all the text
indexes is not very costly and can be automated too. What are the required
commands to perform this reindex?

Cheers,

Re: Raphael Hertzog

The problem is, no one knows. Or else PG would already use that info.

You could alternatively use the libicu collations, but these have
effectively the same problem, just their track record of doing changes
is more coordinated.

template0 is always empty, yes.

For template1 and postgres, you could just run `reindex database template1;`

Christoph

Hi,

I don't think there is an easy way to determine that. On the other hand,
I think it's safe to assume that the collation information only
changes for an upstream release, not for a debian revision.

For a new upstream release, most of the time most of the locales do not
see any collation change, but there is almost always at least one locale
with collation changes (although that was not true for glibc 2.41).

Regards
Aurelien