Re: Ticket 269: Add support for 9.1 ALTER TYPE new syntax for enum

Le 30/10/2010 08:18, Dave Page a écrit :
> On Sat, Oct 30, 2010 at 4:07 PM, Guillaume Lelarge
> <guillaume(at)lelarge(dot)info> wrote:
>> Le 29/10/2010 21:56, Guillaume Lelarge a écrit :
>>> Le 29/10/2010 21:11, Guillaume Lelarge a écrit :
>>>> [...]
>>>> This patch adds support to the new ALTER TYPE syntax in 9.1 for enums.
>>>>
>>>> It's working great except one thing. If a user wants to add two labels,
>>>> we're screwed because we can't do two "ALTER TYPE ... ADD" statements in
>>>> the same query execution. Any idea how to solve this? the only way I
>>>> found would be to disallow adding two labels at once but it results on a
>>>> less interesting feature.
>>>>
>>>
>>> So I was wrong. The issue is that we can't issue this statement in a
>>> explicit transaction. Any idea how to solve the "don't send begin/end
>>> statements"?
>>>
>>
>> The only idea I have is to make dlgType a two-SQL-boxes dialog and
>> modify the dlgProperty::apply() method so that if there is "ALTER TYPE
>> ... ADD {BEFORE | AFTER}" statements, they get splitted and fired
>> individualy. I didn't yet write the code to split the statement, but it
>> will surely be ugly.
>>
>> Any objection on doing this? or better idea to fix this issue?
>
> I don't understand why we can't do all this at once - what's the
> problem exactly? Normally we only split statements when they're not
> atomic and cannot be rolled back.
>

Here is the error message I got:

ERROR: ALTER TYPE ... ADD cannot be executed from a function or
multi-command string

So, the issue we have is that we can't execute two of them at the same
time. We need to split the ALTER TYPE statements to make sure we execute
them one by one. We could have used the two-sql-textboxes without
actually splitting the query but in this case, a user will only be able
to add two new labels to an enum datatype. What happens if he wants to
add three of them?

So I really think we need to split the ALTER TYPE statements. We
probably can borrow the code from psql for this.

--
Guillaume
http://www.postgresql.fr
http://dalibo.com