Re: Separate the attribute physical order from logical order

On 2022-Jun-28, Julien Rouhaud wrote:

> So, assuming that the current JOIN expansion order shouldn't be
> changed, I implemented the last approach I mentioned.

Yeah, I'm not sure that this is a good assumption.  I mean, if logical
order is the order in which users see the table columns, then why
shouldn't JOIN expand in the same way?  My feeling is that every aspect
of user interaction should show columns ordered in logical order.  When
I said that "only star expansion changes" upthread, what I meant is that
there was no need to support any additional functionality such as
letting the column order be changed or the server changing things
underneath to avoid alignment padding, etc.


Anyway, I think your 0001 is not a good first step.  I think a better
first step is a patch that adds two more columns to pg_attribute:
attphysnum and attlognum (or something like that.  This is the name I
used years ago, but if you want to choose different, it's okay.)  In
0001, these columns would all be always identical, and there's no
functionality to handle the case where they differ (probably even add
some Assert that they are identical).  The idea behind these three
columns is: attnum is a column identity and it never changes from the
first value that is assigned to the column.  attphysnum represents the
physical position of the table.  attlognum is the position where the
column appears for user interaction.

In a 0002 patch, you would introduce backend support for the case where
attlognum differs from the other two; but the other two are always the
same and it's okay if the server misbehaves or crashes if attphysnum is
different from attnum (best: keep the asserts that they are always the
same).  Doing it this way limits the number of cases that you have to
deal with, because there will be enough difficulty already.  You need to
change RTE expansion everywhere: *-expansion, COPY, JOIN, expansion of
SQL function results, etc ...  even psql \d ;-)  But, again: the
physical position is always column identity and there's no way to
reorder the columns physically for storage efficiency.

You could put ALTER TABLE support for moving columns as 0003.  (So
testing for 0002 would just be some UPDATE sentences or some hack that
lets you test various cases.)

In a 0004 patch, you would introduce backend support for attphysnum to
be different.  Probably no DDL support yet, since maybe we don't want
that, but instead we would like the server to figure out the best
possible packing based on alignment padding, nullability varlenability.
So testing for this part is again just some UPDATEs.

I think 0001+0002 are already a submittable patchset.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Si quieres ser creativo, aprende el arte de perder el tiempo"