Problems with enums after pg_upgrade

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.

thanks in advance
Bernhard Schrader

--
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

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?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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.

Can anyone suggest why enum upgrade is failing for this user? What
should he be checking for?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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

--
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

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

On Tue, Dec 18, 2012 at 10:52:46AM -0500, Andrew Dunstan wrote:
> 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.

Yes, this matches what I thought too. You see the
binary_upgrade.set_next_pg_enum_oid() calls in pg_dump --binary-upgrade
--schema-only and those set the oid of the newly created enum.

I agree you would need to run this on the _old_ cluster for us to figure
out how it failed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 12/18/2012 05:22 PM, Bruce Momjian wrote:
> On Tue, Dec 18, 2012 at 10:52:46AM -0500, Andrew Dunstan wrote:
>> 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.
> Yes, this matches what I thought too. You see the
> binary_upgrade.set_next_pg_enum_oid() calls in pg_dump --binary-upgrade
> --schema-only and those set the oid of the newly created enum.
>
> I agree you would need to run this on the _old_ cluster for us to figure
> out how it failed.
>
Hey,
i just made a testrun, i restored a dump to a testmachine with 9.0
running, made a pg_dump --binary-upgrade --schema-only of that, made my
upgrade to 9.2, after that i checked the schema dump and the values of
the enumtypid in the 9.2 database and they were identically. Thats how
it is expected to be.

Nevertheless this didn't worked with the beta server. but i have no dump
to prove this. Beside the fact that i want to fix my db's, i would also
like to help to improve the upgrade process, but i have no clue right
now how i could do this. i think i will try some other dbs to check if
there maybe an error occurs.

Beside of that, we tested a little bit more with the failing query:
The statement which is causing the error is a big UPDATE-statement with
FROM. After some testing we figured out that the subselect in the
FROM-clause is working fine. And if we simplify the UPDATE-statement
it's also working. We're able to show the data and we're able to do
simple updates on the table. But the two things combined are not
working. We checked the data from the subselect - it's correct. In the
FROM-clause we're using a window-function to calculate a ranking. Do you
know, if there is any mapping for window-functions which has to deal
with enums?

regards

--
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

Bernhard Schrader <bernhard(dot)schrader(at)innogames(dot)de> writes:
> Beside of that, we tested a little bit more with the failing query:
> The statement which is causing the error is a big UPDATE-statement with
> FROM. After some testing we figured out that the subselect in the
> FROM-clause is working fine. And if we simplify the UPDATE-statement
> it's also working. We're able to show the data and we're able to do
> simple updates on the table. But the two things combined are not
> working.

Does the table being updated have any indexes on enum columns? I'm
suspicious that the bogus OID is in an index page somewhere, and not
in the table at all.

If that is the answer, then reindexing said index would get rid of
the problem (as well as all evidence that would help us find out how
it happened ...)

regards, tom lane

On 12/18/2012 01:24 PM, Tom Lane wrote:
> Bernhard Schrader <bernhard(dot)schrader(at)innogames(dot)de> writes:
>> Beside of that, we tested a little bit more with the failing query:
>> The statement which is causing the error is a big UPDATE-statement with
>> FROM. After some testing we figured out that the subselect in the
>> FROM-clause is working fine. And if we simplify the UPDATE-statement
>> it's also working. We're able to show the data and we're able to do
>> simple updates on the table. But the two things combined are not
>> working.
> Does the table being updated have any indexes on enum columns? I'm
> suspicious that the bogus OID is in an index page somewhere, and not
> in the table at all.
>
> If that is the answer, then reindexing said index would get rid of
> the problem (as well as all evidence that would help us find out how
> it happened ...)
>
>

Unless they can make a physical backup of the datadir first.

cheers

andrew

On 2012-12-18 13:24:12 -0500, Tom Lane wrote:
> Bernhard Schrader <bernhard(dot)schrader(at)innogames(dot)de> writes:
> > Beside of that, we tested a little bit more with the failing query:
> > The statement which is causing the error is a big UPDATE-statement with
> > FROM. After some testing we figured out that the subselect in the
> > FROM-clause is working fine. And if we simplify the UPDATE-statement
> > it's also working. We're able to show the data and we're able to do
> > simple updates on the table. But the two things combined are not
> > working.
>
> Does the table being updated have any indexes on enum columns? I'm
> suspicious that the bogus OID is in an index page somewhere, and not
> in the table at all.

