Re: Alter or rename enum value

On 03/25/2016 03:22 PM, Christophe Pettus wrote:
> On Mar 25, 2016, at 11:50 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>> I don't believe anyone knows how to do that safely.
> The core issue, for me, is that not being able to modify enum values in a transaction breaks a very wide variety of database migration tools. Even a very brutal solution like marking indexes containing the altered type invalid on a ROLLBACK would be preferable to the current situation.
>

Well, let's see if we can think harder about when it will be safe to add
new enum values and how we can better handle unsafe situations.

The danger AIUI is that the new value value will get into an upper level
page of an index, and that we can't roll that back if the transaction
aborts.

First, if there isn't an existing index using the type we should be safe
- an index created in the current transaction is not a problem because
if the transaction aborts the whole index will disappear.

Even if there is an existing index, if it isn't touched by the current
transaction the we should still be safe. However, if it is touched we
could end up with a corrupted index if the transaction aborts. Maybe we
could provide an option to reindex those indexes if they have been
touched in the transaction and it aborts. And if it's not present we
could instead abort the transaction as soon as it detects something that
touches the index.

I quite understand that this is all hand-wavy, but I'm trying to get a
discussion started that goes beyond acknowledging the pain that the
current situation involves.

cheers

andrew