Re: [ADMIN] Problems with enums after pg_upgrade

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.

regards

###########

Hi again,

maybe there are more information needed to point this stuff out. I'm not
quite sure what would be useful, so i just give you that stuff where is
stumpled upon.

1.) We have some staging servers, where i first used pg_upgrade to make
sure everything is running and nothing breaks on our beta/live servers.
And it worked there, without any problem i can use the enums which break
on the beta servers

2.) As mentioned, on beta servers the usage of the enum fails with error
message:

ERROR: invalid internal value for enum: 520251

3.) If i search for the enumtypid or oid in pg_enum, it is obviously not
there.

select * from pg_enum where enumtypid=520251;
(No rows)

select * from pg_enum where oid=520251;
(No rows)

4.) If i am searching for the enumlabels which are used by the query i
am getting as enumtypid 16728 which also belongs to the expected pg_type

5.) pg_enum of the enumtypid looks like this

select oid,* from pg_enum where enumtypid=16728;

oid | enumtypid | enumsortorder | enumlabel
--------+-----------+---------------+-----------
16729 | 16728 | 1 | att
16730 | 16728 | 2 | def
16731 | 16728 | 3 | all
646725 | 16728 | 4 | adm_att
646726 | 16728 | 5 | adm_def

6.) enumlabels adm_att and adm_def are also defined under another
enumtypid, but i think this shouldn't affect anything. just wanted to
mention this.

7.) during pg_upgrade i used --link method

Well, if you need any other info please ask. i just can't imagine why
this stuff worked on staging servers but not on beta, as they are
identical on database point of view.

--
Bernhard Schrader
System Administration

InnoGames GmbH
Harburger Schloßstraße 28 (Channel 4) - 21079 Hamburg - Germany
Tel +49 40 7889335-53
Fax +49 40 7889335-22

Managing Directors: Hendrik Klindworth, Eike Klindworth, Michael Zillmer
VAT-ID: DE264068907 Amtsgericht Hamburg, HRB 108973

http://www.innogames.com -- bernhard(dot)schrader(at)innogames(dot)de