Re: Alter or rename enum value

Just stumbled across this thread while looking for something else…

> On 28 Mar 2016, at 12:50 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> What you really need is to prevent the new value from being inserted
> into any indexes, but checking that directly seems far more difficult,
> ugly, and expensive than the above.
>
> I do not know whether this would be a meaningful improvement for
> common use-cases, though. (It'd help if people were more specific
> about the use-cases they need to work.)

My team’s use case is: We have to add new values to an enum (no removal or renames) during occasional database schema migration as part of software releases. We’re using a db migration tool that understands that postgres can do most schema changes in a transaction, so it attempts to run our add enum value statements in a transaction, even if we mark them as individual changes. With some huffing and puffing we’ve managed to work around this limitation in the tool, but it would definitely be nice to keep our enum-related migrations with our other changes and drop the custom non-transactional migration code that we’ve had to write.

The suggested solution of disallowing any use of the new value during the same transaction that is added in would work fine for us. In the (so far unprecedented) case that we need to use the new value in a migration at the same time, we’d always have the option of splitting the migration up into two transactions.

Cheers

Tom