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.

But the display order (and hence the COPY order) of columns would still
be determinted by attnum, not by some attstoragepos, no?
The column reordering would only apply to the physical storage of
columns, not to how it's presented to the user I'd think.

The original idea was to add a third column, attdisplaypos, and let the
user choose the display ordering independent from the unique id
(attnum), which in turn is independent from the storage position.

For simplicity, the OP said he omitted the display-position part here,
because it's really orthogonal to being able to modify the storage position.

greetings, Florian Pflug