Q: documentation improvement re collation version mismatch

[Karsten Hilbert <Karsten.Hilbert@xxxxxxx>]

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