Re: [ADMIN] Problems with enums after pg_upgrade

On 12/18/2012 03:45 AM, Bernhard Schrader wrote:
> On 12/18/2012 02:41 AM, Bruce Momjian wrote:
>> On Mon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote:
>>> Hello together,
>>>
>>> last thursday I upgraded one of our 9.0.6 postgresql servers to
>>> 9.2.2 with pg_upgrade. So far everything seemed to work but we now
>>> discover problems with the enum types. If we run one specific query
>>> it breaks all time with such an error message:
>>>
>>> ERROR: invalid internal value for enum: 520251
>>>
>>> if this number should represent the enumtypid it is not existing
>>> anymore in pg_enum.
>>>
>>> How could i solve this problem? should we regenerate all enums? or
>>> what could we do?
>>> Hopefully anyone has a clue, google doesn't seem to be the ressource
>>> for this problem.
>> We seriously tested the enum code so I am pretty confused why this is
>> failing. If you do pg_dump --binary-upgrade --schema-only, do you see
>> that a number like this being defined just before the enum is added?
>>
> Hi Bruce,
>
> if i am dumping this db and grepping through the dump, i can't find
> the number.
> As far as we can see, the enum that is affected has now the enumtypid
> 16728.
>
> is there a table which keeps the possible typecasts from enum to
> text/text to enum etc.? if so, maybe the mapping in here is corrupt
> since the upgrade.
>

The translations from oid to label are in pg_enum, but it looks like
somehow you have lost that mapping. I'm not sure what you've done but
AFAICT pg_upgrade is doing the right thing.

I just did this (from 9.0 to 9.2) and the pg_upgrade_dump_all.sql that
is used to create the new catalog has these lines:

-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('40804'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT
binary_upgrade.set_next_array_pg_type_oid('40803'::pg_catalog.oid);

CREATE TYPE myenum AS ENUM (
);

-- For binary upgrade, must preserve pg_enum oids
SELECT binary_upgrade.set_next_pg_enum_oid('40805'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'foo';

SELECT binary_upgrade.set_next_pg_enum_oid('40806'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'bar';

SELECT binary_upgrade.set_next_pg_enum_oid('40807'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'baz';

and this worked exactly as expected, with a table using this type
showing the expected values.

Can you produce a test case demonstrating the error?

When you run pg_upgrade, use the -r flag to keep all the intermediate
files so we can see what's going on.

It's no good dumping the new db looking for these values if they have
been lost. You would need to have a physical copy of the old db and dump
that in binary upgrade mode looking for the Oid. If you don't have a
physical copy of the old db or the intermediate dump file pg_upgrade
used then recovery is going to be pretty difficult. It's not necessarily
impossible, but it might involve you getting some outside help.

cheers

andrew