Q: documentation improvement re collation version mismatch

[Karsten Hilbert]

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