Received-SPF: pass (mail123-ch1: domain of innogames.de designates
	157.56.248.5 as permitted sender) client-ip=157.56.248.5;
	envelope-from=bernhrad.schrader@innogames.de;
	helo=AMSPRD0310HT001.eurprd03.prod.outlook.com ; .outlook.com ;
Message-ID: <50D0B0AD.9010905@innogames.de>
Date: Tue, 18 Dec 2012 19:06:37 +0100
From: Bernhard Schrader <bernhard.schrader@innogames.de>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: Bruce Momjian <bruce@momjian.us>
CC: Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development
	<pgsql-hackers@postgreSQL.org>
Subject: Re: [ADMIN] Problems with enums after pg_upgrade
References: <50CEFE95.9040301@innogames.de>
 <20121218014137.GB29143@momjian.us>
	<50D02D13.3010004@innogames.de> <50D0914E.3040508@dunslane.net>
	<20121218162256.GA24700@momjian.us>
In-Reply-To: <20121218162256.GA24700@momjian.us>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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=20
running, made a pg_dump --binary-upgrade --schema-only of that, made my=20
upgrade to 9.2, after that i checked the schema dump and the values of=20
the enumtypid in the 9.2 database and they were identically. Thats how=20
it is expected to be.

Nevertheless this didn't worked with the beta server. but i have no dump=20
to prove this. Beside the fact that i want to fix my db's, i would also=20
like to help to improve the upgrade process, but i have no clue right=20
now how i could do this. i think i will try some other dbs to check if=20
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=20
FROM. After some testing we figured out that the subselect in the=20
FROM-clause is working fine. And if we simplify the UPDATE-statement=20
it's also working. We're able to show the data and we're able to do=20
simple updates on the table. But the two things combined are not=20
working. We checked the data from the subselect - it's correct. In the=20
FROM-clause we're using a window-function to calculate a ranking. Do you=20
know, if there is any mapping for window-functions which has to deal=20
with enums?

regards

--=20
Bernhard Schrader
System Administration

InnoGames GmbH
Harburger Schlo=DFstra=DFe 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 =96 bernhard.schrader@innogames.de


--=20
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers