Q: documentation improvement re collation version mismatch - Mailing list pgsql-general

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