Re: Preserve versions of initdb-created collations in pg_upgrade

On Sat, Dec 21, 2019 at 7:38 PM Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 2019-10-29 03:33, Thomas Munro wrote:
> > Seems to work as described with -E UTF-8, but it fails with clusters
> > using -E SQL_ASCII. That causes the pg_upgrade check to fail on
> > machines where that is the default encoding chosen by initdb (where
> > unpatched master succeeds):
> >
> > pg_restore: creating COLLATION "pg_catalog.af-NA-x-icu"
> > pg_restore: while PROCESSING TOC:
> > pg_restore: from TOC entry 1700; 3456 12683 COLLATION af-NA-x-icu tmunro
> > pg_restore: error: could not execute query: ERROR: collation
> > "pg_catalog.af-NA-x-icu" for encoding "SQL_ASCII" does not exist
> > Command was: ALTER COLLATION pg_catalog."af-NA-x-icu" OWNER TO tmunro;
>
> This could be addressed by using is_encoding_supported_by_icu() in
> pg_dump to filter out collations with unsupported encodings.
>
> However, the more I look at this whole problem, I'm wondering whether it
> wouldn't be preferable to avoid this whole mess by just not creating any
> collations in initdb. What do you think?

I think this problem goes away if we commit the per-object collation
version patch set[1]. It drops the collversion column, and Julien's
recent versions handle pg_upgrade quite well, as long as a collation
by the same name exists in the target cluster. In that universe, if
initdb didn't create them, we'd have to tell people to create all
necessary collations manually before doing a pg_upgrade into it, and
that doesn't seem great. Admittedly there might be some weird cases
where a collation is somehow completely different but has the same
name.

[1] https://www.postgresql.org/message-id/flat/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com