Re: Column storage positions

Simon Riggs wrote:
> On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
>
>> On 2/21/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>>
>>> I'd expect the system being able to reoder the columns to the most
>>> efficient order possible (performance-wise and padding-saving-wise),
>>> automatically. When you create a table, sort the columns to the most
>>> efficient order; ALTER TABLE ADD COLUMN just puts the new columns at the
>>> end of the tuple; and anything that requires a rewrite of the table
>>> (ALTER TABLE ... ALTER TYPE for example; would be cool to have CLUSTER
>>> do it as well; and do it on TRUNCATE also) again recomputes the most
>>> efficient order.
>>>
>> That's exactly what I'm proposing. On table creation, the system
>> chooses an efficient column order for you.
>>
>
> That's fairly straightforward and beneficial. I much prefer Alvaro's
> approach rather than the storage position details originally described.
> Moreover, you'd need to significantly re-write lots of ALTER TABLE and I
> really don't think you want to go there.
>
> There is a problem: If people do a CREATE TABLE and then issue SELECT *
> they will find the columns in a different order. That could actually
> break some programs, so it isn't acceptable in all cases. e.g. COPY
> without a column-list assumes that the incoming data should be assigned
> to the table columns in the same order as the incoming data file.
>

You seem to have missed that we will be separating logical from physical
ordering. Each attribute will have a permanent id, a physical ordering
and a logical ordering. You can change either ordering without affecting
the other.

COPY, SELECT and all user-visible commands should follow the logical
ordering, not the physical ordering, which should be completely
invisible to SQL.

cheers

andrew