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.schra...@innogames.de
