Re: Column storage positions - Mailing list pgsql-hackers

On Wed, 2007-02-21 at 13:16 -0500, Andrew Dunstan 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.
> >   
> 
> 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.

I missed nothing, AFAICS. My understanding was that Alvaro was proposing
to have just a simple physical re-ordering and that would be altered at
CREATE TABLE time. No complexity of multiple column orderings: nice,
simple and effective. My only addition was to say: must be optional.

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

I agree with comments here about the multiple orderings being a horrible
source of bugs, as well as lots of coding even to make it happen at all
http://archives.postgresql.org/pgsql-hackers/2006-12/msg00859.php

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com