Re: [ADMIN] Problems with enums after pg_upgrade
От | Andrew Dunstan |
---|---|
Тема | Re: [ADMIN] Problems with enums after pg_upgrade |
Дата | |
Msg-id | 50D0914E.3040508@dunslane.net обсуждение исходный текст |
Ответ на | Re: [ADMIN] Problems with enums after pg_upgrade (Bernhard Schrader <bernhard.schrader@innogames.de>) |
Ответы |
Re: [ADMIN] Problems with enums after pg_upgrade
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: