Re: sorting table columns - Mailing list pgsql-hackers

Excerpts from Tom Lane's message of mar dic 20 18:24:29 -0300 2011:
> Alvaro Herrera <alvherre@alvh.no-ip.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.

Yeah, I was unclear -- that's what I'm doing (or, rather, attempting to
do).

> 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.

Hmm, having the numbers in Var nodes seems a fundamental part of the way
I'm attacking the problem.  Hopefully after I give setrefs.c a read I
will have a clearer picture of the way to do it without that.

> 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.)

Okay.  AFAICS this shoots down the idea of modifying destreceivers,
which is good because I was coming to that conclusion for a different
reason.

Thanks for the pointers.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support