Re: [ADMIN] Problems with enums after pg_upgrade

<pre style="margin: 0em;">On 12/18/2012 02:41 AM, Bruce Momjian wrote:
</pre><blockquote style="border-left: #5555EE solid 0.2em; margin: 0em;     padding-left: 0.85em"><pre style="margin:
0em;">OnMon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote:
 
</pre><blockquote style="border-left: #5555EE solid 0.2em; margin: 0em;       padding-left: 0.85em"><pre style="margin:
0em;">Hellotogether,
 

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.
</pre></blockquote><pre style="margin: 0em;">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?

</pre></blockquote><pre style="margin: 0em;">Hi Bruce,

</pre> <tt>if i am dumping this db and grepping through the dump, i can't find the </tt><tt>number. </tt> <pre
style="margin:0em;">As far as we can see, the enum that is affected has now the enumtypid 16728.
 

</pre> <tt>is there a table which keeps the possible typecasts from enum to </tt><tt>text/text to enum etc.? if so,
maybethe mapping in here is corrupt </tt><tt>since the upgrade. </tt> <pre style="margin: 0em;">regards
 


</pre> ###########<br /><br /> Hi again,<br /><br /> maybe there are more information needed to point this stuff out.
I'mnot quite sure what would be useful, so i just give you that stuff where is stumpled upon.<br /><br /> 1.) We have
somestaging servers, where i first used pg_upgrade to make sure everything is running and nothing breaks on our
beta/liveservers. And it worked there, without any problem i can use the enums which break on the beta servers<br /><br
/>2.) As mentioned, on beta servers the usage of the enum fails with error message:<br /><br /><pre style="margin:
0em;">ERROR:invalid internal value for enum: 520251
 


</pre> 3.) If i search for the enumtypid or oid in pg_enum, it is obviously not there.<br /><small><font face="Courier
New,Courier, monospace"><br /> select * from pg_enum where enumtypid=520251;<br /> (No rows)</font></small><br /><br
/><small><fontface="Courier New, Courier, monospace">select * from pg_enum where oid=520251;<br /> (No
rows)</font></small><br/><br /> 4.) If i am searching for the enumlabels which are used by the query i am getting as
enumtypid16728 which also belongs to the expected pg_type<br /><br /> 5.) pg_enum of the enumtypid looks like this<br
/><br/><small><font face="Courier New, Courier, monospace">select oid,* from pg_enum where enumtypid=16728;<br /><br />
 oid   | enumtypid | enumsortorder | enumlabel <br /> --------+-----------+---------------+-----------<br />   16729
|    16728 |             1 | att<br />   16730 |     16728 |             2 | def<br />   16731 |     16728
|            3 | all<br />  646725 |     16728 |             4 | adm_att<br />  646726 |     16728 |             5 |
adm_def</font></small><br/><br /> 6.) enumlabels adm_att and adm_def are also defined under another enumtypid, but i
thinkthis shouldn't affect anything. just wanted to mention this.<br /><br /> 7.) during pg_upgrade i used --link
method<br/><br /> Well, if you need any other info please ask. i just can't imagine why this stuff worked on staging
serversbut not on beta, as they are identical on database point of view. <br /><br /><br /><pre class="moz-signature"
cols="72">--
 
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

<a class="moz-txt-link-freetext" href="http://www.innogames.com">http://www.innogames.com</a> – <a
class="moz-txt-link-abbreviated"href="mailto:bernhard.schrader@innogames.de">bernhard.schrader@innogames.de</a>
 
</pre>