Thread: 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
Attachment
If I understand it correctly you are saying: “If you run queries like this, you’d better know what you are doing”?
Is there a more supported way to change the default collation on PostgreSQL?
Right now (after your response) I’d just document in our private documentation that we’d recreate indexes with fixed collation set and only then change collation defaults if we are required to in the future.
The reason I would document this privately is because I can’t seem to find any information on it on the official documentation, only hits on mailing lists or random forums.
I’m glad to help make this more clear for future users, if at all possible.
Kind Regards
Vincent Van Driessche
On 11 September 2019 at 15:39:32, Tom Lane (tgl@sss.pgh.pa.us) wrote:
Vincent Van Driessche <vincent@dabble.be> writes:
> ... 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'
That is not, and I do not think ever will be, a supported operation.
If it breaks your database it's your own fault.
regards, tom lane