Re: Q: fixing collation version mismatches

Le lun. 14 nov. 2022 à 05:58, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> a
écrit :

> Am Sun, Nov 13, 2022 at 12:46:53PM -0800 schrieb Christophe Pettus:
>
> > > On Nov 13, 2022, at 12:45, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
> wrote:
> > > REINDEX DATABASE db_in_question;
> > > ALTER DATABASE db_in_question REFRESH COLLATION VERSION;
> > > ALTER COLLATION every_collation_from_pg_collation REFRESH VERSION;
> >
> > I may be totally off-base here, but shouldn't the REINDEX be the last
> step?
>
> To my understanding, the REFRESH statements "merely" update
> the version information stored in the related objects. They
> do not change anything else; and the REINDEX does not
> reference them in any way.
>
> I suppose the REINDEX goes first as it does the actual fixing
> of now-invalid objects by rebuilding them. After that one is
> back to a usable database state, even if left with pesky
> (albeit harmless) warnings on version mismatches -- which to
> get rid of one runs the REFRESH statements.
>
> Or so my understanding...
>

yes exactly. but it's likely that people will have some form of automation
to run the reindex if there's any discrepancy between the recorded
collation version and recorded version, so if you first fix the versions
metada and then encounter any error during the reindex, you won't know if
you need to reindex or not and might end up with corrupt indexes.

>
> Which is why my question still stands: does the above
> three-strikes operation safely take care of any collation
> issues that may currently exist in a database ?
>

yes

>