Re: [ADMIN] Problems with enums after pg_upgrade
От | Bernhard Schrader |
---|---|
Тема | Re: [ADMIN] Problems with enums after pg_upgrade |
Дата | |
Msg-id | 50D1E284.40707@innogames.de обсуждение исходный текст |
Ответы |
Re: [ADMIN] Problems with enums after pg_upgrade
(Andres Freund <andres@2ndquadrant.com>)
|
Список | 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>
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Heikki LinnakangasДата:
Сообщение: Re: Switching timeline over streaming replication