Re: [ADMIN] Problems with enums after pg_upgrade - Mailing list pgsql-hackers

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