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"
