docs: Include database collation check on SQL from alter_collation.sgml

On Thu Apr 9, 2026 at 3:00 PM -03, Matheus Alcantara wrote:

Hi,

The ALTER COLLATION documentation section include a SQL that can be used
to identity all collations in the current database that need to be
refreshed due to a collation version miss match and the objects that
depend on them. However if there is objects that use the database
collation these objects are not returned by the query.

The attached patch change the query to include the database collation
check to report collation version miss match for objects that use the
database default collation as they are not stored on pg_depend.

The proposed SQL on the v1 patch still don't handle the cases where an
index is using the database default collation.

The new v2 attached handle it properly. The new query is based on the
query suggested on Locale data changes wiki page [1]https://wiki.postgresql.org/wiki/Locale_data_changes but with some
changes to check the collation version mismatch.

[1]: https://wiki.postgresql.org/wiki/Locale_data_changes

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

Hi Matheus,

I reviewed v2 of this patch.

I applied it cleanly on master. To test the actual query logic, I created a
throwaway database with a libc locale (en_US.UTF-8) that carries a real
collation version, created a table and an index that use the database's
default collation (no explicit COLLATE), and then simulated a version
mismatch by updating pg_database.datcollversion directly.

With the mismatch in place, PostgreSQL correctly warns on connect:

WARNING: database "colltest" has a collation version mismatch
DETAIL: The database was created using collation version 1.0, but the
operating system provides version 2.39.

The existing (pre-patch) query in the docs returned 0 rows in this
situation, confirming the problem described: it misses objects using the
database default collation.

The new query from this patch correctly found the affected index, with
accurate stored vs. actual version numbers:

database | table | index | collname | collprovider | collation_version |
actual_collation_version
colltest | t | t_name_idx | default | d | 1.0 | 2.39

One thing I noticed while reading the query: the WHERE clause only checks
collprovider='c' (libc) for the non-default branch. It looks like a named
collation using the ICU provider (collprovider='i') with a version mismatch
would not be caught by either branch of the OR. I haven't tested this
directly since my environment uses libc, but wanted to flag it in case it's
a gap worth addressing.

I wasn't able to build the docs locally due to an unrelated DTD/catalog
issue in my toolchain setup, so I can't comment on the rendered output, but
the query itself works as described.

Thanks for the patch.

Best regards,
Shubhra Jain
[image: Shubhra Jain]
Shubhra Jain
Junior Software Engineer
[image: Phone] (+91) 9752683649 <(+91)+9752683649> [image: Website]
www.ksolves.com [image: Ksolves - AI First, Always]

On Wed, Sep 30, 2026 at 11:50 AM Matheus Alcantara <matheusssilv97@gmail.com>
wrote: