Re: docs: Include database collation check on SQL from alter_collation.sgml - Mailing list pgsql-hackers

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