Q: documentation improvement re collation version mismatch

Dear all,

regarding changed collation versions this

https://www.postgresql.org/docs/devel/sql-altercollation.html

says:

The following query can be used to identify all
collations in the current database that need to be
refreshed and the objects that depend on them:

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d JOIN pg_collation c
ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid)
ORDER BY 1, 2;

I feel the result of that query can be slightly surprising
because it does not return (to my testing) any objects
depending on the database default collation, nor the database
itself (as per a collation version mismatch in pg_database).

Now, there is a line

For the database default collation, there is an analogous
command ALTER DATABASE ... REFRESH COLLATION VERSION.

right above that query but the query comment does not really
make it clear that the database default collation is _not_
identified to be in mismatch, if so. IOW, the database
default collation may still need to be refreshed even if the
query does not return any rows.

Perhaps this query (taken from the net)

SELECT -- get collation-change endangered indices
indrelid::regclass::text,
indexrelid::regclass::text,
collname,
pg_get_indexdef(indexrelid)
FROM (
SELECT
indexrelid,
indrelid,
indcollation[i] coll
FROM
pg_index, generate_subscripts(indcollation, 1) g(i)
) s
JOIN pg_collation c ON coll=c.oid
WHERE
collprovider IN ('d', 'c')
AND
collname NOT IN ('C', 'POSIX');

could be added to the paragraph (or it could be folded into
the first query by a UNION or some such) ?

Or perhaps one could move the "ALTER DATABASE ... REFRESH
..." hint _below_ the query paragraph and add "Note: you may
need to refresh the default collation even if the query above
does not show any objects directly affected by a collation
version change" ?

Thanks for considering.

Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B