Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

Hi,

Thanks a lot for the feedback, which actually solved the problem. After executing the below command, upgrade from 10 to 12 worked perfectly fine.

pgsqldb=# DROP EXTENSION pg_repack CASCADE;

Med vennlig hilsen / Best regards,
_______________________
Arne Henrik Segtnan

> 4. mai 2021 kl. 18:52 skrev Bruce Momjian <bruce(at)momjian(dot)us>:
>
> On Tue, May 4, 2021 at 12:43:36PM -0400, Tom Lane wrote:
>> Arne Henrik Segtnan <arne(at)basis-consulting(dot)com> writes:
>>> When trying to upgrade from PostgreSQL 10 to 12, the installation fails as shown below.
>>
>>> 2021-05-04 13:06:05.614 CEST [12890] postgres(at)zabbix_db ERROR: column r.relhasoids does not exist at character 1616
>>> 2021-05-04 13:06:05.614 CEST [12890] postgres(at)zabbix_db STATEMENT:
>>> -- For binary upgrade, must preserve pg_type oid
>>
>>> SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('162222'::pg_catalog.oid);
>>> -- For binary upgrade, must preserve pg_type array oid
>>> SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);
>>
>>
>>> -- For binary upgrade, must preserve pg_class oids
>>> SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);
>>
>>
>>> CREATE VIEW "repack"."tables" AS
>>> SELECT ("r"."oid")::"regclass" AS "relname",
>>> "r"."oid" AS "relid",
>>> "r"."reltoastrelid",
>>> CASE
>>> .
>>> .
>>> .
>>
>> You didn't show us the rest of the command, but it looks like
>> this view contains a reference to pg_class.relhasoids. As
>> Bruce noted, that column no longer exists, so you're not going
>> to be able to transparently upgrade this view.
>>
>> I don't know what this view is from, though the schema name
>> "repack" is suggestive. Possibly you could drop whatever it's
>> a part of, and then reinstall an updated version after upgrading?
>
> Yeah, Tom, I think you are on to something. I couldn't figure out where
> the r.relhasoids was referenced in the rest of the log output, but if it
> is on the lines that are part of CREATE VIEW, it certainly could be the
> case that the view references a pre-PG-12 column of pg_class. To fix
> it, Tom is right that removing the view then recreating it with PG-12
> pg_class column assumptions is the right fix. I actually rarely see
> this kind of failure.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us <mailto:bruce(at)momjian(dot)us>> https://momjian.us <https://momjian.us/>
> EDB https://enterprisedb.com <https://enterprisedb.com/>
>
> If only the physical world exists, free will is an illusion.