Re: [ppa-dev] alter column order

[Robert T. <ro...@xz...>]

On Mon, Apr 22, 2013 at 8:49 AM, Jehan-Guillaume (ioguix) de Rorthais
<io...@fr...> wrote:
> On 20/04/2013 17:17, Karl O. Pinc wrote:
>> On 04/20/2013 09:34:04 AM, Hans Chen wrote:
>>> On Sat, Apr 20, 2013 at 4:11 PM, Karl O. Pinc <ko...@me...> wrote:
>>>> On 04/20/2013 08:04:04 AM, Jehan-Guillaume (ioguix) de Rorthais
>>> wrote:
>>>>> On 20/04/2013 00:03, Hans Chen wrote:
<snip>,
>>> Yes, actually there are also some thoughts from pg community. See
>>> here: http://wiki.postgresql.org/wiki/Alter_column_position
>>>
>>> I think recreate table or recreate column would have trouble if the
>>> old column is with constraint.
>>
>> Not only constraints but also indexes, clustering, views, and triggers
>> come to mind.
>>
>>>
>>> I will tested both recreate and my way, and will also spend several
>>> days to investigate postgresql source code.
>>
>> Yes.
>> The right way would be to work first with pg and get changing
>> column ordering supported there and then add that support to ppa.
>
> +1000
>

Yeah, I looked into this a number of years back, and it's somewhat
complicated; right now attnum is used within the postgres catalogs and
source code to represent both physical and logical position of a row.
If you were going to try and implement this, I think you need to do it
in parts; first modify the postgres catalogs and code to have a
separate idea of both the physical and logical column position, then
you can implement the column position changes, then if you want bonus
points, implement automatic physical reordering for optimized page
layout. There are some good threads on hackers about this, though they
are quite a bit older, but the state of that code hasn't changed much.
Before you code anything though, you'll want to get some kind of buy
in on the hackers list (which I'll admit is bike-sheddingly
dangerous).

Robert Treat
play: xzilla.net
work: omniti.com