Re: pg_upgrade fails to detect unsupported arrays and ranges

> On 28 Apr 2021, at 17:09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> [ blast-from-the-past department ]
>
> I wrote:
>> Daniel Gustafsson <daniel(at)yesql(dot)se> writes:
>>> I can see the appeal of
>>> including it before the wrap, even though I personally would've held off.
>
>> Nah, I'm not gonna risk it at this stage. I concur with your point
>> that this is an ancient bug, and one that is unlikely to bite many
>> people. I'll push it Wednesday or so.
>
> I happened across a couple of further pg_upgrade oversights in the
> same vein as 29aeda6e4 et al:

Nice find, this makes a lot of sense.

> ..the same OID was 12022 in v13, 11551 in v11, etc. So if you
> had a column of type pg_enum, you'd likely get no-such-type-OID
> failures when reading the values after an upgrade. I don't see
> much use-case for doing such a thing, so it seems saner to just
> block off the possibility rather than trying to support it.

Agreed. Having implemented basically this for Greenplum I think it’s wise to
avoid it unless we really have to, it gets very complicated once the layers of
worms are peeled back.

> The attached proposed patch fixes these cases too. I generalized
> the recursive query a little more so that it could start from an
> arbitrary query yielding pg_type OIDs, rather than just one type
> name; otherwise it's pretty straightforward.
>
> Barring objections I'll apply and back-patch this soon.

Patch LGTM on reading, +1 on applying. Being on parental leave I don’t have my
dev env ready to go so I didn’t perform testing; sorry about that.

> + pg_fatal("Your installation contains system-defined composite type(s) in user tables.\n"
> + "These type OIDs are not stable across PostgreSQL versions,\n"
> + "so this cluster cannot currently be upgraded. You can\n"
> + "remove the problem tables and restart the upgrade.\n"
> + "A list of the problem columns is in the file:\n"

Would it be helpful to inform the user that they can alter/drop just the
problematic columns as a potentially less scary alternative to dropping the
entire table?

> - * The type of interest might be wrapped in a domain, array,
> + * The types of interest might be wrapped in a domain, array,

Shouldn't this be "type(s)” as in the other changes here?

--
Daniel Gustafsson https://vmware.com/