Re: logical column ordering

Tomas Vondra wrote:
> On 12.3.2015 03:16, Tom Lane wrote:

> > I agree though that it's worth considering defining
> > pg_attribute.attnum as the logical column position so as to minimize
> > the effects on client-side code. I doubt there is much stuff
> > client-side that cares about column creation order, but there is
> > plenty that cares about logical column order. OTOH this would
> > introduce confusion into the backend code, since Alvaro's definition
> > of attnum is what most of the backend should care about.
>
> IMHO reusing attnum for logical column order would actually make it more
> complex, especially if we allow users to modify the logical order using
> ALTER TABLE. Because if you change it, you have to walk through all the
> places where it might be referenced and update those too (say, columns
> referenced in indexes and such). Keeping attnum immutable makes this
> much easier and simpler.

I think you're misunderstanding. The suggestion, as I understand it, is
to rename the attnum column to something else (maybe, say, attidnum),
and rename attlognum to attnum. That preserves the existing property
that "ORDER BY attnum" gives you the correct view of the table from the
point of view of the user. That's very useful because it means clients
looking at pg_attribute need less changes, or maybe none at all.

I think this wouldn't be too difficult to implement, because there
aren't that many places that refer to the column-identity attribute by
name; most of them just grab the TupleDesc->attrs array in whatever
order is appropriate and scan that in a loop. Only a few of these use
att->attnum inside the loop --- that's what would need to be changed,
and it should be pretty mechanical.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services