Default collation changes leave indexes in invalid state

Hi all

When making changes to the database collation types in PostgreSQL, indexes that already exist that point to the “default” collation type, remain pointed at “default”, even though the collation type that the “default” entry is referring to, no longer is valid.

```
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)

Kind Regards
Vincent Van Driessche

Sent via Migadu.com, world's easiest email hosting