Re: column ordering, was Re: [PATCHES] Enums patch v2

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> You could make a case that we need *three* numbers: a permanent column
>> ID, a display position, and a storage position.

> Could this not be handled by some catalog fixup after an add/drop? If we
> get the having 3 numbers you will almost have me convinced that this
> might be too complicated after all.

Actually, the more I think about it the more I think that 3 numbers
might be the answer. 99% of the code would use only the permanent ID.
Display position would be used in *exactly* one place, namely while
expanding "SELECT foo.*" --- I can't think of any other part of the
backend that would care about it. (Obviously, client-side code such
as psql's \d would use it too.) Use of storage position could be
localized into a few low-level tuple access functions, probably.

The problems we've been having with the concept stem precisely from
trying to misuse either display or storage position as a permanent ID.
That's fine as long as it actually is permanent, but as soon as you
want to change it then you have problems. We should all understand
this perfectly well from a database theory standpoint: pg_attribute
has to have a persistent primary key. (attrelid, attnum) is that key,
and we can't go around altering a column's attnum without creating
problems for ourselves.

regards, tom lane