I already wondered whether it could be a problem caused by pg_upgrade
not ignoring invalid indexes until recently, but I don't really see how
it could cause an invalid oid to end up in the index.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2012-12-18 13:24:12 -0500, Tom Lane wrote:
>> Does the table being updated have any indexes on enum columns? I'm
>> suspicious that the bogus OID is in an index page somewhere, and not
>> in the table at all.

> I already wondered whether it could be a problem caused by pg_upgrade
> not ignoring invalid indexes until recently, but I don't really see how
> it could cause an invalid oid to end up in the index.

It seems like this might indicate a flaw in our scheme for preventing
uncommitted enum values from getting into tables/indexes. Hard to see
what though.

Bernhard, if you do identify a particular index as being the source of
the failure, that would at least tell us for sure which enum type is
at fault. I don't suppose you would have any info about the history
of that enum type in your database? The fact that the OID is odd
implies that it belonged to a value that was added by ALTER TYPE ADD
VALUE, but what we'd want is some context around any past uses of
that command, especially if they failed or were rolled back.

regards, tom lane

On 12/18/2012 02:34 PM, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>> On 2012-12-18 13:24:12 -0500, Tom Lane wrote:
>>> Does the table being updated have any indexes on enum columns? I'm
>>> suspicious that the bogus OID is in an index page somewhere, and not
>>> in the table at all.
>> I already wondered whether it could be a problem caused by pg_upgrade
>> not ignoring invalid indexes until recently, but I don't really see how
>> it could cause an invalid oid to end up in the index.
> It seems like this might indicate a flaw in our scheme for preventing
> uncommitted enum values from getting into tables/indexes. Hard to see
> what though.
>
> Bernhard, if you do identify a particular index as being the source of
> the failure, that would at least tell us for sure which enum type is
> at fault. I don't suppose you would have any info about the history
> of that enum type in your database? The fact that the OID is odd
> implies that it belonged to a value that was added by ALTER TYPE ADD
> VALUE, but what we'd want is some context around any past uses of
> that command, especially if they failed or were rolled back.
>
>

He's upgrading from 9.0, which didn't have enum extension at all, and
where odd enums didn't mean anything special.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> He's upgrading from 9.0, which didn't have enum extension at all, and
> where odd enums didn't mean anything special.

Really? The noncontiguous pg_enum OIDs shown in
http://archives.postgresql.org/pgsql-hackers/2012-12/msg01089.php
suggest strongly that *something's* been done to that type since
it was created.

regards, tom lane

On 12/18/2012 02:58 PM, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> He's upgrading from 9.0, which didn't have enum extension at all, and
>> where odd enums didn't mean anything special.
> Really? The noncontiguous pg_enum OIDs shown in
> http://archives.postgresql.org/pgsql-hackers/2012-12/msg01089.php
> suggest strongly that *something's* been done to that type since
> it was created.

That's what he said.

People have been known to hack pg_enum on their own, especially before
we added enum extension.

Of course, if they do that they get to keep both pieces.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> People have been known to hack pg_enum on their own, especially before
> we added enum extension.
> Of course, if they do that they get to keep both pieces.

Yeah ... this would be very readily explainable if there had been a
manual deletion from pg_enum somewhere along the line. Even if there
were at that time no instances of the OID left in tables, there could
be some in upper btree pages. They'd have caused no trouble in 9.0
but would (if odd) cause trouble in 9.2.

Of course, this theory doesn't explain why the problem was seen on some
copies and not others cloned from the same database --- unless maybe
there had been an index page split on the master in between the
clonings, and that moved the troublesome OID into a position where it
was more likely to get compared-to. That's not a hugely convincing
explanation though.

regards, tom lane

On 12/18/2012 09:38 PM, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> People have been known to hack pg_enum on their own, especially before
>> we added enum extension.
>> Of course, if they do that they get to keep both pieces.
> Yeah ... this would be very readily explainable if there had been a
> manual deletion from pg_enum somewhere along the line. Even if there
> were at that time no instances of the OID left in tables, there could
> be some in upper btree pages. They'd have caused no trouble in 9.0
> but would (if odd) cause trouble in 9.2.
>
> Of course, this theory doesn't explain why the problem was seen on some
> copies and not others cloned from the same database --- unless maybe
> there had been an index page split on the master in between the
> clonings, and that moved the troublesome OID into a position where it
> was more likely to get compared-to. That's not a hugely convincing
> explanation though.
>
> regards, tom lane
>

Guys, thaaaaank youuu aaaall. :) reindex helped, did reindex on two
tables, and everything is now working like expected.

I will provide tomorrow all information which could help to understand
everything in detail, but now it's gonna be late in germany :). and i
got a headache of all this stuff ^^

Thanks so much!!!

--
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