Re: Collation versioning

On Fri, Nov 8, 2019 at 10:20 AM Christoph Berg <myon(at)debian(dot)org> wrote:
>
> Re: Laurenz Albe 2019-11-08 <3c3b9ff84d21acf3188558928249d04db84ea2e9(dot)camel(at)cybertec(dot)at>
> > #3 is the best proposal, but there is still the need to run
> > ALTER INDEX on all affected indexes to keep PostgreSQL from nagging.
> > Perhaps the situation could be improved with a pg_upgrade option
> > --i-know-my-indexes-are-fine that causes a result like #2.
> > Together with a bold note in the release notes, this may relieve
> > the pain.
>
> Ack.

+1

> We should also try to make the actual commands more accessible.
> Instead of having the user specify a version number we could as well
> determine from the current state of the system as in
> ALTER INDEX ... DEPENDS ON 'version-number-I-never-heard-of-before'

This one is needed for pg_upgrade on later version, but I agree that
it shouldn't be exposed to users.

> could it just be
> ALTER INDEX ... COLLATION IS CURRENT

this sounds like a better idea, though this should probably work at
the collation lever rather than index level. I think that we should
offer users this but with multiple filter, like:

- mark all indexes' collation version dependencies as current version
- mark all indexes' dependencies on a specific collation and collation
version as current version
- mark all indexes' dependencies on a specific collation (any version)
as current version

> or, given the general action to take is reindexing, how about a no-op reindex?
> REINDEX INDEX ... METADATA ONLY
>
> That might look less scary to the average end user.

This should be scary, as any misuse can lead to hidden corruption. If
a user isn't sure of what to do, a plain REINDEX is the safe (and
painful) way to go.

> Do we even think people upgrade PG and the OS at the same time?
> pg_upgrade might frequently actually be invoked on an otherwise
> unchanged system, so we could even make "collations are fine" the
> default for pg_upgrade. And maybe have a switch like pg_upgrade --os-upgrade
> that reverses this.

+1