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

On Sat, Oct 30, 2010 at 4:51 PM, Guillaume Lelarge
<guillaume(at)lelarge(dot)info> wrote:
> 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

I just checked the docs, and wow - that seems like a major step
backwards compared to our normal strive for transactional DDL.

> 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?

Yeah, that's really nasty. I guess we need split the commands at ;. I
guess we should pass a flag down somehow to tell the function that
executes the query to do that and then we could also potentially get
rid of the double SQL boxes. I'm not looking at the code, but I
suspect that'll be nasty.

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

Well psql has a full blown parser doesn't it? I'm not sure we want to
go that far if we can help it.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company