Re: Simple Column reordering

On Thu, 2007-02-22 at 20:07 -0600, Andrew Dunstan wrote:
> Simon Riggs wrote:
> >
> > I propose that at CREATE TABLE time, the column ordering is re-ordered
> > so that the table columns are packed more efficiently. This would be a
> > physical re-ordering, so that SELECT * and COPY without explicit column
> > definitions would differ from the original CREATE TABLE statement.
> >
> > This would be an optional feature, off by default, controlled by a
> > USERSET GUC
> > optimize_column_order = off (default) | on
> >
>
>
> Umm, you want a GUC setting to enable standards-breaking behaviour and
> that will be obsolete when we do column ordering right, which is not
> likely to be more than one release away, and could even still happen in
> this coming release?

If this is standards-breaking as you say, I would withdraw immediately.
I checked the SQL standard and could not see how this would do so. The
standard states SELECT * would return columns in order; it doesn't say
what that order should be, nor does CREATE TABLE enforce the ordering to
be the same as it has specified, AFAICS. Please correct me and I will
withdraw. Practical issues seem far stronger drivers than standards
issues here, which is why the parameter would default=off.

If the full implementation exists and works, I would welcome it. This
proposal is really aimed at what we do if that doesn't occur; we must
wait to see if it will. I see that many users would want to get
something sooner rather than later. That isn't a commercial perspective,
I see that as a PostgreSQL advocacy perspective.

I also see that we are forcing change into the on-disk format of heaps
in this release. If we defer this to another release then we would be
effectively changing the on-disk format again in next release.

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