Re: [HACKERS] [ADMIN] Problems with enums after pg_upgrade
Hello again,
well, still everything is working.
What information do you need to get into this issue?
Well, so far i can say, we dont use ALTER TYPE ADD VALUE. We use some
more or less changed enum_add and enum_del (Which are appended at the
end) to be able to change enums within transactions.
And that this happened to the beta server and not to the staging server,
might be because we sometimes have to drop the whole stuff of staging,
because of some failures we did, so old enum values will not be
persistent in old indexes.
if you need more info, just ask. :)
regards Bernhard
SET check_function_bodies = false;
CREATE OR REPLACE FUNCTION enum_add (enum_name character varying,
enum_elem character varying) RETURNS void
AS
$body$
DECLARE
_enum_typid INTEGER;
version_int INTEGER;
_highest_enumsortorder REAL;
BEGIN
-- get enumtypid
SELECT oid FROM pg_type WHERE typtype='e' AND typname=enum_name
INTO _enum_typid;
SELECT INTO version_int setting FROM pg_settings WHERE name =
'server_version_num';
--postgres 9.2 or higher
IF version_int > 90200 THEN
SELECT MAX(enumsortorder) FROM pg_enum WHERE enumtypid =
_enum_typid INTO _highest_enumsortorder;
-- check if elem already exists in enum
IF NOT EXISTS (SELECT * FROM pg_enum WHERE enumlabel =
enum_elem AND enumtypid = _enum_typid) THEN
INSERT INTO pg_enum(enumtypid, enumlabel, enumsortorder)
VALUES (
_enum_typid,
enum_elem,
_highest_enumsortorder + 1
);
END IF;
ELSE
-- check if elem already exists in enum
IF NOT EXISTS (SELECT * FROM pg_enum WHERE enumlabel =
enum_elem AND enumtypid = _enum_typid) THEN
INSERT INTO pg_enum(enumtypid, enumlabel) VALUES (
_enum_typid,
enum_elem
);
END IF;
END IF;
END;
$body$
LANGUAGE plpgsql;
--
-- Definition for function enum_del:
--
CREATE OR REPLACE FUNCTION enum_del (enum_name character varying,
enum_elem character varying) RETURNS void
AS
$body$
DECLARE
type_oid INTEGER;
rec RECORD;
sql VARCHAR;
ret INTEGER;
BEGIN
SELECT pg_type.oid
FROM pg_type
WHERE typtype = 'e' AND typname = enum_name
INTO type_oid;
-- check if enum exists
IF NOT EXISTS (SELECT * FROM pg_enum WHERE enumtypid = type_oid) THEN
RETURN;
END IF;
-- check if element in enum exists
IF NOT FOUND THEN
RAISE EXCEPTION 'Cannot find a enum: %', enum_name;
END IF;
-- Check column DEFAULT value references.
SELECT *
FROM
pg_attrdef
JOIN pg_attribute ON attnum = adnum AND atttypid = type_oid
JOIN pg_class ON pg_class.oid = attrelid
JOIN pg_namespace ON pg_namespace.oid = relnamespace
WHERE
adsrc = quote_literal(enum_elem) || '::' || quote_ident(enum_name)
LIMIT 1
INTO rec;
IF FOUND THEN
RAISE EXCEPTION
'Cannot delete the ENUM element %.%: column %.%.% has
DEFAULT value of ''%''',
quote_ident(enum_name), quote_ident(enum_elem),
quote_ident(rec.nspname), quote_ident(rec.relname),
rec.attname, quote_ident(enum_elem);
END IF;
-- Check data references.
FOR rec IN
SELECT *
FROM
pg_attribute
JOIN pg_class ON pg_class.oid = attrelid
JOIN pg_namespace ON pg_namespace.oid = relnamespace
WHERE
atttypid = type_oid
AND relkind = 'r'
LOOP
sql :=
'SELECT 1 FROM ONLY '
|| quote_ident(rec.nspname) || '.'
|| quote_ident(rec.relname) || ' '
|| ' WHERE '
|| quote_ident(rec.attname) || ' = '
|| quote_literal(enum_elem)
|| ' LIMIT 1';
EXECUTE sql INTO ret;
IF ret IS NOT NULL THEN
RAISE EXCEPTION
'Cannot delete the ENUM element %.%: column %.%.%
contains references',
quote_ident(enum_name), quote_ident(enum_elem),
quote_ident(rec.nspname), quote_ident(rec.relname),
rec.attname;
END IF;
END LOOP;
-- OK. We may delete.
DELETE FROM pg_enum WHERE enumtypid = type_oid AND enumlabel =
enum_elem;
END;
$body$
LANGUAGE plpgsql;
--
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
Re: [HACKERS] [ADMIN] Problems with enums after pg_upgrade
On 12/18/2012 09:38 PM, Tom Lane wrote: Andrew Dunstan 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, thank youuu ll. :) 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.schra...@innogames.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ADMIN] Problems with enums after pg_upgrade
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.schra...@innogames.de
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ADMIN] Problems with enums after pg_upgrade
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
Re: [HACKERS] [ADMIN] Problems with enums after pg_upgrade
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.schra...@innogames.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
