Re: Separate the attribute physical order from logical order

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> If you do not provide a column identity number or you use something else
> (e.g. attlognum) to cross-references attributes from other catalogs,
> then you'll have to edit pg_attrdef when a column moves; and any other
> reference to a column number will have to change. Or think about
> pg_depend. You don't want that. This is why you need three columns,
> not two.

In previous go-rounds on this topic (of which there have been many),
we understood the need for attidnum as being equivalent to the familiar
notion that tables should have an immutable primary key, with anything
that users might wish to change *not* being the primary key. This
side-steps the need to propagate changes of the pkey into referencing
tables, which is essentially what Alvaro is pointing out you don't
want to have to deal with.

FWIW, I'd lean to the idea that using three new column names would
be a good thing, because it'll force you to look at every single
reference in the code and figure out which meaning is needed at that
spot. There will still be a large number of wrong-meaning bugs, but
that disciplined step will hopefully result in "large" being "tolerable".

>> I think that supporting at least a way to specify the logical order
>> during the table creation should be easy to implement

> As long as it is really simple (just some stuff in CREATE TABLE, nothing
> at all in ALTER TABLE) then that sounds good. I just suggest not to
> complicate things too much to avoid the risk of failing the project
> altogether.

I think that any user-reachable knobs for controlling this should be
designed and built later. The initial split-up of attnum meanings
is already going to be a huge lift, and anything at all that you can
do to reduce the size of that first patch is advisable. If you don't
realize what a large chance there is that you'll utterly fail on that
first step, then you have failed to learn anything from the history
of this topic.

Now you do need something that will make the three meanings different
in order to test that step. But I'd suggest some bit of throwaway code
that just assigns randomly different logical and physical orders.

regards, tom lane