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

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(at)gmail(dot)com>
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.
>
> --
> Matheus Alcantara
> EDB: https://www.enterprisedb.com
>