Re: [ADMIN] Problems with enums after pg_upgrade

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2012-12-19 16:51:32 +0100, Bernhard Schrader wrote:
>> 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.

> That explains everything. You *CANNOT* do that.

Yeah. So this was not pg_upgrade's fault at all: that code would have
created problems in 9.1 or later even without using pg_upgrade.

For the record, the reason you can't safely do this is exactly what we
saw here: it's possible for deleted/never-committed values of the type
to remain behind in upper levels of btree indexes. Since we now need
to be able to consult pg_enum to know how to compare values of an enum
type, deleted values are uncomparable.

enum_add is all right as long as you are careful to commit its
transaction before inserting the new value anywhere. enum_del is quite
unsafe unless you REINDEX all indexes on columns of the type after
making sure the value is gone from the tables.

regards, tom lane