Re: Problems with ENUM type manipulation in 9.1

Josh Kupershmidt <schmiddy(at)gmail(dot)com> writes:
>> Excerpts from depstein's message of mi sep 28 07:21:17 -0300 2011:
>>> Anyway, the procedure that we used (based on
>>> http://en.dklab.ru/lib/dklab_postgresql_enum/) does the necessary
>>> checks before removing enum values.

> Not exactly; that code is rife with race conditions. For instance, how
> does the "Check data references" loop ensure that previously-checked
> tables don't get a new row containing the forbidden enum_elem before
> the function is finished?

It's worse than that: even if you have in fact deleted all occurrences
of a specific enum OID from the tables, that OID might still be lurking
in a btree index on an enum column. If you delete the pg_enum entry,
and the OID is odd (meaning that the pg_enum entry must be consulted to
find out how to sort it), you just broke that index.

You might think you could get out of that by VACUUM'ing to ensure that
dead index entries get cleaned out, but that is not good enough. The
problem OID could have gotten copied into a btree page boundary value or
non-leaf-page entry. If that happens, the OID will most likely never
disappear from the index, short of a REINDEX; and this is also the worst
case for index corruption, since we must be able to compare other OID
values to the non-leaf-page entry to figure out which leaf page to
descend to in searches.

In short, the reason why this type of code hasn't been adopted into core
is that it doesn't work.

regards, tom lane