Default collation changes leave indexes in invalid state

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Default collation changes leave indexes in invalid state

Vincent Van Driessche
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

Reply | Threaded
Open this post in threaded view
|

Re: Default collation changes leave indexes in invalid state

Tom Lane-2
Vincent Van Driessche <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Default collation changes leave indexes in invalid state

Vincent Van Driessche
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 ([hidden email]) wrote:

Vincent Van Driessche <[hidden email]> 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

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

Reply | Threaded
Open this post in threaded view
|

Re: Default collation changes leave indexes in invalid state

Alvaro Herrera-9
On 2019-Sep-11, Vincent Van Driessche wrote:

> 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?

I don't think there's a supported way to change the default collation on
an existing database.  You would have to dump and restore in order to
achieve that cleanly.  That would be my advice.

Part of the reason that there isn't a way to do this is that it isn't a
trivial change such as a single catalog update.  If you want to use this
hack, fine -- it's your database.  However, as you noticed, you will
also have to reindex many indexes (which? it's up to you to find out),
and there likely are other changes that you need to do.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services