Re: sorting table columns

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> I've been trying to implement the holy grail of decoupling
> logical/physical column sort order representation, i.e., the feature
> that lets the server have one physical order, for storage compactness,
> and a different "output" order that can be tweaked by the user. This
> has been discussed many times; most recently, I believe, here:
> http://archives.postgresql.org/pgsql-hackers/2007-02/msg01235.php
> with implementation details here:
> http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php

> The idea described there by Tom, and upon which I formed a vague
> implementation plan in my head, is that I was to look for all uses of
> an "attnum", and then replace it by either "attlognum" (i.e. the
> user-visible sort identifier) or "attphysnum" (i.e. the order of
> attributes as stored on disk).

I thought we'd concluded that we really need three values: attnum should
be a permanent logical ID for each column, and then the user-visible
column order would be determined by a different number, and the on-disk
column order by a third. If we're going to do this at all, it seems
like a seriously bad idea to only go halfway, because then we'll just
have to revisit all the same code again later.

You do *not* want to store either of the latter two numbers in
parse-time Var nodes, because then you can't rearrange columns without
having to update stored rules. But it might be useful to decree that
one thing setrefs.c does is renumber Vars in scan nodes to use the
physical column numbers instead of the permanent IDs.

I haven't looked into any of the details, but I would guess that
targetlists should always be constructed in logical (user-visible)
column order. TupleDescs need to match the physical order, most
likely. Note that all three orderings are always going to be the same
everywhere above the table scan level. (And I suppose COPY will need
some hack or other.)

regards, tom lane