Subject: Re: Alter or rename enum value
To: Christophe Pettus <xof@thebuild.com>
References: 
 <CAO=2mx6uvgPaPDf-rHqG8=1MZnGyVDMQeh8zS4euRyyg4D35OQ@mail.gmail.com>
 <56E049CC.8010201@dunslane.net> <25605.1457539654@sss.pgh.pa.us>
 <56E05635.70908@dunslane.net> <28167.1457543612@sss.pgh.pa.us>
 <CAO=2mx4rQHRvifT2yUQ1rz90A=9CVz4_Z0Sx1wTgwgQRzAimpQ@mail.gmail.com>
 <CAO=2mx6rK_h4BC7y+3Nu-8fuZJXdFjd9FE_MZnzkH+vnMp0C-Q@mail.gmail.com>
 <d8ja8ln7nl0.fsf@dalvik.ping.uio.no> <d8j60wb7na4.fsf@dalvik.ping.uio.no>
 <CAO=2mx6CgQXFJYXOpr4OJO+r8WX6PzEU5aT+K0vV94G7DxVJ0A@mail.gmail.com>
 <56F49CF8.8050007@BlueTreble.com> <27476.1458876456@sss.pgh.pa.us>
 <CAO=2mx6EOh6uQQ57Kv2z0x=kuyVbkEbwiY8R2h6VCPL=Fxe5wg@mail.gmail.com>
 <56F58876.6020508@dunslane.net>
 <4608AEFB-27B4-4DEC-9DEA-39DDAD77FEB7@thebuild.com>
Cc: Matthias Kurz <m.kurz@irregular.at>, Tom Lane <tgl@sss.pgh.pa.us>,
 Jim Nasby <Jim.Nasby@bluetreble.com>, pgsql-hackers@postgresql.org
From: Andrew Dunstan <andrew@dunslane.net>
Message-ID: <56F5FDDD.1010001@dunslane.net>
Date: Fri, 25 Mar 2016 23:11:25 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: <4608AEFB-27B4-4DEC-9DEA-39DDAD77FEB7@thebuild.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


On 03/25/2016 03:22 PM, Christophe Pettus wrote:
> On Mar 25, 2016, at 11:50 AM, Andrew Dunstan <andrew@dunslane.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers