Re: logical column ordering

On 12.3.2015 03:16, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Side idea: Let attnum be the logical number, introduce attphysnum
>> as the storage position, and add an oid to pg_attribute as the
>> eternal identifier.
>
>> That way you avoid breaking pretty much all user code that looks at
>> pg_attribute, which will probably do something like ORDER BY
>> attnum.
>
>> Also, one could get rid of all sorts of ugly code that works around
>> the lack of an oid in pg_attribute, such as in the dependency
>> tracking.
>
> I think using an OID would break more stuff than it fixes in
> dependency tracking; in particular you would now need an explicit
> dependency link from each column to its table, because the
> "sub-object" knowledge would no longer work. In any case this patch
> is going to be plenty big enough already without saddling it with a
> major rewrite of the dependency system.

Exactly. I believe Alvaro considered that option in the past.

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

regards

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