Re: Column storage positions - Mailing list pgsql-hackers

On Wed, Feb 21, 2007 at 08:33:10PM +0100, Florian G. Pflug wrote:
> Simon Riggs wrote:
> >On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
> >>On 2/21/07, Alvaro Herrera <alvherre@commandprompt.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.
> 

IMHO I think display order is very important to users.  First, don't
break the select *, no matter how bad it is to code that. Next, don't
break copy or pg_dump/restore.  We've fielded a lot of questions on
the ordering of columns for display and simplicity reasons.

The storage order is orthogonal to the display order.  display order can be handled
in attnum and the new storage order can be the new column.

--elein