Thanks, Julien, for your explanation. > > 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). > > Indeed. The default collation is "pinned", so we don't record any dependency > on it. Indirectly we do, don't we ? Or else > > WHERE > > collprovider IN ('d', 'c') would not make much sense, right ? The comment above the query in the official documentation is rather assertive (even if may true to the letter) and may warrant some more cautionary wording ? Added, perhaps, some variation of this: > For now, the only safe way to go is either reindex everything, or everything > except some safe cases (non-partial indexes on plain-non-collatable datatypes > only). Best, Karsten
