Re: [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(dot)schrader(at)innogames(dot)de