Re: Alter or rename enum value

<div dir="ltr"><div class="gmail_extra"><div class="gmail_extra">Besides not being able to rename enum values there are
twoother limitations regarding enums which would be nice to get finally fixed:</div><div class="gmail_extra"><br
/></div><divclass="gmail_extra">1) There is also no possibility to drop a value.</div><div class="gmail_extra"><br
/></div><divclass="gmail_extra">2) Quoting the docs (<a
href="http://www.postgresql.org/docs/9.5/static/sql-altertype.html">http://www.postgresql.org/docs/9.5/static/sql-altertype.html</a>):</div><div
class="gmail_extra">"ALTERTYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside
atransaction block." Example:</div><div class="gmail_extra"># CREATE TYPE bogus AS ENUM('good');</div><div
class="gmail_extra">CREATETYPE</div><div class="gmail_extra"># BEGIN;</div><div class="gmail_extra">BEGIN</div><div
class="gmail_extra">#ALTER TYPE bogus ADD VALUE 'bad';</div><div class="gmail_extra">ERROR:  ALTER TYPE ... ADD cannot
runinside a transaction block</div><div class="gmail_extra"><br /></div><div class="gmail_extra">To summarize
it:</div><divclass="gmail_extra">For enums to finally be really usable it would nice if we would have (or
similiar):</div><divclass="gmail_extra">ALTER TYPE name DROP VALUE [ IF EXISTS ] enum_value</div><div
class="gmail_extra">and</div><divclass="gmail_extra">ALTER TYPE name RENAME VALUE [ IF EXISTS ] old_enum_value_name TO
new_enum_value_name</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">And all of the operations
(adding,renaming, dropping) should also work when done within a new transaction on an enum that existed before that
transaction.</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">I did some digging and maybe following
commitsare useful in this context:</div><div class="gmail_extra">7b90469b71761d240bf5efe3ad5bbd228429278e</div><div
class="gmail_extra">c9e2e2db5c2090a880028fd8c1debff474640f50</div><divclass="gmail_extra"><br /></div><div
class="gmail_extra">Alsothere are these discussions where some of the messages contain some useful
information:</div><divclass="gmail_extra"><a
href="http://www.postgresql.org/message-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com">http://www.postgresql.org/message-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com</a></div><div
class="gmail_extra"><a
href="http://www.postgresql.org/message-id/50324F26.3090809@dunslane.net">http://www.postgresql.org/message-id/50324F26.3090809@dunslane.net</a></div><div
class="gmail_extra"><a
href="http://www.postgresql.org/message-id/20130819122938.GB8558@alap2.anarazel.de">http://www.postgresql.org/message-id/20130819122938.GB8558@alap2.anarazel.de</a></div><div
class="gmail_extra"><br/></div><div class="gmail_extra">Also have a look at this workaround:</div><div
class="gmail_extra"><a
href="http://en.dklab.ru/lib/dklab_postgresql_enum/">http://en.dklab.ru/lib/dklab_postgresql_enum/</a></div><div
class="gmail_extra"><br/></div><div class="gmail_extra">How high is the chance that given the above information someone
willtackle these 3 issues/requests in the near future? It seems there were some internal chances since the introduction
ofenums in 8.x so maybe this changes wouldn't be that disruptive anymore?</div><div class="gmail_extra"><br
/></div><divclass="gmail_extra">Regards,</div><div class="gmail_extra">Matthias</div><br /><div class="gmail_quote">On
9March 2016 at 18:13, Tom Lane <span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us"
target="_blank">tgl@sss.pgh.pa.us</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span
class="">AndrewDunstan <<a href="mailto:andrew@dunslane.net">andrew@dunslane.net</a>> writes:<br /> > On
03/09/201611:07 AM, Tom Lane wrote:<br /></span><span class="">>> I have a vague recollection that we discussed
thisat the time the enum<br /> >> stuff went in, and there are concurrency issues?  Don't recall details<br />
>>though.<br /><br /> > Rings a vague bell, but should it be any worse than adding new labels?<br /><br
/></span>Ithink what I was recalling is the hazards discussed in the comments for<br /> RenumberEnumType.  However, the
problemthere is that a backend could make<br /> inconsistent ordering decisions due to seeing two different pg_enum
rows<br/> under different snapshots.  Updating a single row to change its name<br /> doesn't seem to have a comparable
hazard,and it wouldn't affect ordering<br /> anyway.  So it's probably no worse than any other object-rename
situation.<br/><br />                         regards, tom lane<br /></blockquote></div><br /></div></div>