=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@xxxxxxxxx> writes: > I am absolutely sure a certain value of one of my ENUM types is not used in > the entire database. Now I am asked to drop that value. Unfortunately, > there is no ALTER TYPE DROP VALUE. Yup. > On my development box I tried > delete from pg_enum > where enumtypid='my_type_name'::regtype > and enumlabel='my_label' > It worked and I could not find any adverse effects. > Given the value is not used anywhere, is this a save command? No. If it were, we'd have an ALTER DROP VALUE command. The key problem that is hard to fix here is that, even if today you have no live rows containing that value, it may still be present in indexes. In a btree, for example, the value might've migrated up into upper index pages as a page boundary value. Once that's happened, it's likely to persist indefinitely, even if the live occurrences in the underlying table get deleted and vacuumed away. Now, even if that's happened, you might be okay, because of the optimizations that typically allow enum value comparisons to be done without consulting pg_enum. But if you're in the habit of altering enums, it's that much more likely that you would have done an ALTER TYPE that defeats those optimizations; so I wouldn't rely on this. Sooner or later you're going to get burnt by complaints about an invalid enum value (not sure of the exact wording) when you access certain parts of the index. You could maybe get around all of that by reindexing any indexes containing the altered enum type after you're certain that all entries of the unwanted enum value are dead and vacuumed away. But it's not terribly safe. regards, tom lane
