Default collation changes leave indexes in invalid state - Mailing list pgsql-bugs

SELECT pg_class.relname AS Index, pg_attribute.attname AS Column, CASE WHEN pg_attribute.attcollation = 0 THEN '<none>' ELSE pg_collation.collname END AS Collation 

FROM pg_class LEFT JOIN pg_attribute ON pg_class.oid = pg_attribute.attrelid LEFT JOIN pg_collation ON pg_attribute.attcollation = pg_collation.oid WHERE pg_class.relam != 0 AND  pg_collation.collname = 'default’;

```

I’ve used the above query to validate this behaviour. By running it before and after changing the `C` collation into `en_US.UTF-8`:

```

UPDATE pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8'

```

I’m aware that the most accepted way to alter collation types right now is to remember to recreate indexes based on the new collation (before or after, whatever takes preference), but I feel like triggering an update of the relevant collations (referring to “default”) when changes to the collation type are detected makes a lot of sense. (A form of cascading update) as this would allow the old indexes to be queried without issues. (I’d think)

Please let me know if this makes sense or not, I got referred here from the slack channel (https://postgresteam.slack.com/archives/C0FS3UTAP/p1568186700106500)