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

Hello again,<br /><br /> well, still everything is working.<br /><br /> What information do you need to get into this
issue?<br /><br /> Well, so far i can say, we dont use ALTER TYPE ADD VALUE. We use some more or less changed enum_add
andenum_del (Which are appended at the end) to be able to change enums within transactions.<br /><br /> And that this
happenedto 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. <br /><br /> if you
needmore info, just ask. :)<br /><br /> regards Bernhard<br /><br /><font size="-1">SET check_function_bodies =
false;<br/> CREATE OR REPLACE FUNCTION enum_add (enum_name character varying, enum_elem character varying) RETURNS
void<br/> AS<br /> $body$<br /> DECLARE<br />     _enum_typid INTEGER;<br />     version_int INTEGER;<br />    
_highest_enumsortorderREAL;<br /> BEGIN<br />     -- get enumtypid<br />     SELECT oid FROM pg_type WHERE typtype='e'
ANDtypname=enum_name INTO _enum_typid;<br /><br />     SELECT INTO version_int setting FROM pg_settings WHERE name =
'server_version_num';<br/>     --postgres 9.2 or higher<br />     IF version_int > 90200 THEN<br />         SELECT
MAX(enumsortorder)FROM pg_enum WHERE enumtypid = _enum_typid INTO _highest_enumsortorder;<br />         -- check if
elemalready exists in enum<br />         IF NOT EXISTS (SELECT * FROM pg_enum WHERE enumlabel = enum_elem AND enumtypid
=_enum_typid) THEN<br />             INSERT INTO pg_enum(enumtypid, enumlabel, enumsortorder) VALUES (<br />        
       _enum_typid,<br />                 enum_elem,<br />                 _highest_enumsortorder + 1<br />            
);<br/>         END IF;<br />     ELSE<br />         -- check if elem already exists in enum<br />         IF NOT
EXISTS(SELECT * FROM pg_enum WHERE enumlabel = enum_elem AND enumtypid = _enum_typid) THEN<br />             INSERT
INTOpg_enum(enumtypid, enumlabel) VALUES (<br />                 _enum_typid,<br />                 enum_elem<br />    
       );<br />         END IF;<br />     END IF;<br /> END;<br /> $body$<br />     LANGUAGE plpgsql;<br /> --<br /> --
Definitionfor function enum_del:<br /> --<br /> CREATE OR REPLACE FUNCTION enum_del (enum_name character varying,
enum_elemcharacter varying) RETURNS void<br /> AS<br /> $body$<br /> DECLARE<br />     type_oid INTEGER;<br />     rec
RECORD;<br/>     sql VARCHAR;<br />     ret INTEGER;<br /> BEGIN<br /><br />     SELECT pg_type.oid<br />     FROM
pg_type<br/>     WHERE typtype = 'e' AND typname = enum_name<br />     INTO type_oid;<br /><br />     -- check if enum
exists<br/>     IF NOT EXISTS (SELECT * FROM pg_enum WHERE enumtypid = type_oid) THEN<br />         RETURN;<br />    
ENDIF;<br /><br />     -- check if element in enum exists<br />     IF NOT FOUND THEN<br />         RAISE EXCEPTION
'Cannotfind a enum: %', enum_name;<br />     END IF;<br /><br />     -- Check column DEFAULT value references.<br />
   SELECT *<br />     FROM<br />         pg_attrdef<br />         JOIN pg_attribute ON attnum = adnum AND atttypid =
type_oid<br/>         JOIN pg_class ON pg_class.oid = attrelid<br />         JOIN pg_namespace ON pg_namespace.oid =
relnamespace<br/>     WHERE<br />         adsrc = quote_literal(enum_elem) || '::' || quote_ident(enum_name)<br />    
LIMIT1<br />     INTO rec;<br /><br />     IF FOUND THEN<br />         RAISE EXCEPTION<br />             'Cannot delete
theENUM element %.%: column %.%.% has DEFAULT value of ''%''',<br />             quote_ident(enum_name),
quote_ident(enum_elem),<br/>             quote_ident(rec.nspname), quote_ident(rec.relname),<br />            
rec.attname,quote_ident(enum_elem);<br />     END IF;<br /><br />     -- Check data references.<br />     FOR rec IN<br
/>        SELECT *<br />         FROM<br />             pg_attribute<br />             JOIN pg_class ON pg_class.oid =
attrelid<br/>             JOIN pg_namespace ON pg_namespace.oid = relnamespace<br />         WHERE<br />            
atttypid= type_oid<br />             AND relkind = 'r'<br />     LOOP<br />         sql :=<br />             'SELECT 1
FROMONLY '<br />             || quote_ident(rec.nspname) || '.'<br />             || quote_ident(rec.relname) || ' '<br
/>            || ' WHERE '<br />             || quote_ident(rec.attname) || ' = '<br />             ||
quote_literal(enum_elem)<br/>             || ' LIMIT 1';<br />         EXECUTE sql INTO ret;<br />         IF ret IS
NOTNULL THEN<br />             RAISE EXCEPTION<br />                 'Cannot delete the ENUM element %.%: column %.%.%
containsreferences',<br />                 quote_ident(enum_name), quote_ident(enum_elem),<br />                
quote_ident(rec.nspname),quote_ident(rec.relname),<br />                 rec.attname;<br />         END IF;<br />    
ENDLOOP;<br /><br />     -- OK. We may delete.<br />     DELETE FROM pg_enum WHERE enumtypid = type_oid AND enumlabel =
enum_elem;<br/> END;<br /> $body$<br />     LANGUAGE plpgsql;</font><br /><br /><br /><br /><br /><pre
class="moz-signature"cols="72">-- 
 
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

<a class="moz-txt-link-freetext" href="http://www.innogames.com">http://www.innogames.com</a> – <a
class="moz-txt-link-abbreviated"href="mailto:bernhard.schrader@innogames.de">bernhard.schrader@innogames.de</a>
 
</pre>